Excel Visual Basic #1: Getting Started

vbimg

Below you will find two videos. The purpose of these videos is to show you how to gain access to the VBA development capabilities of Excel and to show you how to create your first “Hello World” application inside of Excel. I also provide written steps in case you don’t like the sound of my voice (or you just prefer that method of learning). This will be built upon in later posts I have planned that will get a bit more advanced with Excel Visual Basic Applications.

The below process was done in Excel 2016, the approach in Excel 2013 is similar.

The First Half of the Tutorial – Enabling the Developer Option in Excel

Enabling The Excel Developer Tab (Video)

Enabling The Excel Developer Tab (Written Steps)

If Excel is not already opened, open it now.

1. In the top left-hand corner select “File”

File

2. On the left tab click on “Options”

Options

3. A menu will open up, Choose “Customize Ribbon” from the left-hand side menu

CustomizeRibbon

4. Click the box next to “Developer” and ensure a checkmark appears, then select “OK”

Developer

5. A new tab should appear called “Developer”

DevTab

A note about working with different sheets

You will notice in the developer pane on the left-hand side a series of pages below the workbook name. These are different pages that you can work with and add code to. If your Excel file has multiple sheets you can create code for each sheet. The first 3 tutorials within this first group of tutorials do not work much with these. Tutorial number 4 will go through them in greater detail and show you some of the uses.

Tutorials 1 through 3 will be isolated to working within “sheet1”

Sht1

 

When a new sheet within Excel is created, a new page is added here. There is also the standard sheet title “ThisWorkbook” that is created for each Excel workbook.

ShowThisWorkbook

You can also create your own additional sheets. In more advanced tutorial groups I may dive into this more (as of this writing I have no plans in place to do so), but tutorial 4 will walk you through the creation of a module.

 

The Second Half of the Tutorial – The “Hello World” Application

“Hello World” Application (Video)

“Hello World” Application (Written Steps)

1. Click the “Developer” tab and then click the “Insert” toolbox icon

DevTab_Toolbox

2. In the little menu that appears under “Insert” find the section labeled “ActiveX Controls” and click “Command Button”

FormControl

3. Click an area within the spreadsheet, wherever you want your button to appear at (you can move your button around after it is placed)

ButtonPlaced

A. (Optional) If you want to customize the look and text of the button, select the “Properties” option on the ribbon, making sure that the button you placed is selected

Properties1

B. (Optional) The “Properties” menu gives you the ability to define the button in more detail. Play around with it to see what you come up with. You aren’t going to destroy your computer (probably not)

You can view the video for further assistance.

Properties2

4. Double-click the button you placed in the spreadsheet. This will open up a development panel and auto-create some code for you.

The code it created is called a subroutine. This subroutine’s purpose is to handle the clicking of the button you created. When you click the button you created, Excel will check this subroutine automatically. In this original default state, the button will not actually do anything.

Subroutine

5. To get the button to do something, you need to add code below “Private Sub” and above “End Sub”

Type the following code in:

MsgBox "Hello World"

“MsgBox” creates a basic prompt. A little pop-up that can be used to alert people to issues or give messages.

Whatever you put in between the quotations, the pop-up will display as text. I chose “Hello World”, but you could just as easily put “This Blog is Awesome” in and have it display that.

Coded

The completed code should resemble the following (If you played around with “Properties” the text before “Click” may look different):

Private Sub CommandButton1_Click()
      MsgBox “Hello World”
End Sub

6. Navigate back to your Excel spreadsheet and make sure “Design Mode” is de-selected.

Once “Design Mode” is shut off, you will not be able to move, edit, or resize your button until you turn it back on. If “Design Mode” is still on, you will not be able to click your button to execute the code.

DesignMode

7. Test your code by clicking the button

Clicking your button should give you a pop-up that looks like the below:

popup

You have now completed your first Visual Basic Application in Excel

 

So why a tutorial on Excel in a blog that talks about Agile and Lean a lot – because the art and science of handling rapid and unpredictable changes mean that you sometimes have to use the tools on-hand to come up with quick solutions to problems, and Excel is a fairly basic tool found on most work laptops.

The goal is to start pushing your concept of “What is Possible.” Once you start thinking about “What is Possible”, you can come up with new ideas. Eventually, in later tutorials, I hope to expand on the elements learned here and push your conceptualized boundaries of “What is Possible.”

Now it is time to continue on to Step 2: Excel Visual Basic #2: Variables and Decisions



Categories: Excel, Technology, Tutorials, VBA

Tags: , , , ,

2 replies

Trackbacks

  1. Excel Visual Basic #2: Variables and Decisions – Agile-Mercurial
  2. Excel Visual Basic #3: Loops, Combo Boxes, and Saving – Agile-Mercurial

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: