User-Defined Matrix Function as an Alternative Method Simulating the Activity of a Relational Database in the MS Excel Environment to Be Used in Companies

Abstract:

A number of small and medium-size companies have been using the MS Office software package for their administrative activities. In a survey carried out in these companies it was found out that the prevailing majority of administrative parts in the companies have been using one of the MS Office modules – MS Excel for data processing outside their central information systems, primarily for the processing of internal data. Although this spreadsheet program has been used on a wide scale its possibilities far more exceed its actual usage. Based on my survey in the companies where I was engaged in the process improving the quality and increasing the volume of application of ICT technologies as direct mentor and lecturer, I have come to the conclusion that MS Excel is far from being used in the full scope of the possibilities it offers. The reason is first that standard users do not know MS Excel functionality and second that it has not been promoted or marketed internally in an optimum way by the corporate IT department staff towards standard users. Only a fragment of company users know that MS Excel (as well as all the other MS Office parts) is based on the Visual Basic for Application programming language (hereinafter referred to as VBA), which is the internal tool used for the entire MS Office package. This article thus aims at showing the MS Excel possibilities using user-defined creation of functions for bulk data processing a standard company user could use in his/her everyday routine work activities. As an example of a VBA application the author proposes and describes in detail an alternative for the processing of a relational database in the form of an internal matrix function.