If you are somebody that is quite good at Excel but wanting to move to the next level and automate some of your activities the first port of call for most people is recording macros. This is relatively easy to do and requires very little effort to learn how. However, what are the drawbacks of recording macros and why do people go on to learn VBA?
Recording of macros is a good way to start off for those that are not prepared to spend the time learning to write VBA, however, there are some drawbacks and limitations associated with this process. The most significant is that recorded macros can really only create automated processes for the manipulation of spreadsheets which could include a range of things such as formatting or entering formulas. It is not possible to design macros to be readily dynamic and call for user input which is a key benefit of learning VBA.
The ability to get user input and then design processes that respond to that user input makes writing VBA code significantly better though it can take quite a substantial time to develop the necessary skills to become proficient in this area.
However, it is also a relatively rare skill among people outside of the IT industry and therefore can be used to improve your employability substantially because it allows you to fix problems within an organization that other people cannot. To give you an example of some of the tasks that I’ve undertaken with VBA and how they have affected my ability to create an efficient working environment watch the video below.
What Are The Other Key Advantages Associated With Writing VBA?
As mentioned in the introductory section of the article one of the primary advantages of writing VBA is that you can retrieve user input which can allow you to design processes that respond to user input which is far more powerful than just recording the basics of actions.
In addition to those features, VBA is also very useful for controlling a number of different processes in other programs within the Microsoft Office Suite. An example of this can include the capacity to be able to program Outlook to send an email from Excel without ever opening your emails. It is also possible to call data in from a program such as Access which can be extremely useful at times.
VBA can also create macros designed to activate the macros without the need for the user to do anything which can also be extremely advantageous in certain circumstances. For example, I produced a file that ran a macro upon opening the document which called up data from a centralized location which enabled several versions of the same file to remain synchronized with the most updated data.
Macros can also be triggered via changes in selected cells which is also advantageous for creating a dynamic environment for the code to run. VBA is also advantageous in cases where there is a logical test to be run that is relatively complex. Conventional if statements in Excel can become difficult to use if there are too many conditions.
Lastly, unless you carefully design your recorded macros they can create problems because they can produce positionally sensitive macros but need to start in a specific position within your spreadsheet to work properly. It can also be difficult to design the macros to allow for changes in incoming data very easily, however, there are a few things that you can do to reduce the chances of creating problems.
Tips For Creating Recorded Macros That Will Be More Robust
As mentioned earlier in the article positionally sensitive macros can be created which can be potentially dangerous for losing work as macros cannot be undone once activated. To avoid this problem is fairly straightforward; it just requires you to make sure that you use the go to functions at the start of any recorded macro to ensure that the macro starts from the specific position that you have designated.
This will ensure irrespective of where your cursor is when the macro is triggered the macro will produce the expected results. To do this press CRTL + G and select the cell where you want to start the macro.
The other common problem with recorded macros is that they are not easily able to adjust for changes in the conditions of the spreadsheet when the macros start. An example of this might be if you are processing a sales report every month which varies in length from month to month.
To overcome these problems the easiest way is to extend any process that you are doing far beyond the length that you expect the report to reach. For example, if you need to insert data using a vlookup from another table ensure that vlookup extends well beyond the length that the data that you are likely to ever get.
When doing something like this it is important to ensure that you prevent errors from occurring in vlookups by putting an if statement when the cells are blank. ie =if(A1=””, “”, vlookup(…………..
In some circumstances it is also a good idea to highlight, using color, the range that the macro covers to ensure that you don’t forget how far the macro extends in case things change in the future.
The other tip that is useful is combining macros within macros. This is very useful for building up complex processes in small bite-size pieces. To do this you create small macros for each part of the process you are trying to create and then at the end record a macro where you trigger each of the small macros successively which will combine them together in one macro.
Relevant Articles
Is VBA Worth Learning? Does It Take Long?
Is VBA Hard To Learn? Will It Take Long?