Pinterest Stumbleupon Whatsapp

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 coding Learn to Code in 12 Weeks with this Interactive Learning Bootcamp Learn to Code in 12 Weeks with this Interactive Learning Bootcamp Normally 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 (CA/UK).

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.

Excel Options Customize Ribbon


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.

ActiveX Command Button

Drag the button out to an appropriate size and place it somewhere convenient — you can easily change this later on.

CommandButton1 Example

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 coding 10 Tips for Writing Cleaner & Better Code 10 Tips for Writing Cleaner & Better Code Writing 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.

Name Field in Properties

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.

Excel Design Mode View Code

You’ll see a window like the one below:

Excel Coding Window

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 button How to Create an Interactive PDF How to Create an Interactive PDF Interactive 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.

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:

ActiveCell.Value = (ActiveCell * 1.28)

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:

Excel Code Complete

Next, close the VBA editor window 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.

Excel Design Mode Switch

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 game 6 Iconic Games Recreated in Microsoft Excel 6 Iconic Games Recreated in Microsoft Excel Microsoft 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!

  1. Gothard
    December 14, 2016 at 8:52 am

    Thank you.
    But just mentioned that you have given me the taste and now i need more.

Leave a Reply

Your email address will not be published. Required fields are marked *