1 Adv Excel VBA Lesson 02 (FTP)

1 My goal in this lesson is for you to design a form, understand and set the properties placed in the form..

1.1 Homage

1.1.1 Languages, Software and Tools that I’ve used to create this website:

Microsoft Excel

Microsoft Word

R, RStudio

Packages

devtools

knitr

rmarkdown

YAML

MiKTeK

LaTeK

CSS

Paint.Net

Sublime Text 2

Regular Expressions

XML

HTML

Google Chrome

XPath

DOM

Bootstrap

1.1.1.1 If you’re looking at your computer screen right now like –

It’s okay I’m not expecting you to know any of that, I’m just giving credit to the tools I used.

1.2 Creating from Scratch, Step by Step

There will be less explanations in this lesson and more hands on instructions so you will see more terms like click, select, type, enter, etc..

Also Note: There are a few questions in the quiz where you have to look beyond this lesson for the answer.

1.3 If You Are Starting From Lesson 02

1.3 For those who haven’t taken lesson 01 open up Excel and in cell G8 of sheet1 type: What is your name?.

1.4 If You Have Completed Lesson 01

1.4.1 Open your File from Lesson 01

1.4.1.1 You will most likely have to go through the security measures depending on your security settings. You may see a message that states:

1.4.2 see the following for the location:

1.4.2.1 You may see another message asking if you Trust the user. Well, you created it so I hope you trust yourself.

2 A Reason Why I Teach

Before I begin to take you on this journey, I would like to address a question that is often asked of me when I teach VBA: “If you teach what you know then what use will they have of you?” This question takes me back to my days working at Wal-Mart and walking and talking with Mahmoud, the Asst Store Manager. I was just the guy who threw (unloaded) the truck, but I wanted to learn more so I sought out and learned the Telson (a laser gun with a bunch of information on store products). I asked Mahmoud: why don’t we teach all the employees to make our store a more powerful store and make it better for the customer because everyone will be able to help the customer? Mahmoud’s response to me was: “If I teach them all everything I know then what good am I?”

I have never really cared for that logic. First of all I see programming as an art. I look at someone’s code as another would look through paintings in a museum. Each programmer develops their own style. And, even as you learn every tool, method, and function, you will still have to use your imagination to create. So young artists, let’s begin!

2.1 Let’s begin developing

3 Creating and Designing a Form

You should be on Sheet1. Click Alt F11. This is a shortcut key to bring you to the Visual Basic Editor.

3.1 Insert a Userform

From the Menu bar select Insert and then…
select Userform from the list.

3.2 Edit the Userform properties

Look for the properties section (see image below).

In the right column across from (Name) located in the left column

replace UserForm with the words
M Y _ u f.
(When renaming your modules and userforms it is a good idea to give them names that are related to content)

Four rows down from the name, change the Caption from

UserForm1

A G o o d F i r s t Q u e s t i o n.

Across from Font click on the words Tacoma

click on the elipses button that appears

Select Times New Roman font
Select Bold for the font-style and
12 for the size

Two rows down change the Height to 300

Scroll down to the Width and replace the number 240 with 500

3.3 The Visual Basic Editor Toolbox

The toolbox is usually nearby the userform but if you’re using two monitors, you might find it over on the other monitor. Here is a picture of it, so you will know what to look for.

Per member feedback: When you click on your UserForm the toolbox appears and when you deselect the UserForm it disappears. You can minimize other windows that you may have open and try selectiong the UserForm again. You can also see if it is visible by selecting View from the menu and then look for Toolbox on the list of menu items. If the icon next to the word Toolbox is greyed out then it is not visible and will not be visible even if you select the UserForm. If this happens you can then select Toolbox from the menu and it will be visible again. There is also another way that you can toggle the visibility on the Toolbox.

Oh you thought I was going to tell you the other way?

Hover your over the buttons and a tooltip will appear giving a short description. Find the one labeled commandbutton and click on it.

Note: a the commandbutton is the same as any other command button you’ve used in windows where you push the button and an action happens as a result. Sure, I know you all know what a button does in computers. I’m really just using this note to add some distance from the one mouse above and the mouse below. Notice this upcoming mouse is donning a hardhat. You guessed it, this mouse, while working on userforms, is going to put in a lot of work.

Hover your over the userform and notice how the pointer has changed from an arrow to plus sign and a button image.

click anywhwere inside your MY_uf userform. You should now have a button in your userform.

Now let’s set the properties for the button.

3.3 Set the button’s properties

Before you can set the button you have make sure the button is selected or else the properties for the button will not display.

Across the column from (Name) change CommandButton1 to cmdGetQuestion.
Look along the left column for Left and across from it replace the number in the right column with 282
Now look for Top in the left column and replace the number across from it with the number 240.
Look for Caption on the left column and across from it change CommandButton1 to Push button to receive a question and hit Enter.

You cannot see all of the words, right? Okay, let’s fix that.

Scroll down and look for width along the left column and change the number across from it to 200.

3.3 Add a Label and adjust the properties

Go to the toolbox and Click the capital A, that is a Label.

Make sure the Label is selected and find the properties window.

Across from (Name) change Label1 to lblDisplay.

21. Look for the word Top in the left column and change the value in the right column to the value 12.

The value across from width let’s set that to 300.

By default the value across from word wrap should be set to True

3.3 Add a combobox and set the properties

Hover your hard working over the toolbox and Click on the Combobox.
Click on an available space on the UserForm.

Make sure combobox is selected

In the left column of the properties windows look for the word Left and across from the word Left change the value to 312.

Look for the word Top in the left column and change the number to 12 (to make it even with the label’s Top setting)

Now look for the word Width in the left column and replace the number on the right with 168.
Find the ToggleButton button on the Toolbox and Click it.
Hover the over any open area in the MY_uf form and Click the left mouse button.

Once again, remember to make sure the toggle button is selected in order to access the properties for it.

3.3 Set the properties for your toggle button

Locate (Name) in the left column of the properties section and change ToggleButton1 to myToggle.

Look for Caption in the left column and change value on right from ToggleButton1 to on.

Let’s put the toggle button in a good place

Since you’re getting the hang of this I’ll give you the coordinates for the Top, Height, Width, and Left all at once. Set the Top equal to that of our button: 240, the Width to 42, Set the Height to the same number as the button: 24, and set the Left to 234.

The BackColor is set to &H8000000F&. Click the dropdown button to the right of it. See the diagram below for a visual aid.

Click the tab named palette that is to the left of the System tab.

Select the green square that is the 5th over from the left and 3rd down from the top.

We’re almost finished designing the form, but we need to add just one more button. When you open the form you will need to be able to close the form, so we need to add a Close button.

From the Toolbox Click the button named CommandButton and then Click on an unoccupied space in the MY_uf userform.

3.3 Set the properties for the Close button

You should have the hang of it now, so in the properties window look for (Name) and change the value in the right column to cmd_Close. Look for the Caption and set the value on the right to Close. Set the Top, Height, Width and Left. Set the Top to 240, the Height to 24, the Width to 48 and the Left to 180.

3.3 We can now begin programming

3.3 Programming to Show the Form

3.3 Inserting a Module

From within the Visual Basic Editor select insert from the Menu bar

Select Module from the list of drop down choices.

3.4 The Project Explorer

By default the Project Explorer is the upper left window of the Visual Basic Editor. These windows are docked (locked in place) but can be undocked and moved around. If the project window is not visible, you can. . .

select View on the Menu bar and Project Explorer. You should be able to see the image below.

Select Module1

In the Properties window you can customize the name of the module.

I like to give mine short names and sometimes descriptive names. For this lesson we will name the module MYMOduLE, why not?

With Module1 selected in the Project Explorer goto to Properties Window and across from (Name) change the value Module1 to MYMOduLE

You will see that the Project Explorer instantly updated the name of the module.

Double Click the left over the “MYMOduLE” module

You “SHOULD” see a blank workspace with two dropdown menus right above your workspace; one reads (General) and the other reads (Declarations) as shown in the figure below. The dropdown with (General) at the top of the windows keeps a list of your Objects and the one that reads (Declarations) holds a list of the Object’s Procedures.

Type the following code in your workspace:




Sub ANYNAME
  Load MY_uf
  MY_uf.Show
End Sub

Notice the space above the code. I usually like to leave a little room to entering more code. You can do this at any time, but I normally like to leave space between subs (and functions). After you enter your Sub name and hit Enter an opening and closing parenthesis will appear after the name and End Sub will appear on the next line giving you a line inbetween to write your code (see the description below)

Go back to the Project Explorer

There are several ways that you can get to the form’s workspace. Right now we will explore one of the different ways to get to the code area for the form.

Right Click that ever hardworking over the MY_uf form module and select View Code from the dropdown list.

Right above the work area Click the dropdown on the left that displays the word (General).
From the dropdown list select cmdClose as described in figure below.

3.4.1 Your work area will look like the following:


Private Sub cmd_Close_Click()

End Sub

Within the lines: hit the Tab key to indent your code and then type: MY_uf.Hide then hit the Enter key and type: Unload MY_uf. Your code should look like the following:


Private Sub cmd_Close_Click()
  MY_uf.Hide
  Unload MY_uf
End Sub

3.5 Run Your Code

Hit the play button to run your code. Your play button looks like a green triangle pointing to the right as shown in the picture below.

3.5.1 The only button that will respond to your mouse is the Close button. That is the only button that has been programmed. The other buttons will not respond to your requests. We will program the other buttons in the next lesson. For now, push the Close button to exit the form.

4 End of Lesson 02

4.1 Lesson 02 Quiz (20 QUESTIONS)

4.1.1 Name two (2) different ways to create a UserForm

4.1.2 What does a Sub stand for

4.1.4 What is the shape of the button that you use to run your code

4.1.5 What window would you use to change the name of a module

4.1.6 Given what you have learned so far, do you have to have an object on a form selected in order to change it’s properties

4.1.7 What names can you NOT give a subroutine

4.1.8 Name the four (4) properties used to position a commandButton on the Userfor

4.1.10 What are the two (2) keywords used to display a Userform

4.1.11 In the properties windows which column (the right or the left) would you use to change data

4.1.12 Besides Userform what are the other three (3) objects you can create

4.1.13 What window stores a list of Controls to add to a Userform

4.1.14 When using hexadecimals for color values on the objects of your userform, what symbol is represented on each side of the hexadecima

4.1.15 What is the keyboard shortcut method to get to the Visual Basic Editor

4.1.16 Which function key would you use to step through your code? (from Lesson 01)

4.1.17 When developing what is the first thing you should do after you open a new workbook? (from Lesson 01)

4.1.18 What do the letters VBA represent?

4.1.19 Name the two (2) ways to stop a macro from recording (from Lesson 01)

4.1.20 Name the four (4) different objects you have used on this project?

4.2 Extra Credit

4.2.1 Why do you want to become an Excel VBA Developer

4.2.2 Explain a task that you do every day on the computer that could use automatio

4.3 grading…

4.3.1 You will receive 5 points for each correct answer. If you get 2 answers wrong your bonus questions will go toward your quiz which would still give you an A. If you get all 20 questions correct and your bonus questions, you will receive an A+

For Questions and Feedback: earllamontmontgomery@gmail.com

ADVANCED EXCEL VBA LESSON 02

Earl Lamont Montgomery

November 3, 2016