Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners

Are you sure

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.

Continue reading “Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners”

Do You Hate The Numbers Stored As Text Error?

Convert To Numbers

How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your lookups and Match functions, and arithmetic calculations. Excel has a Convert to Number functionality to help with this situation, but it could be a lot better. You have to deal with your columns one at a time, sometimes one cell at a time. Also, I noticed that if the dataset is huge, excel takes a lot of time to push through; occasionally, it is so slow that you can see the cells getting updated one by one.

Continue reading “Do You Hate The Numbers Stored As Text Error?”

Excel Worksheet Functions and VBA Functions

Struggling to Excel

Many excel users might not be aware of the subtle distinction between Excel Worksheet functions and VBA functions. Majority of Excel’s capability is constructed using Visual Basics for Applications (VBA). VBA is a programming language that contains a fairly rich set of built in Functions; lets call these functions VBA Functions. Using the aforementioned VBA Functions, developers of Excel meticulously create hundreds of functions for their software, that could be keyed into the Formula Bar. These functions are called Excel Functions or Worksheet functions.

Continue reading “Excel Worksheet Functions and VBA Functions”

Reversing a string in Excel

String Reverse in Excel

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()

Continue reading “Reversing a string in Excel”

Concatenate Range and Retain Formatting

Concatenate Range with Formats

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.

Continue reading “Concatenate Range and Retain Formatting”

An upgrade to the Text() function in Excel

Struggling to Excel

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.

Continue reading “An upgrade to the Text() function in Excel”

Extract the Nth word in a String in Excel

Get Nth Word Excel Example

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.

Continue reading “Extract the Nth word in a String in Excel”