Summary of Lesson 01

Creating macros and writing programming is done for one main goal: to make our lives a lot easier. The purpose of creating a macro or writing code is to have the computer do a multitude of operations from just clicking one or a few buttons. When I first looked at the word macro I thought it stood for Multiple Automated Computer Recorded Operations, but this has never been confirmed by the gurus.

In this lesson you will learn how to

  • setup of your Visual Basic Development Environment,
  • Record a macro
  • read your macro
  • step through your code.




Step by Step Instructions

First things first, save your work

  1. Open Microsoft Excel 2010 (or higher) to a blank sheet.
Before we begin, save your file as MYADVEXCELLESSON01_ and your initials
For me it would be MYADVEXCELLESSON01_ELM.
Save your file as an Excel Macro-Enabled Workbook (\*xlsm)

Setting up your environment

  1. Look across your menu bar for the words Developer
    • hint: from the left, your menu bar items are: File, Home, Insert, etc… Your Developer ribbon should be to the right of the View ribbon.
    • If the Developer ribbon is not visible then follow these steps:
      1. Select the File menu.
      2. Select Options.
      3. Select Customize Ribbon from the list on the left of Excel Options
      4. On the right look for a set of checkboxes.
      5. Check the checkbox named Developer.
      6. Hit the Okay button.

YOU NOW HAVE YOUR DEVELOPER RIBBON VISIBLE




Wait!! Before we take off…  

 

Let’s make sure we haven’t overlooked anything…  

for example: did you save your file with the  

xlsm  

extension?

It’s not too late to correct that.


Okay great!! Everything looks good now, right?

Let’s continue.



  1. If your Developer ribbon is not selected Click on the Developer ribbon.
    • All the way to the left you will see
      • Visual Basic button
      • Macro button
      • Record a macro button
  2. Hover your over the Visual Basic button.
    • You should see a message describing the button
    • If the message doesn’t show go on to step 5
    • If you can see the descriptive message, it will show you that this is your portal to the Visual Basic Editor.
  3. Now Click the Visual Basic button! Yes, CLICK it.

    • Okay forgive me for the whole hovering step. I just wanted a reason to put that cute mouse in there.


A screen will pop up. This is your Visual Basic for Applications Editor. Take a good look because this is where you write your code.

You will not write any code in lesson 01 but instead create code and then examine what you created. We will come back to the editor later, but for now I just need you to do a few things -

Setting up your Visual Basic Editor environment


This is your Visual Basic Editor menu area


  1. Hover that same cute over any unused space within the menu area and Right click your mouse button.

  2. If debug is not checked then Click debug.

  3. Repeat step 6 but this time do the same thing for Edit if Edit does not have a check mark next to it.


Well, you don’t want to leave those things just laying around like that do you? Let’s tuck them in place.


Using your debug menu let’s do step 9


  1. You see the the grey area of the debug menu? Sure you do. Okay Click the mouse while holding the button down and Drag the debug menu over to the Visual Basic Editor menu area. Repeat step 9 for the Edit menu.


Okay time to go!


  1. Look in the top left corner of your VBA Editor there are menus. From left to right there is File, Edit, View, insert etc…
    • Underneath the File menu there is a button that looks like the Microsoft Excel icon. Click on that button and it will bring you back to Microsoft Excel.
    • You should be back to the Excel that’s familiar to you. Make sure you still have the Developer ribbon displayed
  2. To the right of your visual basic button is a button name macros. Click it. + A screen will pop up:



Here you can find a listing of your macros. From here you can run, step into, edit, create, delete and organize your macros. Don’t worry about the term Step Into. Once you begin coding and chatting with others about coding it will become a part of your vocabulary without you even realizing it.

All macros you create will be listed here. Okay you’re probably thinking: “macros in, what is that?” Let’s say you have several workbooks open, you can choose to display the macros for all open workbooks or for a specific workbook.

You can also include descriptions to better remember when you go back to them later.

Okay we just came here to look around and not actually do anything. Click the Cancel button.



Can you believe you learned this much already in just 11 steps? Now it’s time for you to have some fun and be creative. We are going to record a macro. I have to let you know that recording a macro or even a series of macros does not make you a computer programmer nor a developer, but it’s a start in that direction. 



=======================================

note: First read steps 12 through 27 and then do the actual steps.


  1. Click the button.

    • A dialog box will pop up:


The default name Macro1 is highlighted.

  1. Clear the highlighted default name by pressing the BACKSPACE key and then

  2. typing M Y F I R S T M A C R O , Shift- and your initials, for me would be E L M. My full macro name would look like: MYFIRSTMACRO_ELM

  3. In the little box that reads: Shortcut Keys: followed by Ctrl+ type Shift Q.

  4. In the Description box type: This is my first macro for the Advanced Excel VBA class


17. It should look similar to the one below. If so then hit





  1. Select Cell G8.

  2. Type What is your name?.

  3. hit Enter

  4. Select Column G.

  5. Select the Home ribbon.

  6. In the Cells section of the home ribbon. Click Format

  7. From the list Click AutoFit Column Width. (see the picture on the right, or below if you’re viewing this from a mobile device)

  8. Select Cell G9.

  9. Select the Developer ribbon.

  10. Hit the button.


Great! You’ve just created your first macro

Now, Let’s go look at what you created.

But first…



  1. Hover and
    Right Click your over the triangle above the 1 and to the left of the A.

    note: (see the diagram to the right, or below if you’re viewing this from a mobile device)

  2. Select Delete.

This starts the sheet back to new. Of course you could right click and select clear contents, but that would not reset the column width.




  1. press Ctrl Shift Q on the keyboard and watch what happens.

  2. Repeat step 28.

  3. select the Developer ribbon.

  4. Click the button.

  5. Click the button.

  6. Make room so that you can see both your code in the Visual Basic Editor and Column G of the spreadsheet.




Stepping through your code


This is how you visually and comprehensively connect the code with the actions

  1. If this button is not visible you can use the F8 function key.



Congratulations!! You have made it through lesson 01

The Code



Your Homework Assignment


Go into the Visual Basic Editor and send me a brief description of each of the buttons “items” on your standard toolbar (contact me if you need help for this task)

note: I mistakenly wrote buttons instead of items so for those who have already turned in their homework and only gave descriptions of the buttons will NOT receive deductions on their score.

note: try to make sure that you can see the whole standard toolbar. If you resize the window it will hide some of the toolbar from view




For Questions and Feedback: earl.montgomery@va.gov
or for a more immediate response: earllamontmontgomery@gmail.com