Tag Archives: VBA

Copy Symbol Image

Add Macro Button to the Toolbar in Excel 2011

You can add an icon to the toolbar in Excel 2011 for your Personal Workbook Macro. In an earlier post I created a short macro to imitate the Control+Home keyboard shortcut in Excel for Windows. You can add an icon to the toolbar to run that, or any other macro with a few quick steps.

  • Right click on the toolbar and select Customize Toolbars and Menus… then
  • Click the Commands tab, then
  • Scroll down and select Macros from the Categories pane, and
  • Drag the Custom Button with a smiley face to the toolbar, then
  • Click OK to get rid of the dialog box.

Next you:

  • Right click on the smiley face, select Assign Macro… and
  • Pick the macro you created (in your personal macro workbook), then
  • Click OK.
  • Change the Smiley Face Image

    If you don’t like the smiley face icon staring back at you want to change it (like I did) that’s easily done as well.

  • Right click on the Custom Button icon and select Properties… then
  • Type a name in the Name: box, then
  • Click the drop-down arrow beside the smiley face and select from the icon list, then
  • Click OK.
  • Use a Custom Image for the Toolbar Button

    The icon list is rather short and pathetic, but you need not restrict your choices as Excel allows you to copy and paste an image into the button face. Here is how I changed my smiley face image.

  • Choose Insert > Symbol
  • Click Shapes (at the top of the dialog box)
  • Right click on your favorite shape and click Copy, then

  • Copy Symbol Image

  • Close the Media dialog box, and
  • Right click on the smiley face toolbar icon, then
  • Click Properties…
  • Click the drop-down beside the image, and
  • Click the option Paste Button Image, then
  • Click OK.
  • Move Your New Macro Button

    In case you didn’t get your button located in the right place on your toolbar when we first started this exercise, the button is easily moved.

  • Right click on the Toolbar and select Customize Toolbars and Menus…
  • Drag your icon to a position of your choice, then
  • Click OK.
  • Personal Macro Workbook GoHome Code

    Control + Home in Excel for Mac

    One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years.

    I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go straight to the answer, let me tell you how I got there.

    My Journey

    I knew that VBA was going to enter into the equation, so I started to record a macro on a Windows PC while using the Control+Home keyboard shortcut. What I found out is that Excel does not record that keyboard shortcut. Nothing, nada, zip.

    I then noticed that Control+Home acted differently, depending if the sheet panes were frozen or not. When sheet panes are not frozen, the Control+Home shortcut took me to cell A1. When the sheet panes are frozen, then the upper left corner cell of the window was selected.

    To make a long story short, I found out that the active window’s scroll row and column were being selected when the CTRL+Home shortcut is executed in Excel for Windows.

    The Control+Home Macro

    I also knew that if a Chart sheet were selected the macro would fail, so I crafted the following macro to mimic the CTRL+Home shortcut on a Mac. (Works on a Windows PC too.)


    Sub GoHome()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Make sure the active sheet is a worksheet,
    ' then locate the active window's scroll row and
    ' column, and activate that cell.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim lngRow As Long
    Dim lngCol As Long

    If ActiveSheet.Type = xlWorksheet Then
    lngRow = ActiveWindow.ScrollRow
    lngCol = ActiveWindow.ScrollColumn
    Cells(lngRow, lngCol).Activate
    End If
    End Sub

    The next thing I did was create a shortcut for the macro, but realized that the macro would only work with the current workbook. Rats!

    I wanted the macro work automatically on every Excel file so I chose to store this macro in the Personal Macro Workbook, which solved the problem.

    However the shortcut key combination I assigned did not work in Excel for Mac. There was a conflict. It seems that COMMAND+OPTION+H is a reserved keyboard shortcut for the Mac. I found out this shortcut hides all windows except the one that is active.

    How I Created the Control+Home Keyboard Shortcut on my Mac

    Here are the steps I took, with a few false starts along the way.

  • Open Excel 2011 and choose Tools > Macro > Record New Macro…
  • Type a name for the macro. I used GoHome.
  • Enter a Shortcut key. (I used h, which didn’t work so I changed to g as you’ll see below.)
  • Where you see Store macro in: click the drop-down and select Personal Macro Workbook.
  • Click OK.
  • Record Macro Dialog Box

    I got a warning that the keyboard shortcut Option+Command+h was reserved.

    Reserved Shortcut

    So I chose g instead and clicked OK.

  • Next choose Tools > Macros > Stop Recording. This will effectively end the macro recording without recording anything.
  • Choose Tools > Macro > Macros…
  • Macro Dialog Box

  • Click the Step Into button on the Macro dialog box, which will take you directly inside the macro in the VBA Editor. (Note: While this takes you directly to the macro, it also starts the macro running inside the VBA Editor.)
  • Reset the Macro

  • Click the square Reset button to stop the macro program execution. (Note: If you know how to navigate the VBA editor, you can skip this last step and choose Tools > Macro > Visual Basic Editor and then locate the macro.
  • Select the entire GoHome code from this article. Or click this link and copy from a new window.
  • Copy the text,
  • Switch back to the Excel VBA editor,
  • Select the entire GoHome subroutine, and
  • Paste the code.
  • Personal Macro Workbook GoHome Code

  • Close the VBA editor by choosing Excel > Close and Return to Microsoft Excel.
  • Close VBA Editor

    Now here is the important part so pay attention. You have to save changes to the Personal Macro Workbook. You will be asked to do this when you Quit Excel.

  • Choose Excel > Quit Excel and the following dialog box will appear.
  • Save Personal Macro Workbook Changes

  • Click the Save button when asked, “Do you want to save the changes you made to the Personal Macro Workbook?”
  • Run the GoHome Macro

    Now lets check it out. Open Excel 2011 and select any cell that is not A1, then use the shortcut (mine was Command+Option+g) and watch the active cell change to cell A1.

    If your shortcut doesn’t work you can set it now. Choose Tools > Macro > Macros… and select the GoHome macro, then click Options. Type in a shortcut key and click OK.

    Now choose any cell in the top left quadrant of the current window, like C5. Choose Window > Freeze Panes. Next select any cell except C5 and run the shortcut combination for the macro, and watch the curser jump to cell C5.

    The Personal Macro Workbook will load each time you open Microsoft Excel so it’s always in the background, and you don’t get that annoying “enable macro” pop-up screen.

    How to Update a List or Range without OFFSET

    I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome.

    Worksheet cells that use Data Validation for a drop-down list can simplify the input process, or be used to limit the available choices. But the list needs be expandable. Here are two primary ways to keep your data validation list automatically updated, without having to resort to using the OFFSET function.

    Update Your List Range with VBA

    Put your data validation drop-down cell on one worksheet and the reference list range on another worksheet. Example: Sheet1 contains a cell with data validation. Sheet2 contains a data range (the list) that is given a defined name of myList. Add some VBA code in the Sheet2 Deactivate routine to update the named range.


    Private Sub Worksheet_Deactivate()
    Dim rng As Range
    Set rng = Sheet2.Range("myList").CurrentRegion
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
    rng.Name = "myList"
    End Sub

    This is an event-based programming technique, which I commonly use with Excel 2003.

    Data Validation List Lookup 1 Sheet

    Use Some Table INDEX Magic

    This is a variation of the same thing, but no VBA programming is warranted. Instead, use a Table for your reference list data. Then create a defined name with the INDEX Function, and use that name for the data validation list.

    Tables automatically update their ranges when expanded and the INDEX function will too. Example: Create the defined name myListFormula and use =INDEX(Table1,0,1) as the formula. Then when setting the data validation list, use =myListFormula as the list reference.

    Data Validation List Lookup 2 Sheet

    Example Worksheet

    I’ve put together a workbook with the two examples listed above. The first technique, with VBA, uses two worksheets: Lookup 1 and Data 1. The Data 1 worksheet has the VBA code, which updates the named range when deactivated. You can add or subtract to this list and the data validation list on the Lookup 1 sheet will automatically be updated.

    Both Lookup sheets have data validation in cell A2, which is a list of names. I’ve added another column for the city that uses a formula to get the right value from the list.

    Lookup 1 Sheet

    Lookup 1 Sheet

    Data 2 with VBA Code

    Data 2 Sheet with VBA Code

    The second example uses Lookup 2 and Data 2 worksheets. The Table is on the Data 2 worksheet. When you add or subtract data from this Table and the defined name myListFormula will automatically update the data validation list on the Lookup 2 worksheet. Be sure to look at the Define Name dialog box (on the Mac) or the Name Manager (Windows) to see the INDEX formula.

    Define Name Dialog Box

    Download the file: Data_Validation_List_Update.xlsm

    Since there is no OFFSET function, updating at random intervals, in either of these examples, I can rest easy. That’s one less thing to slow down your worksheet.

    Select Excel’s Used Range on a Mac

    I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button.

    The used range on a worksheet starts with cell A1 and ends with the last used cell in the worksheet. This “last cell” is not always apparent, but easily found. Just use the keyboard shortcut CONTROL + G to bring up the Go To dialog box.

    Go To dialog box
    Click Special… which will bring up the Go To Special dialog box.
    Go To Special dialog box
    Select Last cell and click OK.

    The last cell may sometimes surprise, because Excel considers cell formatting as being “used” so you may see blank cells that are way outside your data range. Tip: Sometimes you can delete the seemingly extra rows and columns outside your data range and it will reduce the file size.

    Select the Used Range by Navigating Back Home

    Once you find the last cell, you can then hold the Shift key down and click cell A1 to select the entire range. Of course if you can’t see cell A1 in the current window there is no Home button on the Mac to help you out. (Major bummer)

    The next best thing is to hold the COMMAND + Shift keys down while you tap the left arrow and up arrow keys until you reach cell A1. This can be simple, or time-consuming depending upon size and shape of your worksheet.

    Selecting the Used Range

    To select the entire used range with VBA is a simple matter. Choose Tools > Macro > Visual Basic Editor, then choose View > Immediate Window, and type activesheet.usedrange.select inside the immediate window and hit enter.

    Create a Macro to Select the Used Range

    You can also create a macro to select the entire used range by opening the VBA Editor, inserting a Module, and entering the following code.


    Sub ActiveSheetUsedRange()
    ActiveSheet.UsedRange.Select
    End Sub

    Or you could get fancy with this code.


    Sub SelectUsedRange()
    Dim rng As Range
    Set rng = Range("A1").SpecialCells(xlCellTypeLastCell)
    Range(Cells(1, 1), rng).Select
    End Sub

    Add a Keyboard Shortcut for the Macro

    To make things simpler to run the macro, you can enter a shortcut. Just choose Tools > Macro to bring up a dialog box.

    Macro dialog box

    To add a shortcut, select a macro and click Options… which will bring up the Macro Options dialog box.

    Enter a shortcut key by clicking inside the Shortcut key box and pressing a key on the keyboard. I pressed the “u” key on the keyboard, and consequently the keyboard combination is shown as Option + Cmd + u, as you can see in the screen shot. (Depending on the key, you may also include COMMAND, Control, Shift into your shortcut.)

    Macro Options dialog box

    Enter a description if you wish, and then click OK. Select the Cancel button on the Macro dialog box to make it disappear.

    Now you can run the macro by simply using the keyboard shortcut Option + Cmd + u.

    Note: This macro will not work if a Chart sheet is selected.

    Used Range verses Actual Used Range

    The actual used range might be different than the used range. Meaning that some blank cells that are formatted might be included in the used range. Most likely you will only want to deal with a range that has some actual values. This would be the actual range.

    Please refer to the aforementioned blog post over at Contextures to see a couple of different examples of code that you can use to select the actual used range. These examples are short and use the VBA FIND function to get the job done.

    Analysis ToolPak AddIn

    Check the Analysis ToolPak Add-In in Excel 2003 [VBA Code]

    Analysis ToolPak AddInI recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error.

    The only way for me to avert an error with users who don’t have the Analysis TookPak installed in Excel 2003 is to use some VBA code that runs when the file is opened. I want to see if they are using a version of Excel older than 2007, check if they have the Analysis ToolPak installed, and then tell them, with a pop-up message, to install the Analysis ToolPak, so all the formulas will work correctly.

    Create a Routine to Check for the Analysis ToolPak

    Open the Visual Basic Editor (Alt+F11) then choose Insert > Module and enter the following VBA code.


    Sub CheckToolPak()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Checks to see if the Excel version comes before Excel 2007,
    ' if so then checks to see if the Analysis ToolPak is installed.
    ' If not, then notify the user and end the program.
    '
    ' This is required because of the NETWORKDAYS formula
    ' that is used in the charts.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If CInt(Application.Version) < 12 Then If AddIns("Analysis ToolPak").Installed <> True Then
    MsgBox "Please install the Analysis ToolPak." & vbCr & vbCr & _
    "Choose Tools > Add-Ins... " & vbCr & _
    "then check the box for Analyiss ToolPak, and click OK."
    End
    End If
    End If
    End Sub

    The code is somewhat self-explainatory except that Application.Version returns a text value, hence you see the CInt function that converts that text value to an integer so that we can tell if the Excel version is less than 12, which is the version number of Excel 2007.

    Create a Workbook Open Event

    This will do the trick, but needs a trigger to call the routine. So double-click ThisWorkbook in the Project Explorer of the VBA Editor, then select the drop-down at the top-left of the window – where you see (General) – and select Workbook. The Private Sub Workbook_Open() subroutine will appear with no code.

    Type in Call CheckToolPak and then save the file.


    Private Sub Workbook_Open()
    Call CheckToolPak
    End Sub

    Now when you open the file with Excel 2003, and don’t happen to have the Analysis TookPak installed, you get the following message.

    Analysis ToolPak PopUp

    If users have the Analysis ToolPak Add-In installed in Excel 2003 there is no message, nor if the user is using Excel 2007, 2010 or 2011.

    Select a Column of Non-Sequential Data

    Non-Sequential Data

    In one of my posts on using VBA to select a column of data, I received a comment asking if the macro could be modified to select data like the screen shot you see to the left.

    This data is non-sequential and consequently the CurrentRegion property won’t work. The way around this is to select the very last row in the same column, then shoot up (Ctrl + Up Arrow) to find the last data cell.

    Once you know the where the column heading and last data cell is in the current column, the range can then be selected.

    The following macro will select the column of data if you start with the active cell at the column heading.


    Sub SelectOneColumnData()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' This routine will select a non-continuous column of data
    ' when active cell is located in the column heading.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ac As Range
    Dim lRow As Long
    Dim lc As Range
    Dim col As Integer
    Dim cr As Range

    Set ac = ActiveCell
    col = ac.Column
    lRow = ActiveSheet.Rows.Count
    Set lc = Cells(lRow, col)

    ' Find the bottom of the range then re-set the last cell range
    Set lc = lc.End(xlUp)
    lRow = lc.Row

    ' Set the current range from the active cell to the last row
    ' in the column with data
    Set cr = ac.Offset(1, 0).Resize(lc.Row - ac.Row, 1)
    cr.Select
    End Sub

    Fill Down a Formula with VBA

    I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column of data.

    The situation is depicted below. Cell C2 is active, and has the formula =B2+A2. I want to copy it down to the rest of the column in this data range. However, cells B6 and B11 are empty, along with countless others below the visible table range. (Pretend this data table is huge.)

    Test Formula Fill Down BEFORE

    Here is some VBA code that will Fill Down the formula.


    Sub FillDownFormula()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Filldown a formula for in column of data.
    ' Assumes a data table with headings in the first row,
    ' the formula in the second row and is the active cell.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim rng As Range
    Dim rngData As Range
    Dim rngFormula As Range
    Dim rowData As Long
    Dim colData As Long

    ' Set the ranges
    Set rng = ActiveCell
    Set rngData = rng.CurrentRegion

    ' Set the row and column variables
    rowData = rngData.CurrentRegion.Rows.Count
    colData = rng.Column

    ' Set the formula range and fill down the formula
    Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
    rngFormula.FillDown
    End Sub

    The key is setting the formula range (rngFormula). I take the entire region of data (rngData) and offset by 1 row because I don’t want the header row, then I resize the rows in the range by subtracting 1 from the total number of rows because I now need one less row in the range.

    Next I offset the entire range by the row number of the active cell, but have to subtract one column because I offset from column 1, not column 0. And finally I resize the data range to 1 column, which gives me the single-column range I want with the formula in the top row.

    The routine does no error checking and is restricted to using the active cell that has a formula. But it does the trick given those limitations.

    International Short Date Formatting for the TEXT Function

    I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.

    Here’s what a US user sees:

      From: 6/6/2011 to 6/10/2011

    Here is the formula:

      =”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)

    The Min and Max dates are in column A on a worksheet named ExtractData.

    The problem is trying to automatically change the date format in the second argument of the TEXT Function – “m/d/yyyy” – which is a string argument. We can use VBA to accomplish this, but first a refresher on the TEXT function syntax.

    TEXT Function Syntax and Argument

    The Solution

    The Application.International Property solves this problem with the xlMDY argument, which is TRUE if the date order is month-day-year, and FALSE if the date order is day-month-year. This property is put into the Workbook_Open event and modifies a defined name constant that’s used for the second argument of the TEXT function.

    Here’s how it’s done.

    Create a Defined Name Constant for the Date Format

    I created a Defined Name Constant to store a Short Date format, and gave it the name sd_format.

    In Excel 2007 and 2010, choose Formulas > Define Name to bring up the New Name dialog box where you type in sd_format in the Name box, and type =”m/d/yyyy” in the Refers to box. Remember the equals sign.

    New Name dialog box

    In Excel 2003, 2008, and 2011 choose Insert > Name > Define to bring up the Define Name dialog box. Type sd_format in the Names in workbook box, then type =”m/d/yyyy” in the Refers to box. Be sure to use the equals sign.

    Substitute the Named Constant in the TEXT Function

    The sd_format defined name can now be substituted for “m/d/yyy” in the second argument of the TEXT function. Here’s the new formula:

      =”From: ” & TEXT(MIN(ExtractData!A:A),sd_format) & ” to ” & TEXT(MAX(ExtractData!A:A),sd_format)

    Since sd_format is already a text string, enclosed quotes are not needed.

    Create a Workbook Open Routine

    In the VBA Editor, I created a Workbook_Open subroutine, which looks at the computers international setting for the US, and if TRUE changes the sd_format value to “m/d/yyy”, and otherwise changes it to “d/m/yyyy” for the UK short date format.

    Private Sub Workbook_Open()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' This routing updates the regional date setting format for the
    ' defined name sd_format.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Application.International(xlMDY) = True Then
    ThisWorkbook.Names("sd_format").Value = "m/d/yyyy"
    Else
    ThisWorkbook.Names("sd_format").Value = "d/m/yyyy"
    End If
    End Sub

    How it All Works

    Each time the workbook is opened the Workbook_Open routine executes the IF-THEN-ELSE statement, which simply looks to see if the computers region setting is month-day-year, then sets the defined name sd_format to “m/d/yyyy” which is a US format. If the computer’s region setting for the long date format is NOT month-day-year (and presumably day-month-year) then sd_format is set to “d/m/yyyy” for the UK.

    Every TEXT function using sd_format for the second argument will then have the proper short date format for that computer.

    Note: Obviously if the region settings are changed on the computer while the file is open the file will have to be closed and reopened, but this would most likely be a rare occurrence.

    Select One Column of Data with VBA

    Let’s assume you aren’t using an Excel Table, List Object, or List for your data table and you want to select the data in only one column, which means the Header row is excluded. This can be simple if there’s data in every row of the column, but what if that’s not the case?

    Select Column Data in a List

    And assume for the moment that the example above extends down several hundred, or even thousand rows. I would get tired of manually selecting the data in a column by using the Ctrl+Shift+down arrow keyboard shortcut.

    So I’d probably write some VBA code to handle this pesky task and link it to a button on the worksheet. Below is the VBA code.

    Sub SelectColumnData()
    Dim ac As Range
    Dim cr As Range
    Dim col As Integer

    Set ac = ActiveCell
    Set cr = ac.CurrentRegion
    col = ac.Column - cr.Column

    If cr.Rows.Count > 1 Then
    cr.Offset(1, col).Resize(cr.Rows.Count - 1, 1).Select
    End If
    End Sub

    The active cell must be in the column you want to select when running this macro.

    Select Column Data

    To insert the button in Excel 2011, 2007 (Windows), I’m using a Form control by choosing Developer > Insert, selecting the Button icon, and drawing a button on the worksheet with the help of the Alt key to snap to cell borders. In Excel 2011 (Mac) choose Developer on the Ribbon then click the Button icon and draw a button on the worksheet. The alt/control key doesn’t work to snap to gridlines.

    Excel Form Buttons

    Then I rename the button and assign the macro.

    Assign Macro to Form Control

    I can also assign a shortcut to the Macro instead of using a button, but the only issue with that is remembering what the shortcut is when I need to use the macro.

    Speeding up VBA with the PrintCommunication Property in Excel 2010

    I have an Excel file that creates different reports from a data table using a macro, and before Excel 2010 was released the “time-hog” was running VBA code for the print settings. Tell me you’ve added something like the following code and had your execution time slow exponentially.
    With ActiveSheet.PageSetup
    .CenterHeader = "&""Verdana,Bold""&12&A"
    .CenterFooter = "&P of &N"
    .LeftMargin = Application.InchesToPoints(0.7)
    .RightMargin = Application.InchesToPoints(0.7)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
    .CenterHorizontally = True
    .CenterVertically = False
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = False
    End With

    Another factor is the default printer of your computer when the code runs. I usually set my default printer to CutePDF, which isn’t really a printer, but a program that writes (prints) to a PDF file. Using this default printer while running the PageSetup code chops mega-time off the execution.

    One trick I used was to run this code in the Workbook_BeforePrint module. That way, running my macro to create a new report doesn’t execute the PageSetup code and therefore executes much, much, much, much, much faster.

    The New PrintCommunication Property

    In Excel 2010 I happen to record a macro for print settings and discovered a new page setup property:

    Application.PrintCommunication

    The Application.PrintCommunication property is new in Excel 2010. It specifies whether communication with the printer is turned on. The values are Boolean and Read/Write.

    Using the PrintCommunication Property

    Set the Application.PrintCommunication equal to FALSE, then run the Page Setup code, then set it back to TRUE and your code will run exponentially faster. However, there are two problems with using the Application.PrintCommunication property and both deal with compatibility.

    Compatibility with Older Versions

    Older versions of Excel don’t recognize this new property so you have to code around this fact to ensure backward compatibility. I checked the Excel 2010 version number by running some code in the VBA Editor’s Immediate window.

    Excel Version code

    You can also check your Excel version by choosing File > Help and reading the screen.

    Since the Application.version property returns a String value, the Val function is required to make use of it. My code now reads like this to achieve backward compatibility:
    If Val(Application.Version) >= 14 Then Application.PrintCommunication = False
    With ActiveSheet.PageSetup
    ' (all the ugly page setup code goes here)
    End With
    If Val(Application.Version) >= 14 Then Application.PrintCommunication = False

    I checked to see how long it takes to run the VBA code to produce my report in three versions of Excel: 2010, 2007 and 2003.

    Excel 2010 – run1 = 0.918 sec, run2 = 0.867 sec
    Excel 2007 – run1 = 5.965 sec, run2 = 6.273 sec
    Excel 2003 – run1 = 6.066 sec, run2 = 6.125 sec

    My default printer at the time was a HP D110 and it took over 5 seconds longer to run the PageSetup code in Excel 2007 and 2003.

    Compatibility with Excel 2011 for Mac

    By now I’m happy with Excel for adding this new property, but reality sets in and I realize that Excel 2011 can run code too. I check to see what version number Excel 2011 for Mac happens to be, and it too is version 14.0.

    Excel 2011 version

    So while I’m in the immediate window I check for the Application.PrintCommunication property and find out it doesn’t exist.

    No Print Communications property

    So I have to use the OperatingSystem property of the Application object to give me another condition.

    Excel 2010 Operating System

    Excel 2010 Operating System

    Excel 2011 Operating System

    Excel 2011 Operating System

    I use the Left function to grab the first three letters of the operating system name.

    Left(Application.OperatingSystem, 3) = “Win”

    Then change my code to account for earlier Excel versions AND the Windows operating system as follows:

    If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
    Then Application.PrintCommunication = False
    ' (my page setup code here)
    If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
    Then Application.PrintCommunication = True

    Using the PrintCommunication property of the Application Object will help speed up your code execution. However, you need to be aware of backward compatibility for earlier versions of Excel that don’t recognize the code, and with the Mac version of Excel 2011 that should have the same property, but doesn’t.

    Quickly Adjust 1904 Dates for the 1900 Date System in Excel

    An associate of mine “helped” me out once by putting a worksheet together for data entry. After the worksheet had several days of user input, I found out the workbook was created on a Mac with an older version of Excel for Mac (2004) that defaulted to the 1904 date system.

    Since the worksheet in question would be used entirely by Windows users, I decided to change to the 1900 date system to avoid any problems. Little did I know that by changing that simple date system setting would change the dates by 1462 days.

    That’s because first day of the 1900 date system starts on 1 Jan, 1900 and the first day of the 1904 date system starts on 2 Jan, 1904.

    Here’s an example of the dates entered with the 1904 date system. I’ve added a column that shows the underlying date serial number.

    1904 Dates in Excel

    To change the Date System setting choose File > Options then click the Advanced tab and scroll down to un-check the box for Use 1904 date system.

    1904 Date Setting

    All dates entered into the worksheet will be altered to fit the 1900 date system and give you totally different dates, which can be confusing. Notice how the dates have changed in the picture below, but the underlying date serial number is the same.

    1904 Dates in 1900 Spreadsheet

    To change this I wrote a simple VBA macro to convert all the date values. Simply select all the date values then run the macro.

    Sub Correct1904Dates()
    For Each cell In Selection
    cell.Value = cell.Value + 1462
    Next cell
    End Sub

    It occurred to me that a macro like this is not needed for this temporary measure. The trick is to use the Immediate window of the VBA Editor and write the code on one line with a colon between the lines.

    Select the date values and open the VBA Editor (Alt+F11), then show the Immediate window  (Ctrl+G) and type the following code.

    VBA Code to Adjust 1904 Dates

    All dates have been converted and the code is no longer needed.

    Documentation for VBA in Excel

    A long time ago, in a far-away classroom I was seemingly forced to write copious amounts of documentation in a FORTRAN class just for a few lines of code. It didn’t help that I was just learning how to keyboard (type) and that I didn’t understand why pseudo code was also required by my nameless, faceless teacher. Yes the experience was so ardous that I can’t even recall the name of the man I should be thanking right about now.

    Oh to be sure, I still don’t like documenting VBA code in Excel. Yet after returning to a lengthy and involved Excel reporting package that I wrote over two years ago the documentation saved me many hours of toil and trouble.

    The following is an example what I normally like to put at the beginning of each subroutine. The overall flow of what the routine is doing, any special input/output variables or noteable arguments, and subroutines or functions that are used or called within the routine.

    ””””””””””””””””””””””””””””””””””””
    ‘ This error check subroutine looks at a number of conditions that
    ‘ would cause the program to fail, and provides the user with an
    ‘ appropriate message. It is called by either the Run Report or
    ‘ Run Reports DT routine and uses the argument SheetName, which is
    ‘ the appropriate sheet name from the calling program. This routine
    ‘ uses the function InvalidDatesInRange, which may call the InputDates
    ‘ routine if true. These both use the SheetName argument. This
    ‘ routine also uses the function EarlyDatesInRange, which may call
    ‘ the EarlyDates routine if true.

    ‘ CHECKS IF INPUT DATA EXISTS
    ‘ CHECKS FOR INVALID START DATE
    ‘ CHECKS FOR INVALID END DATE
    ‘ CHECKS IF THERE ARE INVALID DATES IN THE INPUT DATA
    ‘ CHECKS FOR DATES BEFORE THE EARLIEST DATE SETTING
    ‘ CHECKS IF THERE IS ANY MATCHING DATA
    ‘ CHECKS FOR ANY FUTURE DATE
    ””””””””””””””””””””””””””””””””””””

    Within the subroutine I document segments of code to allow the reader to follow the logic. I try to keep it short and only break out the pseudo code to map out large projects.

    Automatically Expand a Named Range in Excel

    I usually put a name to each data table created for referencing information in in Excel, as in a Named Range. If you add data to the bottom of the table, the Named Range isn’t modified and any reference to it will fail to include the new information.

    Here’s a table of data I stuck on a worksheet called MyData and the range A2:E10 is named myFoodData.

    Named Range to Expand

    Inserting a row inside this range will automatically expand the reference for the Named Range, but normally a user would add data to bottom of the table in the first empty row.

    My solution is event based. I write a simple subroutine.

    Sub ShiftRangeAndRename()
    Const n As String = "myFoodData"
    Dim rng As Range
    Set rng = Range(n).CurrentRegion
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
    rng.Name = n
    End Sub

    Then reference it from the deactivate routine on the MyData worksheet.

    Private Sub Worksheet_Deactivate()
    Call ShiftRangeAndRename
    End Sub

    When a user goes to the MyData worksheet and updates data and returns to the main worksheet, the worksheet deactivate routine calls the routine to update the range reference and its associated name. This also works if they are deleting data, but that’s not common in this type of situation.

    VBA Help System Lacking in Excel 2011

    Since never using the VBA Editor in Excel 2011 for Mac was a slight embarrassment, I thought to give it a whirl with a half-written function that was started in Excel 2010. I quickly found out the Editor’s windows have more of a free-floating style, but the programming looked to be quite the same.

    VBA Editor Excel 2011

    Until I wanted to find out whether to use the TypeName or VarType function and tried to access Help. This is something they make sufficiently hard enough that I didn’t find either function definition before giving up and deciding to write about the futility of being dropped into a maze.

    But first I switched back to Excel 2010, selected VarType in the VBA Editor and activated Help, which quickly generated a nice pop-up window with all the facts I needed about the VarType Function, as you can see below.

    VarType Excel Help

    When you try this in Excel 2011, the following screen is the entrance to the maze.

    VarType Excel Help 2011

    click for larger image

    Continuing on from this screen is when you start feeling that something’s all jumbled and confused in the Help system for VBA in Excel 2011. I don’t recommend going on any further unless you’re desperate.

    Oh what a tangled web they weave.

    Excel ISNUMBER or IsNumeric Function

    I don’t like it when Excel worksheet functions are different from their VBA counterparts. Makes for some aggravation.

    For example, I have a user generated data range that’s supposed to be made up of either numbers or empty cells. However, Excel users sometimes bump their keyboards inadvertently (when you hit the space bar) and things like a space character can get entered into an otherwise empty cell, and go undetected to the naked eye.

    VBA Programming

    As part of a VBA macro I looped through each cell in this data range and performed an operation for any cell value greater than zero. This was a good plan for a data range having only number values or empty cells. (Well, not really as you’ll see below.)

    However, I not only came across a space character in the user data, but also an accent character (`). As you can see in third column of the the chart pictured below, (>0) greater than zero is TRUE for the space character and the accent mark.

    Excel IsNumber or IsNumeric Chart

    What I really wanted was the Excel ISNUMBER fucntion that only shows TRUE when, well, there’s a number. (Fourth column)

    So I tried the VBA Excel IsNumeric function in my macro, thinking it to be the same thing, and found that it didn’t work as I had imagined. For some reason the IsNumeric function shows TRUE for and empty cell as you can see in fifth column of the chart.

    I wrote a function called IsNum() to simulate the VBA function IsNumeric in the worksheet.

    Function IsNum(data As Variant)
    If IsNumeric(data) Then
    IsNum = True
    Else
    IsNum = False
    End If
    End Function

    The difference seems to be that the Worksheet Function ISNUMBER takes a Value for an argument, and the VBA function IsNumeric takes an Expression for its argument.

    My solution is to use both greater than zero (>0) AND IsNumeric for my logic gate to perform further operations. (Last column in the chart) So in looping through the data range above, operations would only be performed on the numbers (23 and 15).

    Lessons Learned About Excel ISNUMBER or IsNumeric Function

    1. Now I know that an accent character (`) is greater than zero.
    2. The VBA function IsNumeric is different than the Excel worksheet function ISNUMBER.
    3. Testing doesn’t always bring out all the ways a user can change the data. Only time and more users can do that.
    4. One logical operation in a very large VBA macro (9 subroutines w/ 2 functions) can bring down the whole show.
    Related Posts Plugin for WordPress, Blogger...