Microsoft Excel is used in millions of workstations and for most users Excel provides all the tools they need. For those who have very specific needs that cannot be fulfilled by the standard functions of Excel, or where routine tasks are often repeated, Excel provides a native programming environment - Visual Basic for Applications (VBA). Skilled users of Excel will be aware of the Macro recording features of Excel - which do quite a good job of creating a solution to a specific problem, however these solutions tend to be quite inflexible. VBA based solutions on the other hand offer the developer full access to all of the features of Excel, plus the ability to create their own functions, processes and interfaces.
When using VBA to create Custom Functions the user is able to create complex functions that have many advantages over traditional nested functions. Some advantages of a UDF over a nested function include:
- Nested Functions in Excel 2007 can be 64 levels deep (7 in Excel 2003) - but when creating a formula this long a number of issues arise:
- Making changes to the formula will be difficult
- If a nested function was used in multiple Worksheets then the developer could easily miss some instances of the function when modifications are made
- When used many times this will significantly effect the size of the Excel File
- The alternative is to create a User Defined Function (UDF), which is written directly into a VBA code module, and is then entered into a cell in the same manner as a standard Excel Function would be - including any required arguments. This approach offers the developer a number of advantages, including:
- Editing is simpler, especially if the Code Module has been correctly indented and commented
- Making a change to the UDF will automatically apply to ALL instances of the Function, meaning only one edit is required
- The Code Module contains the functions logic, and as a result the individual cells have only need the name of the function and any required arguments - often resulting in a much smaller file size
- UDF's can be stored in a central file - known as a UDF Library - and used by more than one Workbook, as a consequence ensuring more consistent data analysis
Like to know more? Use our 'Contact Page' to send us an email.
Whilst the Excel Macro recorded does a good job it has a couple of drawbacks, that can lead to inflexible solutions. VBA based solutions don't suffer from this problem, but require a much higher skill level to develop than simply 'recording a macro'. VBA Developers will take advantage of the macro recorded to get a 'code skeleton' to work from and often strip out unwanted elements from the recorded macro and then add their own code to complete the required task.
Skilled Excel VBA Developers will be able to develop solutions that may not even look like Excel, by adding forms and other elements for the user to interact with - rather than the normal Worksheet / Cells. Some of the advantages of VBA based development include:
- Users can be prompted and actions taken based on the specific needs
- Loops can be written to....
- When working with Files users can be presented with File Dialog Boxes to Open / Save documents in locations relevant to the current task
- Interactions can be written than allow communication with other applications:
- From Excel generate an Email via Outlook (also Lotus Notes) that can include all the features of the email client
- Data can be read from or written into an Access Database or even Word Document
- Reporting solutions can be developed that allow an unskilled user to 'pick a box' or 'select from a list' and then click on the appropriate button to generate a report - something that may take hours to build manually can be generated by the use programmatically in a matter of minutes (or less!)
Some examples of the Excel VBA solutions developed by Training IT include:
- TOIL (Time Off In Lieu) Sheet that interacts with Outlook, sending the Monthly time accrued/used to Head Office for review, after which it is sent back and the next months sheet enabled
- Paddock Management Tool, used by a dairy farmer to ensure that paddocks are rotated in an even program
Like to know more? Use our 'Contact Page' to send us an email.