Tag Archives | vba

Excel Cell, Row and Column Reference Secrets in VBA Code

Code VBA to Refer to Excel Cells, Rows and Columns Using Numbers

My favourite way of referring to ranges of Excel cells in VBA code is to use the Cells property and numbers. For example, the following code refers to cell C4 in the active sheet:

Cells(4, 3)

 

As this does not require any column letters it is very easy to substitute variable names in place of any numerical values, which means I can then use loops to cycle through different cells that are adjacent to each other (a lot of VBA in Excel is repetitive looping and processing or updating cells). So I could have two variables – one for row and one for column position and substitute something like this:

Cells(lngRow, intColumn)

 

Rows Only

If I want to refer to all the cells in a specific row, then I can use this:

Rows(4)

 

That code would refer to every cell in row 4.  Again I could substitute a variable like this:

Rows(lngRow)

 

Columns Only

If I want to refer to all the cells in a specific column, then I can use this:

Columns(3)

 

That code would refer to every cell in column C.  Again I could substitute a variable like this:

Columns(intColumn)

 

Interestingly, because columns are referred to with letters in the Excel User Interface I can use that in my VBA. So another way to refer to column C in my VBA could be:

Columns("C")

 

However, in that code it would be harder to substitute a numerical variable where I could loop through multiple columns. On the other hand to refer to more than one adjacent column I could use the letters. So to refer to columns C, D and E, I could use:

Columns("C:E")

 

Again, in that code would be impossible to substitute a numerical variable. There is a way to achieve this though using numerical variables. The secret is to use a range object and then supply the column numbers as the arguments. So the code below is the equivalent of using Columns(“C:E”):

Range(Columns(3), Columns(5))

 

With the is code I can now substitute numerical variables. So I could use:

Range(Columns(intFirstColumn), Columns(intLastColumn))

 

All of the code samples above refer to actions within the ActiveSheet.  If you need to refer to a specific sheet, then every object in the code needs to be fully qualified. So in order for the code immediately above to be used to refer to a sheet named Expenses I would need to use:

Worksheets("Expenses").Range(Worksheets("Expenses").Columns(intFirstColumn), _
Worksheets("Expenses").Columns(intLastColumn))

 

Of course this I could simplify the fully qualified code using a With..End With statement, so the example below shows me changing the value of every cell in columns C to E to the text “Chris”:

intFirstColumn = 3
intLastColumn = 5
With Worksheets("Expenses")
  .Range(.Columns(intFirstColumn), .Columns(intLastColumn)).Value = "Chris"
End With

Error handling in VBA

Error handling refers to the programming practice of anticipating and coding for error conditions that may arise when your program runs. In programming VBA, errors generally come in four flavours:

  • Syntax errors occur from typographical errors, missing punctuation, or improper use of a language element;
  • Compiler errors such as undeclared variables that prevent your code from compiling;
  • Logic errors, which are more commonly referred to as bugs, which occur when code executes without causing an error, but does not produce the results intended;
  • Run time errors that occur when VBA cannot correctly execute a program statement. Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero.

I’ll only look at run time errors and the example code in this article will use the Error 11 division by zero error to deliberately raise an error in the examples.

Your application should make as many checks as possible during initialization to ensure that run time errors do not occur later. In Excel, this includes ensuring that required workbooks and worksheets are present and that required names are defined. The more checking you do before the real work of your application begins, the more stable your application will be. It is far better to detect potential error situations when your application starts up before data is change than to wait until later to encounter an error situation.

If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. While this may be acceptable, even desirable, in a development environment, it is not acceptable to the end user in a production environment. Furthermore, if you have any module level or public variables then the values held in those variables will be lost.

The goal of well-designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. This also ensures that if you have any module level or public variables then the values held in those variables will be retained. Your goal should be to prevent unhandled errors from arising.

Note: Throughout this article, the term procedure should be taken to mean a Sub, Function, or Property procedure, and the term exit statement should be taken to mean Exit Sub, Exit Function, or Exit Property. The term end statement should be taken to mean End Sub , End Function, End Property, or just End.

The On Error Statement

The heart of error handling in VBA is the On Error statement. This statement instructs VBA what to do when a run time error is encountered. The On Error statement takes three forms.

On Error GoTo 0
On Error Resume Next
On Error GoTo <label>:

 

The first form, On Error Goto 0, is the default mode in VBA. This indicates that when a run time error occurs VBA should display its standard run time error message box, allowing you to enter the code in debug mode or to terminate the VBA program. When On Error Goto 0 is in effect, it is the same as having no enabled error handler. Any error will cause VBA to display its standard error message box.

The second form, On Error Resume Next , is the most commonly used and misused form. It instructs to VBA to essentially ignore the error and resume execution on the next line of code. It is very important to remember that On Error Resume Next does not in any way “fix” the error. It simply instructs VBA to continue as if no error occurred. However, the error may have side effects, such as uninitialized variables or objects set to Nothing.

The third form On Error of is On Error Goto <label>: which tells VBA to transfer execution to the line following the specified line label. Whenever an error occurs, code execution immediately goes to the line following the line label. None of the code between the error and the label is executed, including any loop control statements.

 On Error GoTo ErrHandler:
 N = 1 / 0 ' cause an error
 '
 ' more code
 '
 Exit Sub
ErrHandler:
 ' error handling code
 Resume Next
End Sub

 

Enabled and Active Error Handlers

An error handler is said to be enabled when an On Error statement is executed. Only one error handler is enabled at any given time, and VBA will behave according to the enabled error handler. An active error handler is the code that executes when an error occurs and execution is transferred to another location via an On Error Goto <label>: statement.

Error Handling Blocks and On Error Goto

An error handling block, also called an error handler, is a section of code to which execution is transferred via an On Error Goto <label>: statement. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. You can’t use the On Error Goto <label>: statement to merely skip over lines. For example, the following code will not work properly:

 On Error GoTo Err1:
 Debug.Print 1 / 0
 ' more code
Err1:
 On Error GoTo Err2:
 Debug.Print 1 / 0
 ' more code
Err2:

 

When the first error is raised, execution transfers to the line following Err1: label. The error handler is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement.

The Resume Statement

The Resume statement instructs VBA to resume execution at a specified point in the code. You can use Resume only in an error handling block; any other use will cause an error. Moreover, Resume is the only way, aside from exiting the procedure, to get out of an error handling block as it safely clears the Err object ready for the next error. Do not use the Goto statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers and the Err object does not get reset.

The Resume statement takes three syntactic form:

Resume
Resume Next
Resume <label>

 

Used alone, Resume causes execution to resume at the line of code that caused the error. In this case you must ensure that your error handling block fixed the problem that caused the initial error. Otherwise, your code will enter an endless loop, jumping between the line of code that caused the error and the error handling block. The following code attempts to activate a worksheet that does not exist. This causes an error (9 – Subscript Out Of Range), and the code jumps to the error handling block which creates the sheet, correcting the problem, and resumes execution at the line of code that caused the error.

 On Error GoTo ErrHandler:
 Worksheets("NewSheet").Activate
 Exit Sub
ErrHandler:
 If Err.Number = 9 Then
 ' sheet does not exist, so create it
 Worksheets.Add.Name = "NewSheet"
 ' go back to the line of code that caused the problem
 Resume
 End If

 

The second form of Resume is Resume Next . This causes code execution to resume at the line immediately following the line which caused the error. The following code causes an error (11 – Division By Zero) when attempting to set the value of N. The error handling block assigns 1 to the variable N, and then causes execution to resume at the statement after the statement that caused the error.

 On Error GoTo ErrHandler:
 N = 1 / 0
 Debug.Print N
 Exit Sub
 ErrHandler:
 N = 1
 ' go back to the line following the error
 Resume Next

 

The third form of Resume is Resume <label>: . This causes code execution to resume at a line label. This allows you to skip a section of code if an error occurs. For example,

  On Error GoTo ErrHandler:
  N = 1 / 0
  '
  ' code that is skipped if an error occurs
  '
 Label1:
  '
  ' more code to execute
  '
  Exit Sub
 ErrHandler:
  ' go back to the line at Label1:
  Resume Label1:

All forms of the Resume clear or reset the Err object.

Error Handling With Multiple Procedures

Every procedure need not have an error code. When an error occurs, VBA uses the last On Error statement to direct code execution. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section. However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code. For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B.

Be Careful

It is tempting to deal with errors by placing an On Error Resume Next statement at the top of the procedure in order to get the code to run without raising an error. This is very bad coding practice. Remember that using On Error Resume Next does not fix errors. It merely ignores them.

Problems with ActiveX Controls Not Working

Due to a recent Microsoft Update, some users have found that any embedded ActiveX controls on Excel worksheets or Word documents no longer work. Not only that, they try to add new ones and they get an error message informing them “Cannot insert object”. It appears that a good fix is to simply delete the file MSForms.exd file from any Temp subfolder in the user’s profile. For instance:

C:\Users\[user.name]\AppData\Local\Temp\Excel8.0\MSForms.exd

C:\Users\[user.name]\AppData\Local\Temp\VBE\MSForms.exd

C:\Users\[user.name]\AppData\Local\Temp\Word8.0\MSForms.exd

Make sure that you are not running any Office application (Excel, Word…) before you do this.

VBA to convert a mapped drive letter to UNC path

I was asked today about using VBA to convert a mapped network drive letter to a UNC path. Say for example we have drive Z: mapped to \\Server\Folders\Accounts. How can we use VBA to return the UNC data when we only know the drive letter? The answer is to use the code shown below. This does require us to set a reference in the VB Editor using Tools, References to the Microsoft Scripting Runtime.

 Function GetUNC(strMappedDrive As String) As String
    Dim objFso As FileSystemObject
    Set objFso = New FileSystemObject
    Dim strDrive As String
    Dim strShare As String
    'Separated the mapped letter from
    'any following sub-folders
    strDrive = objFso.GetDriveName(strMappedDrive)
    'find the UNC share name from the mapped letter
    strShare = objFso.Drives(strDrive).ShareName
    'The Replace function allows for sub-folders
    'of the mapped drive
    GetUNC = Replace(strMappedDrive, strDrive, strShare)
    Set objFso = Nothing 'Destroy the object
End Function

So, using the example mapping stated earlier, if I call GetUNC(“Z:”) the function returns
\\Server\Folders\Accounts and if I call GetUNC(“Z:\OldData”) then it will return
\\Server\Folders\Accounts\OldData

Using the R1C1 Reference Style in Excel

There is an option within Excel to use the R1C1 reference style. This is not normally done as most users are comfortable with using the usual Column Letter and Row Number format for references in formulas. However, this can be changed by going to Excel options, selecting the Formulas category and then ticking the box for the R1C1 Reference Style. When you have done this you will notice back in the Excel workbook that the column letter header row has changed to numbers to reflect the changed referencing style.

The diagram here show how you would enter a reference in cell C3 (the grey cell) to point to different cells around C3. For example to point to to one row above the current cell in the same column would require a reference of =R[-1]C whereas to point to the cell one column to the right in the same row would require =RC[1] as a formula.

Releative

So a positive number in square brackets after R means a number of rows below, a positive number in square brackets after C means a number of columns to the right. A negative number in square brackets after R means a number of rows above, a negative number in square brackets after R means a number of columns to the left.

If you want an absolute reference (the equivalent of the dollar signs before the Column/Row reference) then remove the square brackets and use a positive number. For example, to refer to the second cell down from the top row in the current column we use =R2C as a reference. If I switched back to normal referencing and was in a cell in column E this would show up in the formula bar as =E$2.

Using R1C1 Reference Style in VBA

When I am teaching Visual Basic for Applications (VBA) programming I encourage my students to use this style of referencing in creating formulas as it solves problems when trying to programmatically enter a formula and you don’t want determine which row the cell is in . For example, if my VBA code had just imported some data and I wanted to now place a simple SUM formula at the bottom of the data, what do I enter? Well, let us assume I am in the first blank row below the data and I am not converting this range of cells to a table.  Also the first row of data is a column heading/title so I only want to add up from row 2 downwards. The formula would be:

=SUM(R2C:R[-1]C)

Using VBA, let’s assume I am currently positioned in the cell where I want to place the formula, my code would be:

Activecell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

Customising the Office Ribbon

Those of you who use Office 2010 and 2013 are probably quite happy with the Ribbon customisation options offered in those versions. Office 2007 had the Ribbon but no customisation at all.

What if you want to add sub-menus? This is not possible in the 2010 and 2013 user-interfaces and according to Microsoft you really need to program in Visual Studio .NET to get these extra customisations; however, I found an article written way back in 2009 that I want to bring to your attention that still works and gives some really cool customisations using VBA and a little XML. Now don’t panic at the thought of the XML as the article explains it all.

Take a look at the step-by-step guide to customising the Office Ribbon

How to run Excel VBA at specific times

I have been asked a few times now if it is possible to schedule VBA code to run at either regular intervals or at specific times. Using a little VBA code you can use the Application.OnTime method to run a procedure automatically.

The OnTime method requires two arguments to run. The first argument is a specific date and time at which the procedure should run and the second argument is the name of the procedure to run. It is worthwhile knowing that you must provide the date and time to run the procedure rather than an offset from the current time.; however, as you will see this can be worked around.

The following code will run the ProcessData procedure at 12:45

Sub RunTimedActivity()
    'Schedule the ProcessData procedure to run at 12:45
    Application.OnTime EarliestTime:="12:45:00", _
	Procedure:="ProcessData"
End Sub

 

If you need to cancel an OnTime method, you must provide the exact time that the event was scheduled to take place. The following code cancels the previous timed activity.

Sub RunTimedActivity()
    'Stop the ProcessData procedure to running at 12:45
    Application.OnTime EarliestTime:="12:45:00", _
	Procedure:="ProcessData", _
        Schedule:=False
End Sub

 

If you don’t know the precise time, the only way to tell Excel to cancel the next OnTime event or to cancel all the pending OnTime events is to close down the whole application. Therefore, the best advice I can give is to either store the time at which the procedure is to run in a Public variable and use that variable’s value in calls to OnTime, or store the time in a cell that is not viewed normally. Also note that if the workbook containing the procedure to be executed by OnTime is closed before the procedure is run, Excel will open the workbook again before running the procedure and will leave the workbook open after the procedure is complete.

Scheduling a Procedure to Run Regularly

In theory, the OnTime method needs a precise date/time to run. So if you want to schedule a procedure to run on a regular basis then you need a recursive function call that calls itself again in the future. If you need to stop this regular activity make sure you record the next time somewhere. Either use an out of the way cell or a public variable. In the example below I have used cell IV1. The example runs a procedure every 2 minutes 30 seconds.

Sub RunTimedActivity()
    Dim datNextTime As Date
    Dim intHrs As Integer
    Dim intMins As Integer
    Dim intSecs As Integer
    Dim strThisProcedure As String

    'variables to hold elapsed time
    intHrs = 0
    intMins = 2
    intSecs = 30

    'If the procedure named below is not
    'in a standard module then include
    'module name before the procedure name
    'such as ThisWorkbook.RunTimedActivity
    strThisProcedure = "RunTimedActivity"

    'Determine next time to run
    NextTime = Time + TimeSerial(intHrs, intMins, intSecs)
    Range("IV1").Value = datNextTime

    'Schedule this procedure to run
    Application.OnTime EarliestTime:=datNextTime, _
	Procedure:=strThisProcedure

    'Call the procedure you want to run
    ProcessData
End Sub

 

The procedure to cancel the above activity would be:

Sub CancelActivity()
    Dim datNextTime As Date
    Dim strThisProcedure As String

    'If the procedure named below is not
    'in a standard module then include
    'module name before the procedure name
    'such as ThisWorkbook.RunTimedActivity
    strThisProcedure = "RunTimedActivity"

    'Determine next time to run
    datNextTime = Range("IV1").Value 

    'Schedule this procedure to run
    Application.OnTime EarliestTime:=datNextTime, _
         Procedure:=strThisProcedure, Schedule:=False
End Sub

 

One final important note is that Excel will only run OnTime method when it is in Ready, Copy, Cut or Find mode. Any OnTime method cannot run at its scheduled time if you are in the process of editing a cell at that time. An alternative in this situation is to specify a window of time for the OnTime method to run. The following code will rum at some point between 12:45 and 12:50. If Excel remains in Edit mode for the whole of that period the task will be skipped.

Sub RunTimedActivity()
    'Schedule the ProcessData procedure to run between 12:45 and 12:50
    Application.OnTime EarliestTime:="12:45:00", _
       Procedure:="ProcessData", LatestTime:="12:50:00"
End Sub

Initialising Arrays in VBA

I was asked today if it was possible to initialise an array in VBA using a single line statement. The answer is actually no not really although there is a trick that can!

In terms of static arrays, you can’t. The only way is to either set each element separately if each element is a specific value, or if each element initially holds the same value then use a loop to assign that value to each element in turn.

There is a trick however by using a variant variable as the array. As we know a variant holds any type of data. So the following code would create a variant that holds an array of 5 values starting at 5 and increasing by 5. The final line would output a value of 20 in the MsgBox as the array will always be zero-based.

Dim varMyArray As Variant
varMyArray = Array(5,10,15,20,25)
Msgbox varMyArray(3)

 

This code doesn’t fix the array to 6 elements as the Variant variable varMyArray could be changed in size by more code. The following code displays 20 in the message and then in the next MsgBox displays 9.

Dim varMyArray As Variant
varMyArray = Array(5,10,15,20,25)
Msgbox varMyArray(3)
varMyArray = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)    
MsgBox varMyArray(9)

 

And because we are working with a Variant data type, it can hold anything including mixed data types. So the following code works too, with the MsgBox displaying Page.

Dim varMyArray As Variant
varMyArray = Array(5,"Chris",15,"Page",25)
Msgbox varMyArray(3)

Using VBA with Speech

If you program using VBA in Excel there might come a time when you would like the application to speak to the user. The good news is you can use the Application.Speech.Speak method to convert a string to Speech. This works for all versions from 2003 and later.

In the following example, Microsoft Excel plays back “Hello Bob”. I am assuming speech features have been installed on the host system.

Sub SayHello()
    Application.Speech.Speak "Hello Bob"
End Sub

 

There is a second argument of the Speak method that defines either synchronous or asynchronous. True will cause the Text to be spoken asynchronously (the method will not wait of the Text to be spoken). False will cause the Text to be spoken synchronously (the method waits for the Text to be spoken before continuing). The default is False.

The following code illustrated how this works:

Sub DemoSpeech()
    Application.Speech.Speak "This is spoken then code runs"
    MsgBox "This is displayed after the previous text is spoken"
    Application.Speech.Speak "Spoken and the next code runs", True
    MsgBox "This is displayed while the previous text is spoken"
End Sub

 

Another feature that is useful is the ability of Excel to speak the contents of the cell after the user has finished entering data (on pressing Enter).

Sub EnableSpeech()
   Application.Speech.SpeakCellOnEnter = True
End Sub

 

To turn off the Speak On Enter set the value to False.

 

Excel Data Validation and Case-Sensitivity

I was working with a client recently on some VBA programming and the client was trying to limit data entries in a column of cells to one of four specific values. Now those of you who are familiar with Excel will immediately shout out “set up data validation”, and you would be correct. However, not entirely, as most people would create the set of entries in a range of four cells somewhere and set the Excel data validation to that range of cells.

While using data validation this way would work after a fashion it would not enforce the case of the entries. Those of you staying in Excel would probably not worry. I was working using VBA (Visual Basic for Applications) and the VBA code was case sensitive. Not only that but the client wanted to force the text entered by users to have the first letter of each entered word to be upper case.

So how do you force data validation to enforce the case of entered values. The answer is to enter the values literally in the Source box of the Data Validation dialog, separating each value with a comma, and not set the source to be a range reference. So if I was restricting my entries to the three major cities of England then I would type in the Source box:

London, Birmingham, Manchester

In this way London would be acceptable but london or lonDON would be rejected.

Of course, if you are working with a dynamic list then you have to stick with range references and use some other method (as I would in my VBA code) to enforce the case of entries, but the above is a simple solution for short, static lists.