Excel version & Row and Column limits

If macros must run on multiple versions of Excel, care is needed not to overrun the older versions limits. Microsoft made a major change to the Row and Column limits in Excel 2007:

Until Excel 2003:

• 65,536 Rows
• 256 Columns

From Excel 2007

• 1,048,576 Rows
• 16,384 Columns

There are other changes, like unlimited number of sheets and the size of content each cell can have. All huge increases from the previous versions. Excel limits full list can be found here:

https://support.office.com/en-us/article/Excel-specifications-and-limits-CA36E2DC-1F09-4620-B726-67C00B05040F

It can be easy to cross the smaller limits, specially the 256 columns. If the macro adds data in columns, and the input is large, it can easily step over 256, crashing the macro in the process.

To avoid this, macros should check the Excel version they are running and the respective limits. This can be used to check if the limit is going to be cross and show a friendly error message to the user, instead of just crashing unexpectedly.

GetExVersion() 

This function takes no parameters and just returns the Excel version running the macro. Notice that Excel versions are numbers, not the commercial naming – Excel 2007 is 12, Excel 2016 is 16.

With this function we can define 2 more to retrieve the Row and Column limits:

GetMaxRows(nVersion”) eg: GetMaxRows (12)

GetMaxColss(nVersion”) eg: GetMaxCols (12)

Both functions receive the version number as a parameter and return the respective max row or column number (note: it is not the number of rows or columns, but the number of the last one – which is 1 less, so 255 for the last of 256 columns):

The easiest way to use this functions it to call them using the GetExVersion() function as a parameter. This way, it is complete self-sufficient – no need to provide any information to get a result:

nMaxRowsVariable = GetMaxRows(GetExVersion())

You can copy the code here:

Public Function GetExVersion() As Long
' Return Excel version number
Dim nOut As Long                            ' Declare the variables

    nOut = Val(Application.Version)         ' Get Version Number from system variable
    
GetExVersion = nOut                         ' Return result to caller

End Function

Public Function GetMaxRows(nVersion As Long) As Long
' Return the maximum number of rows
Dim nOut As Long                            ' Declare the variables

    If nVersion >= 12 Then                  ' If >= Excel 2007
        nOut = (2 ^ 20) - 1                 ' New versions limit
    Else
        nOut = 65535                        ' Older versions limit
    End If
    
GetMaxRows = nOut                           ' Return result to caller

End Function

Public Function GetMaxCols(nVersion As Long) As Long
' Return the maximum number of columns
Dim nOut As Long                            ' Declare the variables

    If nVersion >= 12 Then                  ' If >= Excel 2007
        nOut = 16383                        ' New versions limit
    Else
        nOut = 255                          ' Older versions limit
    End If
    
GetMaxCols = nOut                           ' Return result to caller

End Function

Manual Fallback: The Invisible Worker Rule

There are many ways to write macros. From experience, on a business environment, there are some general rules that will make them easier to write and use at the long term.

There is however one rule to “rule them all":

• MANUAL FALLBACK “the invisible worker”: the best macros are “invisible”. They produce Excel sheets exactly like the ones being created manually. They do not have macros themselves or macro functions, they contain numbers, text and links. Once done, it is impossible to distinguish between the macro output or a manual created one;

This is very important as it guarantees a fallback – if for any reason the macro fails (and they do fail) and it is not possible to overcome the issue (because the person who did the macro is not available, or it will take too much time), it can still be done by hand.

As an example: on one company, every month, hundreds of similar Excel files must be linked into a “consolidated” view. All input files where pretty similar but with slight differences, so linking could not solve the problem because the exact cells to link would change every month. They had to be found by criteria, not hard links to specific cells. This was done manually by one person for a whole week, and would usually have errors and hundreds of links would be created from the consolidated file to each input file.

Instead of creating a macro on the consolidated file “pulling” the values, the macro was created on a separated file. It would scan all input files and create the links on the consolidated file. Once done, it was similar to the file that was manually created before. But it would only took 2 hours to run and had no errors.

So, the macro was an “invisible worker”, doing in 2 hours the exact same work as before in 5 days, freeing one person to do values added tasks instead on Excel links. But the end result can still be checked, validated or performed manually, in case the macro fails.

Work smarter, outsource your boring work to your “invisible worker”!

Activating the right sheet

Changing the active sheet given its name is one of the basic tasks when writing business macros and can be very simple with this VBA Bits library function:

SetActiveSheet (sheetname) eg: SetActiveSheet ("Sheet2")

The function is quite simple:

If the sheet exists, it will make it the active sheet (the one selected in Excel and visible to the user). If not, nothing happens, and no error is generated - it just does nothing, retaining whichever sheet is already active on the Excel file.

How does it knows if the given Sheetname exists? It uses the DoesSheetExists(“sheetname”) VBA Bits function detailed on an other post:

Testing if a sheet exists on the Excel file, given its name

You can copy the code here:

Sub SetActiveSheet(sSheetName As String)
' Activates the desired sheet by name if the sheet exists
Dim bOut As Boolean                                                     ' Declare the variables

    bOut = False                                                        ' Default - Assume Failure
    If DoesSheetExists(sSheetName) Then Worksheets(sSheetName).Activate ' Activate sheet with given name if it exists

End Sub

Testing if a sheet exists on the Excel file, given its name

DoesSheetExists (“sheetname”) eg: DoesSheetExists ("Sheet2")

Checking if a given sheet exists having its name is simple - iterate all existing sheets and compare each one’s name with the given one. If a match is found, the function returns a TRUE value to its called. If not, FALSE is returned and no sheet with the given name was found.

The sSheetName variable is a function parameter, supplied between () after the function name. Is is a string (text) and can either be a literal text ("Sheet1") or a variable from existing code - examples of this will be found on the next VBA Bits post, which uses this function.

The UPPER() function forces text to be uppercase, assuring the comparison is not case dependent, which is how Excel treats Worksheet names.

You can copy the code here:

Function DoesSheetExists(sSheetName As String) As Boolean
' Checks if a Sheet name exists - Returns TRUE or FALSE
Dim bOut As String, WSN As Worksheet                    ' Declare the variables

    bOut = False                                        ' Default - Assume Failure
    For Each WSN In Worksheets                          ' Loop all worksheets in current file
        If UCase(sSheetName) = UCase(WSN.Name) Then     ' Compare each sheet name with the supplied one
            bOut = True                                 ' Name Found
            Exit For                                    ' Exit loop - already found the sheet name
        End If
    Next                                                ' Loop until all sheets checked

DoesSheetExists = bOut                                  ' Return result to caller

End Function

The VBA Bits – getting rid of “plumbing”

Copying web development ideas.

In the past, web developers used to have a very rough deal - they had to spend most of the time coding tedious tasks like locating the page element to change or just plain working around browser differences. Then, one day, libraries like JQuery arrived taking care of all this “plumbing” and allowing them to just write what they needed to do:

(from http://purencool.com/javascript-and-jquery-what-is-the-difference )

This approach was so successful than nowadays there are thousands of libraries to write web & mobile applications – some generic, some specialized like charting libraries. But the lesson remains: “Code your needs, not plumbing”

This blog attempts to replicate this success with the VBA Bits. Small prices of independent VBA functions that can be copied to any macro to simplify specific tasks. Just copy them to a code module on your own macro and use them to simplify tasks. 

Forget the macro recorder - It’s not that easy

So why are not everyone writing macros?

If you’re reading this blog, you probably already know from experience: because writing macros is not easy.

Most tasks are repetitive BUT with slight differences that demand it to change/adapt according to case. So they need some sort of intelligence, which is not easy to program for anyone but software developers. Which is not the case for Excel business users.

What about the built-in macro recorder? Most people first encounter with macros involve the macro recorder and it creates the illusion that you don’t need to write a macro. Just perform the task you want to automate and record it. Then run it again and again.

Well, if your repetitive task is EXACTLY THE SAME every time, it may work. But almost always, there will be some differences:

• Different number of rows or columns
• Conditional tasks – merge A and B to get a product code only if B is numeric
• Changes in data for every iteration
• …

The macro recorder will record EXACTLY what you did when recording. So, if you delete the “-“ on the product code in cell C10 because it is a number, it will do it again to cell C10 next month – even if it is not a number and the number is on row 12 because the product list is different every month.

There is actually a very good use for the macro recorder – and it will be explained in a later post. But it is not writing macros.

Excel Macros: automating the boring stuff

Work Smarter – outsource the boring tasks to Excel

Excel macros are “Excel Apps”. Some smaller, some larger, but all of them are “Apps” that extend Excel native capabilities. And, like any other App for a Smartphone, Tablet or PC, they are software.

And what can they do for you? Well, automate the million time-consuming tasks that anyone using Excel do. Most specially, the really annoying, no value added, long and repetitive tasks, like manipulating data on 6000 rows every month. So, how far can it go? Well, I’ve seen cases like this: 3 days to 11 seconds. 

Most cases are not this extreme, but even if the macro takes some time to run, like 2 hours and locking Excel while running (no Microsoft Office application will run acceptably on the PC while the macro is running), it is running by itself. So you are free to do something else (maybe on another PC). Or leave it running unattended during lunch time or overnight and deal with the results when you’re back.

That’s it – you can start outsourcing some of your boring work to Excel and leave it alone to come up with the results.