Excel VBA

Introduction To VBA: Macros And Automation Part 1

Our First Macros

Referencing Cells

With basic variables discussed and initialized, our next step is to see how we can pull values from cells within a workbook into our code for further use. There any many ways to accomplish this, but we will be looking at the most general way to do so. We will use the cell A1 in our spreadsheet to hold the message we want to display in our message box.

Following the same format as our previous example, we will create a new sub called ExReference that will hold our new macro.

We will start by initializing a string variable named MyString. Do not assign it a value yet.

Instead of giving it a static value like we did previously, we will instead ask Excel to find what is the value of the cell A1 of our currently active sheet.

To do this we will write out the following:

Mystring = ActiveSheet.Range(“A1”).Value

This is telling Excel to look on the currently active worksheet, in the range defined as A1, and return only its value.

Now that this is done we can create a message box that calls our MyString variable!

To finish this macro off we have entered the text “Thank you for being Excellent!” into the cell A1 of our worksheet.

We can add a new button, assign it the ExReference macro, and test it out!

With one changed line of code we have just expanded our ability to use VBA by incredible amounts. By being able to use cell reference to gather information, we can then manipulate it as we like without needing to always assign values directly through our code. Adding versatility and opportunity to our projects.

Our next step will be to add logic to our code, giving us the option to present two different outcomes based on one input.