Macro handling difference between MS Excel 2010 an

Question

I am currently modifying a Macro that was made early 2005 and running on previous versions of Excel. Recently it is being used in MS Excel 2010, but I am using MS Excel 2013 to modify the codes. Will there be any effects or compatibility issues?

A link explaining the handling of macros on different version of Excels will be very much appreciated. Thank you.

Answer 1

Here's one "gotcha" to be on the look-out for: with Excel 2007, Microsoft introduced the "Big Grid". This was a huge jump in the number of available rows and columns, which your users have potentially started leveraging.

Perhaps you had a forward-thinking group writing VBA for your business in 2005, but at my old gig the following was status quo for identifying the last row:

LastRow = Range("A65536").End(xlUp).Row

If a 2007, 2010 or 2013 worksheet has more than 65,536 populated rows, the above is not going to return the expected result, and your old script will fail, or, worse, will return an incorrect result.

You should sniff out any last row (or last column) identification like the above and refactor it using the techniques described by @SiddharthRout in the definitive post on finding the last row: Error in finding last used cell in VBA... In a nutshell:

Find the last row in a column (below looks for the last occupied row in column A on Sheet1)

With Sheets("Sheet1")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

Find the last row in a sheet (below uses CountA to protect against an empty Sheet1)

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With