Visual Basic for Applications (VBA) is the Microsoft Office programming language that allows you to create macros, add a message box, execute code inside a document in response to a trigger, and much more. With VBA, you can supercharge your Excel spreadsheets and go beyond mere Excel functions.
This article will help you try your hand at VBA with a simple project: a button that converts the value of a chosen cell from GBP to USD. Through this article, you’ll learn how VBA and Excel can intersect, and get started with VBA.
What Is Visual Basic for Applications in Excel?
Visual Basic for Application, or VBA for short, is an integrated form of Visual Basic 6 in Microsoft Office programs. VBA is available in apps from the Office suite such as Word, PowerPoint, and of course, Excel.
VBA allows you to achieve things that are not normally available through the common features of Excel. With VBA, you can manipulate Excel’s interface, add functional buttons, and even define your own functions.
Naturally, VBA uses the Visual Basic programming language. Through this language, and with a bit of coding knowledge, you can transform your experience into something beyond the boundaries of Excel. This can be anything from creating VBA user forms to recreating iconic games in Excel.
VBA in Excel Tutorial: Creating an Exchange Button
There’s no better way to learn VBA in Excel than to try it for yourself. To help you with that, in this article, we’re going to undertake a simple VBA project. The goal of this project is to create a button that will convert the value in a selected cell from USD to GBP.
There are two steps involved in the code for this project. The first and main step is to convert the number value from USD to GBP by multiplying the USD number by 1.22, which is the supposed exchange rate. The second step, is to change the cell formatting from USD to GBP.
In the end, when you click the button, the selected cell’s value will be multiplied by the exchange rate, and the dollar symbol will change to the pound symbol. Now that you have a good understanding of what we’re looking to achieve, let’s get started!
1. Access Developer Controls in Excel
Before we can dive into VBA, it might be necessary to open Excel and adjust the settings to display the Developer tab as part of the Ribbon. The features you need to access for using VBA are housed in the Developer tab.
- Open Excel.
- Go to the File menu.
- Click on Options at the bottom of the screen. This will open the Excel Options window.
- In Excel Options, go to the Customize Ribbon tab.
- Under Main Tabs, check Developer.
Alternatively, you can also right-click the Excel ribbon and select Customize the Ribbon. This will take you to the Customize Ribbon tab in Excel options.
2. Create a Button
To create our currency converter, we first need to insert the button element. In the upcoming step, we’ll attach our VBA code to that button.
- In your Excel spreadsheet, go to the Developer tab.
- In the Controls section, click on Insert.
- Select ActiveX Command Button. This is the first option under ActiveX Controls.
- Draw a button on your spreadsheet.
The button will have a caption saying CommandButton1 by default. That looks a bit bland, so let’s add some character to our button.
- Select the button.
- Right-click on it and then select Properties. The Properties window will appear.
- Change (Name) to something more memorable. Beware that this is not the button caption. This is how the button will be addressed in your VBA code. In this example, we’re going to change the name to convertButton.
- Change the Caption to what you want to be displayed on the button. We’re going to use USD to GBP.
You can also change the font and add some style to your button through the same window. Once your button is ready, it’s time to add some functionality to it.
3. Add Some Code
Coding with VBA takes place in a separate environment from the standard Excel interface. To access it, make sure Design Mode is active in the Developer tab, then double-click the button. You’ll see a window like the one below:
The start and the end of our code are already in place—the two blue pieces of text bookend your function, while the text in black states that you’re stipulating the action that should take place when the user clicks on the button. If you chose a different name to convertButton, you should see the respective term in your version of this window.
To carry out the currency conversion procedure, we’ll use the following lines of code between the two that have already been created by Excel:
Dim R As Range
Dim W As Range
Set W = Selection
For Each R In W
R.Value = (R.Value * 1.22)
R.NumberFormat = "£ 0.00 "
Now let’s break down the code. This piece of code first declares the two variables R and W as ranges. You can change these two letters to whatever you want, as long as you stay consistent throughout the code.
Then, the code sets the variable W as the Selection. This means that W is now the selected cells in the spreadsheet. The For Each statement declares that the code below the statement should run for every R in W, which means for every cell in the selected range.
Next, the main function comes in. The value in the cell is multiplied by 1.22 (which is the exchange rate from GBP to USD), and then the cell format is changed to represent GBP. Finally, the Next statement indicates that the code (multiplication and change of format) should also run for the next cell in the range, which is the next R in W.
Here’s how that looks in the VBA window:
If you’ve given your button another name, or used names other than R and W in your code, remember to change these names to the ones in your code. Otherwise, the code won’t work.
Once your code is all set, close the VBA editor. You don’t need to save anything, as your changes are already saved.
4. Test Your Work
It’s now time to see whether your code works—but there’s an important step to take before you can do that. You need to disable Design Mode to stop any further modifications to the button, and make it functional.
Next, select a cell or a range of cells, and then click your button to see it work its magic. Hopefully, you’ll see the value increase by around a quarter, meaning that the conversion has been carried out correctly.
Next Steps With VBA in Excel
Now that you’ve created a button and used it to execute VBA code in Excel, you can use the same basic method to carry out all kinds of different projects. You could create a button that checks the contents of a specified cell against another one elsewhere in the same document.
Embarking on projects like these exposes you to the different features of VBA. Our currency converter is about as simple as it gets—but it’s the first step toward bigger things. If you’re just starting out, pick projects that relate your learning to a basic goal or task you’re curious about. Step by step, you will become more familiar with how VBA works.
Read the full article here