VBA standards for Visual Basic for Applications and is the language embedded within your spreadsheet in Excel 2016. You can only use VBA if you have saved your workbook as an Excel Macro-Enabled Workbook (or *.xlsm file). You use VBA in Excel whenever you do one of the following: Record a macro. Create a new function.
If you’re using Excel, you have to try this power tool!
Visual Basic for Applications (VBA) is the Microsoft Office programming language that allows you to create macros and userforms, 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 you just have to learn a little bit about codingLearn to Code in 12 Weeks with this Interactive Learning BootcampLearn to Code in 12 Weeks with this Interactive Learning BootcampNormally priced at $499, you can sign up now for this Interactive Coding Bundle for just $29 — but be quick, because this deal ends July 8.Read More.
This guide 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. We will introduce you to the ways that VBA and Excel can intersect. This short tutorial will put you on a path toward creating your own more complex projects.
Here’s how to get started with VBA in Excel 2016.
Access Developer Controls
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. To do so, head to File > Options > Customize Ribbon. You can also right-click the ribbon and select the Customize the Ribbon… option.
Under Customize Ribbon > Main Tabs (list on the right), add and check the Developer option (unchecked in image above).
Create a Button
To create our currency converter, we first need to insert the button element. In a second step, we’ll attach our VBA code to that button.
Open a new Excel spreadsheet, then navigate to the Developer tab. Use the Insert dropdown in the Controls section to select an ActiveX Command Button.
Drag the button out to an appropriate size and place it somewhere convenient — you can easily change this later on.
Now we’ll attach the code. Right-click the button and select Properties. We’ll make two changes; we’re going to change the Name that we’ll use to refer to the button while coding10 Tips for Writing Cleaner & Better Code10 Tips for Writing Cleaner & Better CodeWriting clean code looks easier than it actually is, but the benefits are worth it. Here's how you can start writing cleaner code today.Read More, and the Caption that displays text on the button itself. You can choose whatever you like for these labels, but remember that you’ll need to swap out ConverterButton for whatever you use in its place while we’re adjusting the code.
Now it’s time to give the button some functionality.
Add Some Code
Coding with VBA takes place in a separate environment to the standard Excel interface. To access it, make sure Design Mode is active in the Developer tab, then right-click the button we created and select View Code.
You’ll see a window like the one below:
The start and the end of our code is already in place — the two blue pieces of text bookend our function, while the text in black states that we’re stipulating the action that should take place when the user clicks on the buttonHow to Create an Interactive PDFHow to Create an Interactive PDFInteractive PDFs allow you to add video, audio, hyperlinks, and more into your documents. Here's how to create one using Adobe InDesign.Read More we created. If you chose a different name to ConverterButton, you should see the respective term in your version of this window.
![Excel 2016 vba tutorial pdf file Excel 2016 vba tutorial pdf file](/uploads/1/2/5/0/125000551/322875610.jpg)
To carry out the currency conversion procedure, we’ll use the following line of code between the two that have already been created for us:
To break it down further, this piece of code states that the new value of the cell that the user has selected will be the current value multiplied by 1.28 — the exchange rate from GBP to USD. Here’s how that looks in the VBA window:
Next, close the VBA editor through the File menu and head back to Excel.
Test Your Work
It’s now time to see whether our code works — but there’s an important step to take before we can do that. We need to disable Design Mode to stop any further modifications to the button, and make it functional.
Next, enter a number into a cell, select that cell, and 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
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 might want to create a simulated die that returns a random value when you press the button, perhaps as part of a larger game6 Iconic Games Recreated in Microsoft Excel6 Iconic Games Recreated in Microsoft ExcelMicrosoft Excel can now also help you procrastinate. Excel games like 2048 are a great way to relax between crunching numbers. And they might playfully get you curious about advanced Excel formulas.Read More. Alternatively, 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 towards 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.
Do you have a question about this VBA project? Ask for assistance or offer up some help in the comments below!
![Tutorial Tutorial](/uploads/1/2/5/0/125000551/917267561.jpg)
Explore more about: Microsoft Excel, Microsoft Office 2013, Microsoft Office 2016, Programming, Visual Basic Programming.
- Thanks!
- Please can anybody tell me step by step in simpler words on how to profiling VBA excel code. Please
- If I want to multiple the a cell by 1.28, I can do it much faster with plain Excel. Show us how to retrieve the current exchange rate from the web using VBA. That would make this a useful tutorial.
- Thank you.
But just mentioned that you have given me the taste and now i need more.