I have wasted many hours loading huge amounts of data into spreadsheet models, only to inadvertently click the ‘Reset’ button at the last moment, because it was the day my colleague decided to bump into my chair. Excel is not capable of undoing actions performed by a macro, making it very important to confirm whether a user meant to click a button, before performing certain operations. In this post, we will look at how to get the user’s permission before running a procedure, using the MsgBox function.
I am a big fan of using named ranges in my VBA code. It makes referring to cells in formulas easy, and I can also helps in auditing the code. For instance, I can simply search for “RangeName” (including the quotes) to see if that particular range name is being used in my code.
Regular-Expressions (RegExp) is something I bumped into when my string manipulation requirements jumped to an advanced level. Before I started using this powerhouse, all my string manipulation functions involved maneuvering through a dozen for-loops; and tackling hundreds of Left-Right-Mid-InStr functions. RegExp is a pattern matching system that allows you to perform advanced string manipulations very easily. It may take a while to get used to it, but once you get the hang of it, the possibilities are endless.
In my many years of rummaging through the internet for help, I have seen countless posts where troubled help seekers are told that VBA does not have a built in function to reverse a string. Amature know-alls suggest a makeshift solution using a for-loop; and everyone is happy. Contrary to popular belief, Visual Basics does have a built in function to reverse a string: StrReverse()
In one of my previous posts, I wrote a function to concatenate the values stored in all the cells of a specified range. That function holds good, if you have a text stored in all the cells, and you just want to club them together. It fails if you have cells containing numeric values: dates, percentages, currencies or time. In another post, I wrote a function that returns a formatted string of the value stored in a cell, by automatically fetching the cell’s Number Format.
Many of us are familiar with the Text() function. It comes in handy when you set up excel to draft reports for you. It converts a numeric value stored in a cell to a string based on the format specified by the user. The only thing I hate about that function is that you have to specify the format every time you use it. Another drawback is, the format of the text is not linked to the cell formatting of the cell that is being referred to. For instance, if you later decide to change the cell formatting of a cell, you also have to change the Special-Format-String argument in the Text() function.
Excel uses a three stage process to recalculate cells. It first draws a dependency tree, and then lists a calculation chain and then recalculates the cells. Check this link if you are interested in learning more about the recalculation process. Excel determines if a cell’s value depends on another, and decides the order in which they should be recalculated.
Visual Basics for Applications (VBA) allows users to build on the existing functionality of Microsoft Excel. It comes with a strong set of inbuilt functions that are often not readily accessible from Excel’s interface. These functions have to be wrapped inside a user defined function to be usable in Excel. A classic example is the Split function.
Most of us have used the Concatenate() function in Excel. It is a neat function that saves you the trouble of keying in “&” between the range addresses. The only thing that would make this awesome would be the ability to concatenate an entire range. That would be way more useful than having to comma separate each cell that you want to concatenate.