What to Expect

You can expect, in due time, posts on the following:

• Hijacking blogger to create and a host a free, professional website (without a trace of blogger!)
• Setting up blogger to use your custom domain
• Getting your page to load without the www's
• Redirecting pages with javascript
• Hiding gadgets on static pages
• Choosing and modifying a template, including removing the blogger bar
• Embedding PDFs in pages
• Setting up Google Apps and gmail
• Redirecting your Google Apps email to your personal gmail account (and catch-all email forwarding)
• The importance of Google Places for Business
• Using Google Voice to get a free business phone number
• Using HelloFax to send faxes
• Network Add-in deployment for Excel Add-ins
• Automatic Word template distribution

An Introduction to Excel Macros and VBA

Have you grown tired of manual processes in Excel, repeating the same tasks over and over again? You can liberated from such draconian processes and take advantage of the true power of Excel. I program in Excel on a near-daily basis. It seems that almost every task can be shortened with a few lines of code. To help you get started in the world of creating macros—and creating Excel Add-ins—I have created a lesson made for people with little to no programming experience, although you do need to be familiar with Excel. The lesson is adapted from curriculum created for a graduate class and has been released into the public domain by the copyright holder.

Lesson scope

This lesson is intended to familiarize the student with Excel macros and VBA programming. To accomplish this purpose, the student will be guided through the creation of a simple Excel Add-in for creating Pareto Charts.
Through the lesson, the student will learn how to record a macro, implement variables in the code, use input boxes, implement an error handler, use if/then statements, customize the Excel ribbon, implement User Forms, and create an Excel Add-in.
This lesson is intended for those with little or no programming experience. If you already have programming experience, feel free to review the sections that are already familiar to you. Be sure not to skip the assignments, however.

What are Excel Macros?

Excel macros are automation scripts that run inside Excel. The scripts are coded in Visual Basic for Applications (VBA) and may be coded manually or recorded through the Record Macro tool in Excel. Once written, Excel macros can automate simple tasks such as reordering a list or they can perform much more complicated tasks such as generating custom reports.

Lesson Structure

This lesson is broken up into three segments:
Segment 1: Recording an Excel Macro
Segment 2: Modifying an Excel Macro
Segment 3: Creating an Excel Add-in

Continue to Segment 1: Recording an Excel Macro > > >

Segment 1: Recording an Excel Macro

This post is the first segment of the three-segment series: An Introduction to Excel Macros and VBA.

Copy Data

The following data set includes defect data for an electronics assembly inspection station. To begin the lesson, copy the following data set and paste it into a new Excel Workbook in cell A1 of Sheet1.

Defect Type	Defect Quantity
Backwards Part	26
Billboard	9
Excess Solder	23
Insufficient Solder	30
Missing Part	23
Solder Bridge	12
Tombstone	13
Unsoldered Pin	8

Record Pareto Macro

1.     Select “Record Macro…” under Macros in the View tab and save the macro as “RecordPareto” stored in “This Workbook”

2.      Highlight the data set (including headers)

3.      Sort in descending order by the second column (Sort is located in the Data tab)

4.      Select Clustered Column Chart from Insert tab (Under “2-D Column”)

5.      Delete the legend

6.      Rename the chart "New Chart Title"

7.      Insert an X-axis title (Layout tab under "Axis Titles")

8.      Rename title "X-Axis Title"

9.      Insert an Y-axis title (Layout tab under "Axis Titles")

10.  Rename title "Y-Axis Title"

11.  Select an area in the chart outside the title (to unselect the Y-Axis Title)

View Recorded Macro

Open Microsoft Visual Basic for Applications by selecting Alt + F11. Navigate to Module1 in the VBA Project window on the left (Click the plus next to VBAProject (Book1), then the plus next to Modules). Open Module1 by double clicking on it.

Read through the code to familiarize yourself with VBA code. Copy the code to a word processor and label each line of code that performs one of the above steps with the appropriate step number. Hint: a single event may produce multiple lines of code. When this happens, label only the first line of code produced by the event.

Continue to Segment 2: Modifying an Excel Macro > > >

Segment 2: Modifying an Excel Macro

This post is the first segment of the three-segment series: An Introduction to Excel Macros and VBA.

Requirement for Variables

The macro you recorded is very limited. It will only work if your data is located in cells A1:B9 of Sheet1 in a worksheet that has not yet contained a Chart (otherwise it would not be called “Chart 1”). Although recording macros is very helpful, in order for most macros to be useful the code must be modified. For each occasion where a specific object is mentioned (such as a range, sheet, or chart) we can replace the object with a variable.

In order to properly use a variable, it must first be called then set to a value. In VBA a variable is called by the following syntax:

Dim ChartData As Range

Dim declares the variable for use only within the module, but Public can be used instead for macros stored across various modules. ChartData is an example of a variable. Variables must be alphanumeric without spaces. As Range specifies the type of variable, in this case a range of cells in Excel. Other object type variables include workbook, worksheet, and chart.

In addition to objects, numbers or text can be stored within a variable. Common variable types are a string of alphanumeric text, an integer, a long (large number), a double (decimal number), and a boolean (true/false). Open the add-in file ParetoAddin.xlam, launch the VBA Editor (Alt + F11), navigate to the code titled “Sub CreatePareto( )” within Module1, and take note of the variables declared. As is the case in the example, it is good practice to declare all variables at the top of the subroutine (or module if used for other subs in the module). Keep the subroutine handy periodically scanning it as new principles are introduced—each are found in the example code.

For a more thorough explanation of the use of variables within VBA, visit http://www.cpearson.com/excel/DeclaringVariables.aspx.

Assigning variables

In order to use a variable it must first be assigned. The method of assigning a variable differs whether it is a numeric or string type variable or an object type variable. When assigning an object type variable, the word Set must preface the variable name:

Set RangeVariable = Range(“A1:C10”)

When the code is executed and RangeVariable is referenced, it will act as if Range(“A1:C10”) was in its place.

To assign a numeric or string type variable, simply type the name of the variable, an equals sign, and the value:

StringVariable = “Hello World”

Any time the variable StringVariable is used it will be replaced with the string “Hello World.” It is often helpful to combine strings. To do this, place an “&” between the two strings:

MsgBox(StringVariable & “, my name is ” & NameVariable)

When this code is executed, a message box will appear saying “Hello World, my name is ” followed by the name stored in the variable NameVariable.

Implementing Variables

Assigning a variable in the code really only gives the advantage of organization. A variable becomes much more useful when it can be assigned by the user prior to or during the execution of the macro. This lesson teaches two methods: by inputing values into cells prior to executing the macro and through the use of an InputBox.

Requiring the user to input a value in a cell prior to executing a macro is a very simple method of setting a variable. A spreadsheet can be created that guides the user in setting variables. For instance, if a spreadsheet guides the user to input text into cell C3, the following code can be used:

Dim InputValue As String

InputValue = Range(“C3”).Text

In this lesson, we will require the user to input data in the spreadsheet, but not in a specific location. Rather, we will ask the user to specify the location through an InputBox.

Through the use of an InputBox, the user can specify the value of a string while the code is running. As mentioned previously, it can also be used to require the user to highlight a range of cells. A simple InputBox can be created with the following code:

InputValue = InputBox(“Enter value:”)

Within the Add-in, take note of the instances when an inputbox is used or a variable is otherwise assigned.

Error Handling

Execution errors can prevent code from completing and expose the user to broken code they do not understand. Sometimes errors are inevitable and should be ignored, other times they are a product of imperfect code. To indicate errors should be skipped during execution, use the following code:

On Error Resume Next

To return to the default method of error handling (a debug window appearing), use this code:

On Error GoTo 0

To prevent the debug menu from appearing when an error occurs, an error handler can be used. Instead of 0, type in the name of your error handler (no spaces allowed). At the end of your code, include the error handler code (see the example macro for the exact method). Be sure to use the code Exit Sub prior to defining your error handler or it will always be executed at the end of the code.

If Statements

If… Then statements are of the most commonly used statements in programming. They direct the subroutine to only execute the encapsulated code (between If and End If) if the logic is fulfilled. The following code, for example, will display a message box stating the value input was too high only if InputValue is greater than 100. It will also force the subroutine to end.

     If InputValue > 100 Then

           MessageBox(“Value is too high”)

           Exit Sub

     End If

Adding Else between If and End If specifies the result if the condition is not met:

     If InputValue > 0 Then

           MessageBox(“InputValue is positive.”)

     Else

           MessageBox(“InputValue is negative.”)

     End If

User-Defined Functions (UDFs)

Sometimes VBA does not have the ability to check for the desired condition. When this is the case, a custom function or UDF can be created and used. For an example in the provided subroutine, look for the statement beginning with If NonNumericValues(ParetoValues) = True. You will find the definition of the function after the subroutine. Implementing UDFs helps reduce redundancy—and saves time.

Calling Other Subroutines

Following the same pattern as UDFs, commonly used code can be stored as its own subroutine and called whenever needed in the main sub. To call a sub within the same module, use the code:

Call MySubRoutine

Variables can be passed through to the subroutine if declared within the parenthesis following the sub declaration. They do not need to be declared other than within the parenthesis:

Sub MySubRoutine(MyText As String, MyNumber As Long)

To call the subroutine, use this code:

Call MySubRoutine(“Pass this text”, 1234)

Assignment

To preface the assignment, learn how to use the add-in Pareto tool as it is currently programmed. With the original data set (found in the section: Copy Data), navigate to the Insert tab in Excel. Select Settings within the group Pareto Chart Tool and verify that the tool is set to create the chart in the active sheet. Save the settings and click the Pareto Chart button, then follow the instructions. You will notice that the graph is created off to the side. Within this assignment you will modify the code to allow the user to select the location.

Open the VBA Editor (Alt + F11) and navigate to the subroutine CreatePareto( ) within Module1 of VBAProject(ParetoAddin.xlam). Notice the three comments that extend to the left of the screen (they begin with multiple apostrophes and are in all caps). Using what you have learned previously, replace each of the comments with the code required to allow the user to select the location. The following directions may be helpful:

1.      Declare an object type variable for the range

2.      Use an InputBox Type:=8 to allow the user to select the range (similar to the ChartData InputBox). Make sure to let the user know what he or she needs to do and why.

3.      Embed the InputBox in an If… Then statement to execute only if the NewSheetSetting = False (a setting saved through the Settings dialog by selecting the option “Create Pareto Charts in New Sheet”)

4.      Call the sub: PositionChart(ChartObj As ChartObject, RangetoCover As Range) replacing the arguments with those needed. Hint: Use ActiveChart for ChartObj.

5.  Save the code (Ctrl + S) and run it from the button to make sure it worked. If you receive an error message, turn on debugging by swapping each On Error GoTo ErrHandler for On Error GoTo 0. Happy debugging! (This is where you learn the most)

Copy the new CreatePareto( ) code into your Assignment document.

Once finished with the assignment, close Excel to prepare for the next segment.

Continue to Segment 3: Creating an Excel Add-in > > >

Segment 3: Creating an Excel Add-in

This post is the first segment of the three-segment series: An Introduction to Excel Macros and VBA.

Storing Macros

Macros can be stored in several places. One of the most common places is in your Personal Macro Workbook (an option when recording a macro). However, with this method the macro only works with your user login on your computer. There are a few ways to share your macro with others. The macros can be recorded within the active workbook (as done previously) and saved to the workbook or as an Excel Add-in.

To save the macro within the workbook or create an Excel Add-in, select Save As from your active workbook. To save the recorded macros within the workbook, save the file as an “Excel Macro-Enabled Workbook (*.xlsm).” To create an Add-in, save the file as an “Excel Add-in (*.xlam).”

Saving as an Add-in File

Download the file CreatePareto Macro.xlsm and open it. Once opened, select Save As. From the drop down list (for Save as type:), chose to save the file as an Excel Add-in (*.xlam). Notice that the folder where you are saving your file has changed. Add-ins are stored by default in this folder, but it is not necessary to save it here. In fact, it can make finding the file very difficult. Save the file in a location more familiar to you.

Notice that your opened file is still labeled as an *.xlsm file. When saving as an Excel Add-in, Excel only saves a copy of the active file. That means if you make any changes to the file and save it, the changes won’t be saved to the Add-in file.

Navigating an Excel Add-in

Excel Add-ins can become very difficult to work with because of their unique saving properties. However, there is a way around this. First, close the *.xlsm file and open your recently saved *.xlam file. Next, open the VBA Editor (Alt + F11) and navigate to VBAProject (CreatePareto Macro.xlam) in the side bar. Click the plus next to Microsoft Excel Objects and select ThisWorkbook. Locate the property “IsAddin” and select False from the drop-down menu.

You will notice that the workbook CreatePareto Macro.xlam is once again open as a spreadsheet in the main Excel window. The workbook contains only one sheet, named “Settings.” You may have noticed that the VBA code references this sheet. The code used is:

ThisWorkbook.Sheets(“Settings”)

The difference between syntax is simple: referencing ThisWorkbook allows references to the Add-in’s sheets and referencing ActiveWorkbook allows references to the workbook currently open to the end user.

Having access to the Add-in workbook presents a tremendous benefit for Excel Add-ins. Settings can be stored in a worksheet within the workbook and retrieved during macros. Notice that one setting, called NewSheet, is already stored in the worksheet. Cell A1 contains the label (for organization only) and cell B1 contains the value, which is referenced several times in the macro code.

In the orginial ParetoAddin.xlam there was a Settings button that allowed the user to choose whether to create pareto charts in the active sheet or in a new sheet. When the user selects one of the options, then clicks Save, cell B1 in the Settings worksheet  is updated. When the user reopens the Settings dialog, the current setting is pulled from cell B1 and displayed. The Pareto Chart and Settings buttons are not included in this file, because the remainder of this lesson teaches how to create them.

Creating Groups and Buttons in the Excel Ribbon

Creating custom groups and buttons in Office 2007 and 2010 is very simple. Doing so requires the use of a free, open-source tool, created by the Microsoft Office User Experience team. The program, called “Custom UI Editor For Microsoft Office,” is available from openxmldeveloper.org. If you are unable to download the file from the link, search for the file on the site. Install the program or speak with the professor about using a computer that already has the program installed.

Launch the UI Editor from the start menu and notice the simplicity of the program’s interface. Although it appears simple, the UI Editor introduces a new concept that won’t be covered here: XML. For a great tutorial on learning to customize the ribbon with the UI Editor, visit http://www.rondebruin.nl/ribbon.htm. This lesson will only guide the student through the process and will not teach the student to create other ribbon buttons, groups, or tabs.

Click the open button from the UI Editor and open your add-in file (CreatePareto Macro.xlam). Right click the file on the left pane and select Office 2007 Custom UI Part. Although selecting 2007 limits some newer features, it provides functionality within Excel 2007 and 2010. Another file, called customUI.xml should appear below CreatePareto Macro.xlam. Insert the following code into the right pane:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

     <ribbon>

           <tabs>

                <tab idMso="TabInsert">

                     <group id="groupPareto" label="Pareto Chart Tool" insertAfterMso="GroupInsertChartsExcel">

                           <button id="buttonPareto" label="Pareto Chart" size="large" onAction="RunPareto" image="ParetoButton" />

                           <button id="buttonSettings" label="Settings" size="large" onAction="ShowSettings" imageMso="TableDesign" />

                     </group>

                </tab>

           </tabs>

     </ribbon>

</customUI>

Take a look at the code and notice the group and buttons it creates. The Settings button uses Microsoft image (Mso) named “TableDesign.” This image is already built into Excel. However, the Pareto Chart button uses a custom image. To add this image to the file, right click customUI.xml and select “Insert Icons….” Open the file ParetoButton.png.

There is one more important element of the XML code. Notice the each button has an onAction property. The onAction property references the macro, contained within the Add-in file, to be referenced when the button is clicked. The Pareto Chart and Settings button reference RunPareto and ShowSettings, respectively. These two macros must be added to the file for it to be functional. Save the changes made in the UI Editor and close the program.

Reopen “CreatePareto Macro.xlam” and make sure the new group appeared in the Insert tab (the file ParetoAddin.xlam should not be open). Open the VBA Editor (Ctrl + F11) and navigate to Module1 in “CreatePareto Macro.xlam.” You will need to add the RunPareto and ShowSettings macros to the Module. Here is the code:

Sub RunPareto(control As IRibbonControl)

     Call CreatePareto

End Sub

Sub ShowSettings(control As IRibbonControl)

    If ThisWorkbook.Sheets("Settings").Range("B1") = True Then

        Settings.NewSheet = True

    Else

        Settings.inActiveSheet = True

    End If

   

    Settings.Show

End Sub

Notice that all the RunPareto code does is call the subroutine CreatePareto. Technically the contents of CreatePareto could be included here. However, the ShowSettings code does not launch a subroutine. It does two things. First, it pulls the previously saved settings from the Settings sheet and sets the dialog to display those settings. Second, it displays a UserForm.

Creating UserForms

To create the settings dialog, you must create a UserForm. Right click on VBAProject (CreatePareto Macro).xlsm and select “Insert,” then “UserForm.” Rename the UserForm “Settings” and build it to look similar to the Settings dialog in the ParetoAddin.xlam file. Use Option Buttons to allow the user to only select one option and use Command Buttons to create the Cancel and Save buttons.

Adding Functionality to a UserForm

By clicking on each button, you can assign properties. It is generally a good idea to make the Save button default, so that if the user hits Enter the settings are saved. Make sure to name the buttons appropriately, so that they are easy to reference in code. Name the option to create Pareto Charts in a New Sheet as “NewSheet” and the other option “inActiveSheet” (they are referenced in code so the naming is critical).

Each button requires code to function. To assign code to the Cancel button, double click on it. A code window will open, allowing you to enter code. Enter Me.Hide. This closes the window without saving any changes. To return to the UserForm creator, navigate to Settings within Forms of the VBAProject. Right click Settings and select “View Object.” Double click the Save button and enter the following code:

'Saves Pareto Chart setting in Settings sheet (Cell B1)

If NewSheet = True Then

    ThisWorkbook.Sheets("Settings").Range("B1") = True

Else

    ThisWorkbook.Sheets("Settings").Range("B1") = False

End If

'Save and Hide

ThisWorkbook.Save 'Saves Add-in "workbook"

Me.Hide 'Closes settings window

This code is what makes the settings dialog work. Notice the If… Then… Else statement used. NewSheet refers to the option in the UserForm and when it is True (selected), the code stores “True” in cell B2. If it is not True (aka False), the code saves “False.” The Add-in is then saved (saving the settings) and the UserForm is closed. If you struggle with creating the UserForm or its accompanying code, take a look at the ParetoAddin.xlam file.

Test the New Add-in

With the added buttons in the Ribbon and the new UserForm, the Excel Add-in is ready for use. Test it out and make sure everything works properly. If anything is broken, troubleshoot your setup until you can resolve the problems.

Deploy the Add-in

There are a few ways to deploy an Add-in in a business environment. The Excel Add-in file can be provided to everyone who will use the Add-in. Each user can add the Add-in through Excel. If updates are made to the Add-in, however, they will not be passed on to the installed Add-ins. To avoid this, the Add-in file can be installed on a network location. Refer to the post “Network Add-in Deployment.doc” for instructions on this method.