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 > > >