2007 compatibility

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]

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!

:work:

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

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. :(

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

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

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

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

Quote:

---

Originally Posted by Oorang

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.

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]

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.

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

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]

Looks like a winner to me:)

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

Then it returns the last row of B ...

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

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

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

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]

Well I found another issues. 2003 xls sheets with cells that have validation loose there validation when opened in Excel 2007.

This sucks, I had to rebuild them all.

If that's not enough, the whole save as interactive web page is gone in 2007. They want us all to purchase MOSS 2007 + (Excel Servicesfor $25/user/year). Sounds cheap until you start multiplying.

Are you sure? I haven't heard this, and so I just tried it. Just a simple list DV I admit, but no problems here.

Are there some particular situation where it happens?

I just opened up one of my workbooks built in Excel 2003 and all the data validation seems to work OK in 2007. The 3 types I checked were list, whole numbers, and dates.

Which type if DV was not working for you?

Ron

Conditional Formatting
In 2007, cel.FormatConditions(1).Formula1 will return a string like "=2"
previously this was returned as the simple value.

Use Evaluate to get the numeric value
[VBA]
With cel.FormatConditions(1)
myColour = .Interior.ColorIndex
myOp = .Operator
myVal = Evaluate(.Formula1)
End With
[/VBA]

Ok. I didn't communicate my issue correctly. What I should have said was that to change a validation message in 2007 you need to first delete the old validation (if it exists). In 2003 this worked without deleting it:

[VBA]

Cells(intRow2Write, 7).Validation.InputMessage = strGateCrit

[/VBA]

Now I have to delete first then add it from scratch including the validation and the validation message.

[VBA]
With Cells(intRow2Write, 7).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$G$1:$G$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Criteria:"
.ErrorTitle = ""
.InputMessage = strGateCrit
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
[/VBA]

Sorry about the confusion.

I have just changed the InputMessage in 2007 without deleting the validation. Seems the same to me.

Well I guess I'm loosing my credibility fast here. I don't know why I'm having these issues and no one else is. Perhaps it has something to do with creating the validation in an xls in 2003, and trying to update the message on the xls book in 2007. Regardless, I'm going to drop it. "Don't fix it if it ain't broke", my grandpa used to say. Thanks for the ear...

Has anyone experienced formula errors when saving a 2007 WB as a 2003?

A customer has mandated that no one use any office 2007 product due to errors. This includes back-saving Excel 2007 as 2003 due to "formula errors in the 2003 WB."

Google does not locate any articles that seem to address bad formulas being generated

Has anyone heard of this issue?

Paul

We have seen disappearing range names, but that was fixed by a hotfix a couple of months back.

An issue I found was with the sort method. I recorded a macro in 2007 and it will not function in 2003 because the sort method has changed. My guess is that this will happen with a lot of recorded macros.:bug:

But a sort macro recorded in 2003 will work in 2007.Moral of the story, develop on the lowest version.

Often the problem with recorded macros is new/changed parameters. It's always a good idea to clean up recorded code to remove the stuff you don't need - if you don't need to change a specific parameter and the macro recorder has just inserted the default value, get rid of it!

Thanks for all the tips here! I'm currently still in 2003 at work, but some of the people I support have switched to 2007 and I can already see the headaches piling up on the horizon...

(It's a VERY large company - I have no say in when my computer will be upgraded... :banghead: )

Quote:

---

Originally Posted by kestrylr

Thanks for all the tips here! I'm currently still in 2003 at work, but some of the people I support have switched to 2007 and I can already see the headaches piling up on the horizon...

(It's a VERY large company - I have no say in when my computer will be upgraded... :banghead: )

---

Trust me, you'll hate the move to 2007, just think of it as if someone swapped your hands over, rearranged the fingers and then turned them back to front and you are trying to thread a needle! :)

I have a true dislike for it, i've moved on to 2010 and it seems a tad better although everything still seems misplaced for me, still hard to get used to.

It's a HUGE learning curve - it's the first time in years that I've really had to hunt to find things in an Office product upgrade. I have seen some tools rolled out whereby you can go through a faux Office 2003 screen to select an option, and it shows you where the corresponding option lives in 2007.

Getting used to it now (and I do like some of the new features) but my biggest frustration is with the ribbon. Their policy appears to be "I'm going to guess what I think you want to do next, and make the ribbon that you actually want (and hunted for 5 minutes to find) disappear again". I'd love to switch this off (if I could work out how to do it) - contrary to Microsoft's opinion I generally have a slightly better idea of the functions I need to access next then they do!

Quote:

---

Originally Posted by geekgirlau

........ - contrary to Microsoft's opinion I generally have a slightly better idea of the functions I need to access next then they do!

---

Have you told them this? they probably have never heard that before :rotlaugh:

Heaven forbid that they actually listen to their client base and make you a valued part of the development in creating something that we (the collective frustrated office users "We") can actually use to perform our task efficiently!

Quote:

---

Originally Posted by Simon Lloyd

I have a true dislike for it, i've moved on to 2010 and it seems a tad better although everything still seems misplaced for me, still hard to get used to.

---

Know the feeling ... Bosses wanted to go from 2007 (finally everyone was used to it and most things worked back (after some sleepless nights) to 2010 because they have the option for included upgrades.

Sometimes I think, although I know I don't know everything, how hard it must be for my fellow collegues if even I have some difficulties to grasp everything that has been changed.

Charlize

I found an issue with using Listbox.Selected(i) in 2010 to test for any selected items. In the end I came up with this to be compatible between 2003 & 2010

[VBA]'Check for Selected; handle 2003 and 2010 versions
If IsNull(ListBox1) And ListBox1.Selected(1) = False Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If[/VBA]

Why not test ListIndex?

Thanks Bob. I new I was missing a trick!