VB scripts from Office 97 that no longer work in Office 2003

[Christian Johansson]

Hi!

Some years ago, I wrote two VB scripts for Excel in Office 97. Today, I
discovered that they no longer work with Excel in Office 2003. Both failures
seem to be related to "Cells". Could somebody please help me with how to
rewrite the scripts to get rid of the errors?

In the first script, I get "Run-time error '1004': Application-defined or
object-defined error" for the line "Cells(Row, Column) = 0" in the following
script. Does anybody know why I get this error and how to rewrite the code
to get rid of it. Unfortunately, I don't remember much about writing Visual
Basic scripts. I just did it briefly and it was some years ago.

'
' Diagramdata Makro
'
' Detta makro fyller i de två diagrammen över basaldos i ett testprotokoll
efter värdena i
' tabellerna längst upp till vänster på de två sidorna som testprotokollet
utgörs av.
'
' Kortkommando: Ctrl+d
'
Sub Diagramdata()
Dim Row As Integer, Column As Integer
Dim Intervall As String
Dim FrånTid As Integer, TillTid As Integer
Dim Time As Integer
Dim i As Integer

Row = 6
For Column = 38 To 61
Cells(Row, Column) = 0
Next Column

Row = 53
For Column = 38 To 61
Cells(Row, Column) = 0
Next Column

For i = 0 To 1
Row = 5 + (47 * i)
Intervall = Cells(Row, 1)
Do While (Row < 15 + (47 * i) And Intervall <> "")
Intervall = Cells(Row, 1)
FrånTid = Val(Left(Intervall, 2))
TillTid = Val(Right(Intervall, 2))
If TillTid > FrånTid Then
For Time = FrånTid To TillTid - 1
Cells(6 + (47 * i), 38 + Time) = Cells(Row, 2)
Next Time
Else
For Time = FrånTid To 23
Cells(6 + (47 * i), 38 + Time) = Cells(Row, 2)
Next Time
For Time = 0 To TillTid - 1
Cells(6 + (47 * i), 38 + Time) = Cells(Row, 2)
Next Time
End If
Row = Row + 1
Intervall = Cells(Row, 1)
Loop
Next i

End Sub

In the second script, I get "Run-time error '-2147319784 (80028018)':
Automation error Invalid data format" for the line "If Cells(Row, Column) =
"Neg" Then". The macro is listed below. Does anybody know how I can modify
the script to get it working?

'
' Textfärg Makro
'
' Detta makro går igenom ett testprotokoll och ändrar färgerna på texten för
' urin- och blodsockervärden beroende på hur högt värdet är.
'
' Kortkommando: Ctrl+t
'

Sub Textfärg()
Dim i As Integer, Row As Integer, Column As Integer
Dim Blodsockervärde As Double

For i = 0 To 1
For Row = (20 + 47 * i) To (50 + 47 * i) Step 2
For Column = 2 To 4
If Cells(Row, Column) = "Neg" Then
Cells(Row, Column).Font.ColorIndex = 10
ElseIf Left(Cells(Row, Column), 1) <> "-" And Cells(Row, Column) <>
"?" And Cells(Row, Column) <> "" Then
Cells(Row, Column).Font.ColorIndex = 3
Else
Cells(Row, Column).Font.ColorIndex = 0
End If
Next Column
For Column = 5 To 13
If Left(Cells(Row, Column), 1) = "-" Then
Cells(Row, Column).Font.ColorIndex = 0
Else
If Left(Cells(Row, Column), 2) <> "ca" Then
Blodsockervärde = CDbl(Cells(Row, Column))
Else
Blodsockervärde = CDbl(Right(Cells(Row, Column), Len(Cells(Row,
Column)) - 2))
End If
If Blodsockervärde <= 4.5 And Blodsockervärde > 0 Then
Cells(Row, Column).Font.ColorIndex = 5
ElseIf Blodsockervärde >= 8.5 Then
Cells(Row, Column).Font.ColorIndex = 3
ElseIf Blodsockervärde > 4.5 And Blodsockervärde < 8.5 Then
Cells(Row, Column).Font.ColorIndex = 10
Else
Cells(Row, Column).Font.ColorIndex = 0
End If
End If
Next Column
Next Row
Next i
End Sub

Best Regards,

Christian Johansson (change "combort" to "comhem" in my e-mail address if
responding via e-mail)

 

[Chris Leonard]

It sounds like the Cells function is either no longer supported or the
syntax has changed ..... or theres a bug - not an unknown event in new MS
software releases.

Take a look at how to use the Cell function in Office 2003 (highlight Cell
in your marco and press F1)

Take a look at MSDN and see if there is a fix for an error like this

 

[Dave Peterson]

First, I don't have xl2003 installed. But I can't imagine that .cells() would
be taken away.

But Row and Column are both reserved words in VBA. Maybe xl2003 started being
more stringent. I'd change the Row variables to myRow and same with Column to
myColumn. (But I'd be kind of surprised if this were the case--but I'd fix it
anyway!)

Try looking under Tools|References. Search for anything marked MISSING. If you
have a missing reference, you could get an error that's not related to the
MISSING reference.

If you need the reference, then you'll have to find it or change to the newer
version (maybe a reference to a newer component of Office????).

But I'd still spend some time fixing those variable names.

 

[Christian Johansson]

Now, I noticed something very strange. I create a new .xls document every
year with the macros that now don't work with Office 2003. If I try to run
the macros in documents from 1995-1998 or earlier they work fine. These .xls
documents were originally created with Excel 4.0 and Excel 5.0 under Windows
3.1. On the other hand, if I run exactly the same macros in documents from
1999-2003 they don't work. These documents were created with Excel in Office
97 under Windows 98. Even if I record a very simple macro that just writes
something in a cell, I get an error when trying to run it with Office 2003.

 

[Christian Johansson]

Now, I noticed something very strange. I create a new .xls document every
year with the macros that now don't work with Office 2003. If I try to run
the macros in documents from 1995-1998 or earlier they work fine. These ..xls
documents were originally created with Excel 4.0 and Excel 5.0 under Windows
3.1. On the other hand, if I run exactly the same macros in documents from
1999-2003 they don't work. These documents were created with Excel in Office
97 under Windows 98. Even if I record a very simple macro that just writes
something in a cell, I get an error when trying to run it with Office 2003.

No, that was not completely true. If I try to open one of the documents from
1995-1997 that were created with Excel 5.0, I get "Error in loading DLL".
Then, I get a message that Excel has repaired the file, that the Visual
Basic project has been lost and that I should save it. On the other hand, if
I try to open the document from 1998 that was created with Excel 5.0 and
later converted to Office 97, the macros work. However, if I try to open the
documents from 1999-2003 that use the same macros and that were entirely
created with Office 97, I just get an application error when trying to run
the macros. It seems like Microsoft have ****ed up in the backwards
compatibility.

 

[Dave Peterson]

You may want to post a snippet (not the workbook) of code that's having
trouble. And indicate the line with the error and post the error message.

IIRC, I had to make some tweaks when I went from xl95 to xl97. (I think xl95
was more forgiving in syntax--but it's been a long time.)

 

[Guest]

We have the same isure too.

I have an old Excel 97 doc with VBA Modules.
In the following funktion occured now under
Excel Xp an Error at line 4 ( ActiveWindow.DisplayWorkbookTabs = True ).

The Error Message is :

Run-time error -2147319784 (80028018)
Method of 'DisplayWorkbookTabs'of object 'Window' failed



Sub Auto_Öffnen()
VersionsCheck
If UCase(ActiveWorkbook.Name) = "STUNDEN.XLS" Then
If ActiveWindow.DisplayWorkbookTabs = True Then
ActiveWindow.DisplayWorkbookTabs = False
Sheets(1).Visible = True
'Sheets(2).Visible = False
Sheets(1).Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
'Sheets(2).Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Sheets(1).Select
ActiveWorkbook.Save
End If
Application.ScreenUpdating = False
Home = Pathname(ActiveWorkbook.FullName)
u_Base = Basename(ActiveWorkbook.FullName)
Ext = Extender(ActiveWorkbook.FullName)
x = Now() ' aktuelles Datum
On Error Resume Next
For Count = 1 To 16
File = "STD_" & Format(x, Datum_YYMM) & "." & Ext
Path = Home & "\STD_" & Format(x, Datum_YYYY)
If Aktiviere_Mappe(Path, File) Then
Exit For
End If
If Aktiviere_Mappe(Home, File) Then
Exit For
End If
x = x - Day(x) ' Letzter Tag im vorhergehenden Monat
Next Count
Application.ScreenUpdating = True
Windows("STUNDEN.XLS").Activate
ActiveWorkbook.Close
Else
ActiveWindow.DisplayWorkbookTabs = True
Sheets(1).Visible = True
'Sheets(2).Visible = True
Sheets(1).Unprotect
'Sheets(2).Unprotect
'Sheets(2).Select
End If
End Sub

Regrads
Ralf Welling

 

[Jim Cone]

Ralf,

If there is no active workbook, the code will fail.
The workbook that has the code could be hidden (Personal.xls ?)

Regards,
Jim Cone
San Francisco, Ca