Excel 2003 To Excel 2007, VBA conflicts...{Rectified}

[Spajeoly]

So, we got a new computer that is beyond what we need spec-wise. But it comes with Office 2007.

Now, my request is to everyone who has used Office 2007 & 2003 to post what VBA conflicts they are aware of.

One of our big projects involved a very lengthy VBA app written in 2003. I have seen 2003 VBA code turn into stinky cottage cheese in Excel 2007.

I know I won't be able to know for sure til we get the thing on it, but I would appreciate any pointers on what functions or methods to watch out for, from anyone who has had any issues running 2003 code in 2007.

Thanks.

[RobDog888]

AFAIK, there are no breaks in backwards compatibility with the exception of the CommandBars being partially replaced by the Ribben

The only real way to validate if it will break or not is to test it.

[pgag45]

I had to put Option Explicit on all my code in 2007... I realize its bad habit to not have it on all the time, but some of my older code didn't have it.

[Spajeoly]

Don't feel bad, Pgag, I never use it either. Long live bad habits!

I remember at another job I worked, we had issues with some functions, I just don't remember which. I cannot wait to find out!

Just for fun, I will post any issues I have in this thread.

[anhn]

Not sure anyone know all the differences.
This link listed some of them.

Just google "Excel 2007 vs Excel 2003" it will give you over 5 million entries.

[RobDog888]

Thats more of an operational difference rather then differences between versions on methods, functions and properties etc (programming differences).

[anhn]

Thats more of an operational difference rather then differences between versions on methods, functions and properties etc (programming differences).

Agree! I just googled that and picked the first entry. Plenty of them there.

Another source is to search "What's New?" topic in Excel-2007 VBA Help.

[RobDog888]

Yea, I had googled earlier to day and came across a bunch of operational differences and nothing on programming differences.

[anhn]

Narrow down, google "Excel 2007 vs Excel 2003 VBA" give 1.56 million entries.

[Spajeoly]

Well, I lucked out. The only thing I need to do now is learn how to re-add a menu item in the Add-ins that let's the workers run the macro by just selecting the menu.

I don't even know how it was setup in 2003, the last guy did it.

Any thoughts on this?

[Spajeoly]

Ok, I figured it out the minute I sat down and played with it.

[pgag45]

Well, I lucked out. The only thing I need to do now is learn how to re-add a menu item in the Add-ins that let's the workers run the macro by just selecting the menu.

I don't even know how it was setup in 2003, the last guy did it.

Any thoughts on this?

the infamous "last guy"

no but honestly, the last guy's code is always horrible and not commented.. i think that's fact. at least where I work

[Spajeoly]

You are correct, it's a fact.

This guy has at least 2,000 lines of useless code.

Example: He has a full function with 20 lines of code to count how many elements an array has.

Tip of the iceberg.

[pgag45]

hah thats one ugly iceberg...good luck with that .. haha

[Hack]

Example: He has a full function with 20 lines of code to count how many elements an array has.

I got that beat.

I took over a VBA project in which the last guy had 10 or 20 public variables declared, and all of the variables were single character letters.

Things like

Public a As String
Public q As Integer
'etc

[Spajeoly]

Game on, pal!

Here are 2 subs pasted directly from his code....

Private Sub ReplaceAllCommasWithLF()
    Do
        curCell = ActiveCell.Value
        commaLoc = InStr(curCell, ",")
        If commaLoc > 0 Then
            firstHalf = Left(curCell, commaLoc - 1)
            secondHalf = Right(curCell, Len(curCell) - commaLoc)
            ActiveCell.Value = Trim(firstHalf) & vbLf & Trim(secondHalf)
        End If
    Loop While commaLoc > 0
End Sub

And...

Private Function FindLastCell(RatesCell As Range) As Range
    Dim LastColumn As Integer
    Dim LastRow As Long
    
    'These next few lines didn't always work!!!
    'If WorksheetFunction.CountA(Cells) > 0 Then
    '    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas, LookAt:=xlPart).Row
    '    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas, LookAt:=xlPart).Column
    'End If

    'So I did it the long way
    LastRow = RatesCell.Row + 2
    LastColumn = RatesCell.Column + 1
    Cells(LastRow, LastColumn).Select
    'Get Last Row
    Do While ActiveCell.Value <> ""
        LastRow = ActiveCell.MergeArea.Cells(ActiveCell.MergeArea.Rows.Count, 1).Row
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.Offset(-1, 0).Select
    
    'Get Last Column
    Do While ActiveCell.Value <> ""
        LastColumn = ActiveCell.MergeArea.Cells(1, ActiveCell.MergeArea.Rows(1).Cells.Count).Column
        Cells(LastRow, LastColumn + 1).Select
    Loop
    Cells(LastRow, LastColumn).Select
    
    Set FindLastCell = Cells(LastRow, LastColumn)
End Function

[RobDog888]

So that code looks like it shouldnt have an issue with 2007.

[Spajeoly]

So that code looks like it shouldnt have an issue with 2007.

Funny guy!

[RobDog888]

I didnt realize the microphone was on.

All your posted code is using nothing more then the core basic functions and methods which are in both versions.

[Spajeoly]

The code I posted was for robb to see what i was dealing with. I guess the topic of the original thread is over.

Just venting about dealing with ridiculous code.

[rinser]

Funny how the "last guy" is always a moron whereas we are the brightest and shiniest
Missed fact si that "last guy" probably moved up to a warmer place while we, the bright ones, moved to his position and are actually very happy and consider ourselves lucky to have landed that position.
In the second part of the code he actually explains that it is "the long way" but it WORKS. Have you tried your short version of the same? Did it work? Please share.

[Spajeoly]

Ok, let's dig up the past....

Oh let's see, replace commans with Lf?

Text = Replace(Text, ",", Chr(10))

Number of elements in an array?

ThatCount = UBound(Array) +1'+1 for those who do not know arrays start at 0

Find last row in a Column? There are millions of ways.

There is a single line method in VBA, but I find those less fun, and I often need to do stuff on my way to the answer, so I use loops

Off the top of my head:

Dim LastRow$
For i = 1 to 999999
if range("$A$" & i).Value = "" Then
LastRow = "$A$" & (i -1)
Exit For

Happy?

In this case, the last guy was in fact a hack and a half who faked his way into the position and recorded half the VBA code and trolled forums like VBF to get his code.

Granted, who hasn't done that, right? But I would never lie in this manner to get a job, I think it would terrify me.