2007 compatibility

[mdmackillop]

I came across this problem, which I think may be quite common in opening pre 2007 workbooks from Excel 2007

[VBA]LastRow = Workbooks("Test.xls").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
[/VBA]
In this case, Rows.Count can take the value of 1048576 from my 2007 workbook, which causes an error as it does not exist in Test.

Fully qualify the Range and the Rows.count by changing to
[VBA]
Sub Test()
With Workbooks("Test.xls").Sheets(1)
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
End Sub

[/VBA]

[paulked]

Many thanks for the info. I'm upgrading today and any anomilies like this will be very helpful to anyone upgrading.

Please continue this sticky!

[CaptRon]

Discovered this characteristic about Excel 2007 shortly after our office converted to Office 2007. Thought I'd share it.

The workbooks I had built in Excel 2003, when converted to .xlsm, would not enable macros and wouldn't offer the user the option to do so. I researched the issue online and someone mentioned that the worksheets and workbooks could not be protected or Excel 2007 won't enable macros.

After monkeying around with Excel 2007 for a while, I discovered that Excel 2007 doesn't care if the worksheets are protected, with or without a password, but it WILL NOT enable macros in a workbook that is password protected on open. Period! Won't give you the option to enable either, even if you place the workbook in a "trusted location." The workbook can be protected when opened, just not password protected.

Once the workbook is open, you can apply password protection to the workbook itself and everything works OK because the macros have already been enabled. I am now making sure that workbook protection is removed on close and then reset by the user (click this button to show sheets) after its opened.

FWIW.

Ron

[Ken Puls]

I just had a FileSearch issue raise it's ugly head for me as well. I know that it doesn't work in 2007 any more, but due to a brain cramp, I tried testing a routine in 2007 that used this method.

Removing it is one thing, but they could at least make it throw an error... As it is right now, the routine just silently ends, without even giving the chance to do anything about it. Not very cool at all.

[Zack Barresse]

Wasn't that stupid? This is what we get from one dumb a$$ progger - ASSUMING! I throw curses at that person..

[Ken Puls]

I figured it actually came about by committee. In my experience there usually seems to be one of those beyond the most ridiculous decisions...

[CaptRon]

Xld helped me with an issue that deals with the file format and syntax variances between Excel 2007 and earlier versions.

I created a workbook in Excel 2003 for distribution within our organization. Only about 10% of the agency at this point has migrated to Office 2007. The workbook creates a duplicate of itself and I wanted that duplicate to save in the Excel file format resident on the user's machine.

When the portion of code was added to accommodate the save routine for Excel 2007, it was not recognized by Excel 2003 and was then treated as an undeclared variable.

The solution is found at:

http://vbaexpress.com/forum/showthread.php?t=20220

Ron

[Oorang]

Does Worksheet.UsedRange.Rows.Count cause any issues?

[Bob Phillips]

Does Worksheet.UsedRange.Rows.Count cause any issues?

No it won't. It is unlikely that UsedRange would be all rows, and even if it were it would not be a problem as Count is a Long, so it caters for 1M+ rows easily.

However, Worksheet.Cells.Count is a different matter. We are talking 17Billion+ cells here and that overflows Count significantly. Becuase of this, 2007 as an extra property, CountLarge, which returns the answer.

Interestingly, type

?TypeName(Activesheet.Rows.Count)

in the immediate window, and it tells you it is a long. Type

?TypeName(Activesheet.Cells.CountLarge)

and you get a Compile Error.

[Oorang]

XLD >> That is interesting.

MDMackillop >> Why aren't we just using something like this?

[VBA]Public Function GetBottomRow(ByRef ofWorksheet As Excel.Worksheet) As Long
Const lngOffset_c As Long = 1
Dim rngUsed As Excel.Range
Set rngUsed = ofWorksheet.UsedRange
GetBottomRow = rngUsed.Rows.Count + rngUsed.Row - lngOffset_c
Set rngUsed = Nothing
End Function[/VBA]

[Bob Phillips]

Why use

[vba]

GetBottomRow = rngUsed.Rows.Count + rngUsed.Row - lngOffset_c
[/vba]

isn't rngUsed.Row always 1, so you could just use

Why use

[vba]

GetBottomRow = rngUsed.Rows.Count
[/vba]

but usedrange has the leftover bug, which spoils it anyway.

[Oorang]

lol Well mostly because it's not always "1". Create some test data in a worksheet and then insert several empty rows above it. The start of the used range will be the first row with data.

I know, I know... "Who in their right mind would that?"

hehe I can only say I have seen it done

(It's usually the same people who think that color is great way to show if an account has been paid or not.)

[Bob Phillips]

Okay, but you can do it a lot simpler notwithstanding the limitations of UsedRange

[vba]

Function GetBottomRow(ByVal sh As Worksheet) As Long

With sh
GetBottomRow = .UsedRange(.UsedRange.Count).Row
End With
End Function
[/vba]

[Oorang]

Looks like a winner to me

[mdmackillop]

... and if Column B data is longer than Column A data?

[Bob Phillips]

Then it returns the last row of B ...

[markmrw]

I discovered this after my work PC was upgraded to 2007, while other PC's in the company were still running 2003

[VBA]
.TintAndShade = 0
[/VBA]

I used the record macro button. to change some formats on a couple of sheets, and this line of code runs fine with 2007,
but any earlier versions, break and explode causing many of my colleagues much distress and anguish.

thoought this might be handy for all.

Mark

[Oorang]

Since interest seems to be increasing, here is a comprehensive list of all Adds and Changes to Excel Object Model since 2003:
http://msdn.microsoft.com/en-us/library/bb149069.aspx

As a side note, if you are going to use 2007 methods/properties in a mixed shop it might be beneficial to call them in a wrapper. In this way you will work as expected in either version. Here is a simple example of how to do this:

[vba]Sub Test()
ColorRange Selection, Excel.Application.version, 6
End Sub
Sub ColorRange(rng As Excel.Range, version As Double, ParamArray args() As Variant)
With rng.Interior
.colorIndex = 6
.Pattern = xlSolid
If version >= 12# Then
'Because the property name is stored in a string this will still compile.
'And it will only get called if the correct version is in use.
CallByName rng.Interior, "TintAndShade", VbLet, 0
End If
End With
End Sub
[/vba]

Edit:
Just occured to me you could put version inside the wrapper instead of passing it as a parameter. (I know... duh)

[markmrw]

Thank you oorang

Very helpful, bookmarked
I keep finding many little things, if i find any not listed i will post.
Now that christmas is over, back to the grind stone i suppose.

Thanks
Mark

[Oorang]

Ok, so the last row discussion came up over on MrExcel... And I think this function from Rorya may account for more vagaries than the "usedrange" approach we were discussing.

[VBA]
Public Function LastRowInSheet(wks As Worksheet) As Long
'From Rorya on MrExcel:
'http://www.mrexcel.com/forum/showthread.php?p=1793851&posted=1#post17938521
' Returns the number of the last row with data anywhere in it
LastRowInSheet = 1
On Error Resume Next
With wks.UsedRange
LastRowInSheet = .Cells.Find("*", .Cells(1), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End With
End Function
[/VBA]