Author Archive | Chris Page

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

Windows 10 Default Browser Problems

Working on a new tablet with Windows 10 installed, I recently discovered that I could not set the default web browser to Firefox – my browser of choice. No matter what I did it seemed to only accept Microsoft Edge. In fact when I went to Settings, System, Default Apps all I saw was a plus icon and the choose a default prompt. Tapping the icon gave me a choice of browsers but selecting anything other than Edge failed.

So I decided to stay old school and went via Control Panel. In the Category View you can tap on Programs and then tap on Default Programs.  In the Small or Large Icons view, tap the Default Icons option. Either way you finally tap on the Set your default programs option that is eventually displayed.  Tap the browser of your choice and tap on the Set this program as default option on the right. It worked for me!

Create a memory-efficient Data Model using Excel 2013 and the PowerPivot add-in

In Excel 2013, you can create data models containing millions of rows, and then perform powerful data analysis against these models. Data models can be created with or without the PowerPivot add-in to support any number of Pivot Tables, Pivot Charts, and Power View visualizations in the same workbook.

The data models can contain a huge amount of data that can use up computer memory which in turn could affect other programs that use the same system resources. If you use any Office or Excel online cloud applications, files are limited to 10MB which can be reached very quickly.

There is a good article here to read at on Microsoft’s Office Support website that will explain what you can do in detail.

You could also download and run Microsoft’s Workbook Size Optimizer. It analyses your Excel workbook and if possible, compresses it further.

How to place text in Word after the Endnotes

There is a problem when using bibliographies (or indexes or appendices) and endnotes together in Word. The problem arises when you want to place the text at the end of the document, after the endnotes. Word interprets end of document very strictly, and unfortunately, your endnotes appear after everything else.

Thankfully, using a little trickery we can get our bibliography, or appendix, or index, or whatever text is necessary to appear after the endnotes. The secret is to use sections. Briefly, you need to insert a section break at the end of the document. It’s possible that you are using multiple sections in your document for other reasons. So for each section in the document you need to change the Endnotes settings to display at the end of the section rather than the end of the document.  After this is done stop endnotes appearing in every section. You do this by suppressing the endnotes for all sections, except the one where the endnotes are to appear. As soon as you suppress endnotes in a section, the ones for that section appear in the following section. So you suppress them for all sections apart from the one where you want them to appear. Finally you place the bibliography, or appendix, or index, or whatever text is necessary, after the final section break.

The detailed instructions are:

  1. EndnoteDialogInsert a section break at the end of the document.
  2. In the References tab of the Ribbon, in the Footnotes group, click on the Dialog Box Launcher. The Footnote and Endnote dialog here is displayed.
  3. Select the Endnotes location option, then click on the drop-down selector to the right and choose End of Section.
  4. Click on Apply.
  5. Now position in each section where you do not want the endnotes displayed. You need to suppress the endnotes for that section. To do this:
    1. In the Page Layout tab of the Ribbon, in the Page Setup group, click on the Dialog Box Launcher. The Page Setup dialog is displayed.
    2. PageLayoutClick on the Layout tab, and click on the Suppress endnotes check box to place a tick mark in it.
    3. Click on OK.
    4. Repeat Step 5 for every section where you do not want endnotes displayed.
  6. Finally, move to the end of the document (using CTRL + End) and place the bibliography, or appendix, or index, or whatever text is necessary, after the final section break.

Welsh Characters in Word

I was asked during a training course this week how to easily insert the Welsh characters of ŷ and Ŷ.  Here are some ways to do it, and of course, the first two methods would work for any required character not on the usual keyboard:

Use the Insert Symbol Dialog.

  1. Using Insert tab from the Ribbon, then in the Symbols groups click on Symbol.
  2. Select the Latin Extended-A subset, and click down about 1 page in the dialog using the vertical scroll bar to find the symbols.
  3. Double click on the relevant symbol and then close the dialog.

Use the Pre-Assigned Shortcut Keys

You could use the shortcuts keys that let you work with any 4-number Unicode. In your document, type 0177 on your keyboard then press ALT + X to insert ŷ, or type 0176 then press ALT + X to insert Ŷ.

Create Your Own Shortcut Keys

You could assign your own shortcut keys. Highlight the symbol in the grid, then click on the Shortcut Key… button in the Symbol dialog to create a new shortcut key assignment. Maybe use CTRL + Y for the lower case ŷ and CTRL + SHIFT + Y for the upper case Ŷ.

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

Printing Excel Cell Comments

In Excel, how do you print cell comments that you have made on a worksheet. Follow these instructions:

Activate the worksheet that contains the comments that you want to print.
1.   If you want to print the comments in place on the worksheet, display them by doing one of the following on the Review tab, in the Comments group:

  • To display an individual comment, either click the cell that contains the comment, and then click on the Show/Hide Comment button, or right-click on the cell and then select Show/Hide Comments on the shortcut menu.
  • To display all comments, click on the Show All Comments button. You might need to move and resize any overlapping comments.

2.   On the Page Layouttab, in the Page Setup group, click on the Page Setup dialog box launcher .
3.   Click on the Sheet tab.
4.   Click on the Comments drop-down selector and select either As displayed on sheet or At end of sheet as required.
5.   Click on Print (you can always use Print Preview first to check everything looks OK).

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