You can create, delete and hide ranges using Visual Basic. Named ranges make it much easier to reference your cells as you're coding dynamic spreadsheets. In this article, we'll discuss global and local variables, creating named ranges using VB, and using comments to explain your code to other programmers.
Global and Local Variables
There are two main types of variable scopes: global and local. When you create a variable within a function or subroutine, its scope is local. You can only use this variable within the subroutine or function where it's defined. If you attempt to reference it outside of the subroutine, Visual Basic gives you an error.
If you need to use a variable in several subroutines, you can make the variable global. A global variable is available to all subroutines within the VB module. You must define the global variable outside of the subroutines for it to be set as global.
Let's take a look at both types of variables. We've seen local variables, but let's take a look at another example.
Sub LocalExample()
A = 100
B = A + 20
MsgBox "B equals " & B
End Sub
In the example above, we define two variables. We then add 20 to the A variable and assign the result to the B variable. We then display the result to the user. Remember that the ampersand sign concatenates two strings, so the result shown to the user is "B equals 22."
If we tried to reference B in another subroutine, Visual Basic would give you an error. Let's look at an example that will give you an out of scope error.
Sub LocalExample()
A = 100
B = A + 20
MsgBox "B equals " & B
End Sub
Sub ErrorSub()
MsgBox "B equals " & B
End Sub
The second subroutine in our example contains no reference to the B variable, so the reference is out of scope. Visual Basic will either give you an error or display nothing for B. The above example is what happens when a variable goes out of scope. When the subroutine ends, the compiler destroys it in memory, so it's not available anymore.
You can overcome this obstacle using global variables. The following is an example of how you can make the B variable a global one.
Dim B As Integer
Sub LocalExample()
A = 100
B = A + 20
MsgBox "B equals " & B
End Sub
Sub ErrorSub()
MsgBox "B equals " & B
End Sub
Now, you can run this module and you have access to B in both subroutines. In the first one, B is again given the value of 120. When we run the ErrorSub subroutine, B is global and wasn't destroyed when the LocalExample subroutine ended. Now, your users will see the value of B from the ErrorSub subroutine.
Hide, Add, and Delete Named Ranges
We mentioned that you can dynamically add named ranges in VB. In this section, we'll show you how to add named ranges using Visual Basic instead of using the Excel visual editor. There are two ways to assign ranges to your spreadsheets.
Take a look at the following code.
Sub AddRange()
Names.Add Name:="myrangeA", RefersTo:="=Sheet1!$A$1:$A$10"
Sheet1.Range("$B$1:$B$10").Name = " myrangeB"
End Sub
In the above AddRange subroutine, we add two named ranges using the two possible methods. The first one uses the Names.Add function. In our example, we add the range myrangeA name to Sheet1. The cells in the range are A1 to A10. Notice that we need to define the sheet when we create our named ranges.
The next method uses the Sheet's Range function. In this example, we create the named range myrangeB to the cells B1 to B10. Again, you must define the Sheet. We use the Sheet1.Range function, which tells you that Sheet1 is the sheet to use within the range.
In many cases, you want to create a named range and then use that name to hide a group of cells. You could want to hide them for security reasons or just to make the spreadsheet easier to work with for the user.
Let's take a look at the code you can use to hide and unhide a named range.
Sub HideName()
Names("myrangeA").Visible = False
End Sub
Sub Unhide()
Names("myrangeA").Visible = True
End Sub
We have two subroutines in the above code. The first one hides the myrangeA range that we created in the previous section. The Visible property is how you toggle visibility for any named range. When this property is set to False, the range is hidden. When the property is set to True, it's displayed to the user. These two subroutines use the Visible property to toggle named range visibility on and off.
Finally, you sometimes want to delete named ranges. This is especially true when you've dynamically created one just for a few tasks and no longer need it. The following code shows you how to delete a named range.
Sub DeleteRange()
Names("myrangeA").Delete
End Sub
This subroutine is self-explanatory. We use the named range function Delete to remove it from our spreadsheet.
Identify if a Named Range Exists
Before you use a named range, you should identify if it exists. If you attempt to use a named range that doesn't exist, your program will give your users an error. We can use VB to identify if the named range exists before we work with it.
Let's assume we didn't delete the myrangeA named range that we created in the previous section. The following code identifies if the range exists before we use it.
Sub CheckIfRangeExists()
Set exists = Range("myrangeA")
If exists Is Nothing Then
'name does not exist
Else
' it exists, run this code
End If End Sub
In the code above, the CheckIfRangeExist subroutine identifies if the myrangeA exists. We first set the range to a variable named exists. We then identify if the range is equal to "Nothing." Nothing is the equivalent to null in Visual Basic. Nothing just means that the variable is null and has no memory resources allocated to it. You know if a named range is equal to Nothing that the range doesn't exist.
We then have an If statement that runs code depending on the existence of the named range. If the named range exists, then we can continue to run code such as hiding or unhiding it. If it doesn't, then you can quit the subroutine or send a message to the user. The way you handle your logic statements should be user friendly, but it is entirely up to the programmer to determine the way a program works.
Commenting Your Code
Your code should always be commented. We haven't used comments in our examples, because we've explained what the code is doing. However, when you code VB applications, you should always comment your code. Each subroutine, module and any main functions in the workbook and sheet objects should have comments. Comments allow other programmers to maintain your code, edit it, and troubleshoot any errors.
Visual Basic comments start with the apostrophe. When you use the editor, the comments are shown with a green font, so they are more easily identified when you run through multiple lines of code.
Let's use one of the previous section's subroutine and add comments to it. Take a look at the following code.
'check if a named range exists
Sub CheckIfRangeExists()
'set the range to a variable
Set exists = Range("myrangeA")
If exists Is Nothing Then
'name does not exist
Else
' it exists, run this code
End If End Sub
Notice that we took the last subroutine and added some comments to it. When you create the comments, you describe what the code is doing. If it's a logic decision, always explain the logic. We describe the logic in the If statement to explain when each code block runs.
We covered a lot in this article. The first sections described named ranges and how to create them in Visual Basic code. We covered hiding and unhiding them, and then we discussed comments. You'll always need to comment your code regardless if it's a small or large application. Remember to describe the code's logic and function, so other programmers can maintain and troubleshoot your application.
Event Programming
Events in any language are user input or software related actions that interact with a workbook. Hovering a mouse over a button is an event, clicking the button is an event, and opening and closing a workbook are events. An event is not triggered by the program. It's triggered by something happening outside of the VB code's control. It's usually triggered by your users. When a user sends event input to the program, then you use event handlers to perform some type of action. In this article, we'll learn more about events.
Event Levels
Events are tied to levels. Each component is an event level. A workbook is an example of an event level. When some type of input is sent to the workbook, the event handler must be at the same level as the workbook. For instance, opening a workbook triggers an open event. If you place your event level code in the wrong module, the event handler won't fire and your code performs the default action. The default action for opening a workbook is that nothing happens.
Other event levels include sheets and modules. When a user performs an action on a worksheet, any event handlers you want to use to override the default must be kept in this module. If you recall, you can see the different modules in the Visual Basic Editor's project explorer.
Just remember that all event programming must be in its appropriate level. If you are dealing with a workbook event, you must code the event within the workbook module. Sheet level events must be in the appropriate worksheet, and any module events must be in the correct module.
You cannot change the name of events. You can only override their default behavior. Let's take a look at a simple workbook level event. Since this event is on the workbook level, you must place the code in the ThisWorkbook module in project explorer. The following code is an example.
Private Sub Workbook_Open()
Set mychart = Worksheets(1).ChartObjects(1).Chart
End Sub
In the code above, we've used the Workbook_Open event to override its default behavior. As we said earlier, the default behavior for a workbook when opened is to do nothing. However, when we specify this event in the ThisWorkbook module, the VB compiler finds the even code and then overrides the default behavior with the code that we define.
In this example, we set a variable to a chart located in the first worksheet. With the Set command, the object is assigned to a variable, which in our example is named mychart. If we wanted to format or resize a chart, we could with this variable. If we changed the chart size within the Workbook_Open subroutine, it would happen as the workbook opened.
The event name is always formatted as "EventLevel_Name." The EventLevel variable is the name of the level such as Workbook or Sheet. The Name is the action that triggers the event. Open, NewSheet, BeforeClose, and SheetActivate are just a few workbook level events. Event handlers are also always given the Private modifier. They cannot be called from other sections of your code since they are only local to the module in which the events are performed.
This is just one example of working with an event and its event level. In the next section, we'll discuss events in more detail.
Using Events
Visual Basic and Excel have dozens of events to choose from. Even the most skilled programmer must look up event handler names at some point. You can identify event handler names using the Visual Basic Editor.
Open the editor and double-click ThisWorkbook in project explorer. In the center window where you type your code, change the "General" option in the menu to "Workbook." Now, click the Declaration dropdown menu. Notice that there are several event names displayed in the dropdown. You can identify what the event does by its name, but you can also perform a lookup on Microsoft's site if you are unsure of what it does.
For instance, one event in the dropdown is named NewSheet. NewSheet is triggered when the user creates a new sheet within the workbook. You can add code to the event to override the default behavior.
Choose NewSheet from the dropdown. Notice that the Visual Basic editor automatically sets up the event subroutine in the editor window. You should see the following code when you choose the event.
Private Sub Workbook_NewSheet()
End Sub
Since the event handler is empty, this code is triggered but nothing happens when the user creates a new sheet. We need to add some code to change the default behavior. Let's add some code to add a value to the fist worksheet when a new one is created.
Private Sub Workbook_NewSheet()
Worksheets(1).Select
Range("A1").Value = "A new worksheet was opened."
End Sub
To test this code, save it and return to the main Excel workbook. Create a new sheet in the workbook and watch the text display in the named range A1. We used named ranges in this example, but you can use any code in an event handler in the same way we created other subroutines.
The first statement selects the worksheet. We select the first worksheet in the workbook, but you can select any worksheet that you want as long as it's within the scope of the current workbook.
In our example, we add "A new worksheet was opened" in the first cell in the spreadsheet. You can perform any activity in the event trigger provided it's within scope.
Workbook, Worksheet, and Chart Events
We saw workbook and worksheet events in the previous sections, but there are several others that you can use to automate your Excel spreadsheet procedures. Excel also has chart events. These events are used to dynamically change data used to create a chart, resize it, or even create a new chart when a user triggers an event.
We looked at some workbook events in the previous sections, but let's take a look at another one.
Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim version As Integer
currentversion = Range("A1").Value
Worksheets(1).Select
version = Range("A1").Value
If currentversion = version Then
MsgBox "Your version of Excel must be updated."
Cancel = True
End If
We use the BeforeSave event to trigger this code. The BeforeSave event occurs before the file is saved to disk, so you can use this event to perform an action before the user is able to save the file. Notice that there are parameters set in this event. Most events have parameters that indicate the object that triggered the event and added variables that can be used to cancel or save data. Just know that these variables must be left in the event subroutines. They are necessary to allow the event to run.
In the code above, we assume there is a version number stored in the A1 named range. The code checks the current version with the new version should the user change the value in the cell. In this example, we purposely set the versions to the same value, but you can create a global variable to store the current version as the user works with the spreadsheet.
If the versions are equal, then the program shows the message "Your version of Excel must be updated" to the user. It then cancels execution of the Save process.
Now, let's take a look at a worksheet level event. These events occur as the user works with each worksheet within the workbook. Take a look at the following event code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("B1").Interior.Color = vbRed
End Sub
We use the Change event in this example. The Change event occurs when the user changes any cell in the Excel spreadsheet. In this example, we change the color of the text in cell B1 to red when the user edits data. This is just a small example of what you can do to control data as the user changes the spreadsheet.
Finally, let's take a look at a chart level event. Chart events are called when the user interacts with a chart you've created in your spreadsheet. The following code uses the Select event trigger.
Private Sub Chart_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)
If ElementId = "My Chart" Then
MsgBox "The Chart Title Should Not Be Changed."
End If
End Sub
In the above code, we stop the user when they attempt to change the chart title. ElementId is set to the title you want to use, and if it does not equal My Chart after a change, then the message "The chart title should not be changed" is shown to the user.
Application Events
The final type of event is the application event. This event is a top-level event list that can be used in any part of your application. Application events cover any type of interaction from the user with the Excel application as a whole. For instance, when the user closes Excel, this is an application event.
When you use application events, you must create a global variable to reference it throughout your code. Rarely will you want to keep an application variable local to a subroutine.
When you use an application event, add the following code to the top of your module.
Private WithEvents App As Application
With the global variable set, you can now create a subroutine that uses it. For instance, it's common to reference the main application when opening a workbook. This allows you to work with the application properties and methods during the time the user has the workbook open. Let's take a look at the code.
Private Sub Workbook_Open()
Set App = Application
End Sub
In the above code, the App variable is set to the main application. Since the App variable is global, you can use it throughout the entire module.
When working with events, just remember that too many messages and restrictions can frustrate users. However, used correctly these event triggers help you automate many activities that happen when the user performs an action. You'll need to look up event names online to find out exactly when they trigger, but most of them are named well so that you can identify their function.
