VBA as a Tool for Creating User Matrix Functions to Simulate Database Functions in Bulk Data Processing

Abstract:

Many companies use the MS Office programme at various levels of their internal hierarchy. This programme includes the MS Excel table processor, a tool for processing data in tabular form. However widely this data processing tool may be used, its utilization hardly ever goes beyond its internal functions. Based on a survey I have conducted in companies I worked for as a competitiveness consultant, I have reached the conclusion that MS Excel is far from being used to its full potential. I have found out that around 95 % companies using MS Excel have never used the Visual Basic for Applications internal programming language (or VBA), an internal tool within the MS Office package. The main reason why such a high percentage of employees do not use the VBA programming language is simple: they have not been informed about its possibilities by their provider (employer). It is quite understandable that someone who has no experience with programming or algorithmization will be reluctant to create their own user functions. Paradoxically, however, I have found the same situation in companies which have their own IT departments responsible for software development. Therefore, one of the aims of this article is to show how VBA can be used in bulk data processing, and in particular to create matrix functions simulating database functionality. I have used some of the existing MS Excel internal functions to draw a comparison with my proposal.