If you're using Excel, you have to try this power tool!
Visual Basic for Applications (VBA) is the Microsoft Office programming language that lets you create macros and user forms, add a message box, run code within a document in response to a trigger, and much more. With VBA you can overload your Excel spreadsheets. And you just have to learn a little about coding Learn to code in 12 weeks with this interactive learning program. Learn to code in 12 weeks with this interactive learning program. Usually the price is $499. $29, but be quick, because this deal ends July 8. Read more.
This guide will help you try out VBA with a simple project:a button that converts the value of a selected 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 to creating your own more complex projects.
Here's how to get started with VBA in Excel 2016.
Before we can dive into VBA, it might be necessary to open Excel and adjust the settings to show the Developer tab as part of the Ribbon. To do so, go to File> Options> Customize Ribbon . You can also right click on the ribbon and select the Customize the ribbon... option.
Under Customize Ribbon> Main Tabs (list on the right), add and verify the Developer Option (unchecked in the image above).
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 tongue. Use the Insert dropdown in the Controls section to select an ActiveX Command Button .
Drag the button to an appropriate size and place it in a convenient place, you can easily change it later.
Now we will attach the code. Right click on the button and select Properties . We will make two changes; let's change the Name which we'll use to refer to the button while coding 10 Tips for Writing Cleaner and Better Code 10 Tips for Writing Cleaner and Better Code Writing clean code sounds easier than it really is, but the benefits are worth it. Here's how you can start writing cleaner code today. Read more, and the Subtitle which displays the text on the button itself. You can choose whatever you like for these tags, but remember to change the ConverterButton to whatever you use instead as we adjust the code.
Now it's time to give the button some functionality.
Coding with VBA is done in a separate environment from 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 will see a window like the one below:
The start and end of our code are already in place:the two blue pieces of text complement our function, while the black text indicates that we're stipulating the action to take when the user clicks the How to create a button. Interactive PDF How to Create an Interactive PDF Interactive PDFs let you add video, audio, hyperlinks, and more to your documents. Here's how to create one using Adobe InDesign. Read More If you chose a name other than ConverterButton, you should see the respective term in your version of this window.
To carry out the currency conversion procedure, we will use the following line of code between the two that have already been created for us:
ActiveCell.Value = (ActiveCell * 1.28)To break it down further, this code snippet sets the new value of the cell the user has selected to be the current value multiplied by 1.28, the GBP to USD exchange rate. This is how it looks in the VBA window:
Then close the VBA editor via the File menu and return to Excel.
Now it's time to see if our code works, but there's an important step we need 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.
Then, enter a number in a cell, select that cell, and click its button to see its magic work. Hopefully, you'll see the value increase by about a quarter, which means the conversion was successful.
Now that you've created a button and used it to run VBA code in Excel, you can use the same basic method to accomplish all sorts of different projects. You may want to create a mock die that returns a random value when you press the button, perhaps as part of a larger game 6 Iconic Games Recreated in Microsoft Excel 6 Iconic Games Recreated in Microsoft Excel Microsoft Excel can now help you procrastinate too. Excel games like 2048 are a great way to relax between numbers. And they can curiously amuse you about advanced Excel formulas. Read more . Alternatively, you can create a button that checks the content of a specific cell against another in another part of the same document.
Embarking on projects like these exposes you to the different features of VBA. Our currency converter is as simple as it gets, but it's the first step to bigger things. If you're just starting out, choose projects that tie your learning to a basic goal or task that interests you. Step by step, you will become familiar with how VBA works.
Do you have any questions about this VBA project? Ask for help or offer help in the comments below!