VBA in 98 and OsX

M

margulies

Why do my VBA run in Excel for OSX very much slower than the same do in
Excel 98? Is there a possibility to make them run quicker?
Francesca
 
B

Bernard Rey

margulies said:
Why do my VBA run in Excel for OSX very much slower than the same do in
Excel 98? Is there a possibility to make them run quicker?

It is difficult to give a consistent answer to these questions without
having the slightest idea of the VBA code in your Macro. Could you post some
lines here, or try to see if a specific section in the code is the culprit
for the lack of performance?
 
M

margulies

Bernard said:
margulies wrote:




It is difficult to give a consistent answer to these questions without
having the slightest idea of the VBA code in your Macro. Could you post some
lines here, or try to see if a specific section in the code is the culprit
for the lack of performance?
Thank you for answering!
I was lucky to have your help already years ago!
Here some examples of my code

Additional I must tell you:
I opened the Workbook with the VBA Codes(made in Excel98) with Excel for
OsX (not the newest version 2004)

and saved it in Excel for OsX.
Now all runs very well but slower than in Excel98

The Workbook with the VBA Code does make an Autoopen code, which runs
quickly, here it is:

Sub Auto_Open()

bsystem = InStr(1, Application.OperatingSystem, "win", vbTextCompare)
'kann nun mit der Anweisung (if (bsystem <> 0) then ... (für windows)
[else ... (für nicht Windows)] end if) kontrolliert werden

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Application.Run "invisibleT"
Windows("T2004.xls").Visible = False

'ermittelt den ordner in welchem die taxintosh-dateien sind
pfad_ordner = Workbooks(Workbooks.Count).Path 'liefert den ort (ordner)
vom T
'als nächstes: setzt dem pfad_ordner die einzelnen dateien an, die
geöffnet werden müssen
name_stzh = "SteuernZHNP.xls"
pfad_stzh = pfad_ordner + Application.PathSeparator + name_stzh
Workbooks.Open Filename:=pfad_stzh

'ActiveWindow.WindowState = xlNormal
name_sten = "StEN2004.xls"
pfad_sten = pfad_ordner + Application.PathSeparator + name_sten
On Error Resume Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

......snip, and another code:


Sub BeendenMitSichern()

'FrageBox: Falls No: Sicherung beenden.
Dim Antwort
Antwort = MsgBox("Wollen Sie wirklich BEENDEN und SICHERN?", vbYesNo,
"Taxintosh® StE schliessen, Daten speichern")
If Antwort = vbNo Then
Exit Sub
End If

'Dim Antwort1
'Antwort1 = MsgBox("Ist die Unterjährige oder eine eigene Excel Tabelle
geschlossen? Mit 'Ja' (Enter) geht es gleich weiter, mit 'Nein' (Taste
N) wird die Sicherung abgebrochen.", vbYesNo, "Taxintosh® WARNUNG")
'If Antwort1 = vbNo Then
'Exit Sub
'End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Windows(name_sten).Activate
Calculate

Application.Run "UnprotectKD"
Application.Run "OWerteNein"
Sheets("KD").Select

Range("A1:Z4000").Select
Selection.NumberFormat = "General"

Application.Run "U_Ausfüllen"

.........snip
or:

Sub U_Ausfüllen()
'
' U_Ausfüllen Makro

Sheets("KD").Select
'VVK
Range("B220:I220").Select
Selection.AutoFill Destination:=Range("B220:I237"), Type:=xlFillDefault
Range("B220:I250").Select

'GZ
Range("B255:M255").Select
Selection.AutoFill Destination:=Range("B255:M336"), Type:=xlFillDefault
Range("B255:M336").Select

'A-Seite:
Range("B447:K447").Select
Selection.AutoFill Destination:=Range("B447:K467"), Type:=xlFillDefault
Range("B447:K467").Select

.......snip

sorry, a lot to read...
Francesca
 
B

Bernard Rey

margulies wrote :
Thank you for answering!
I was lucky to have your help already years ago!

Yes, I think I can remember that. It's quite some time ago ;-)
Here some examples of my code

.../...
Application.Run "UnprotectKD"
Application.Run "OWerteNein"

Sheets("KD").Select

Range("A1:Z4000").Select
Selection.NumberFormat = "General"

Try replacing these three lines with this one:

Sheets("KD").Range("A1:Z4000").NumberFormat = "General"


It is often useless to "select" things in a macro. And selecting things,
especially a large range, can be a good reason for a perfomance loss. As you
can see, it is quite simple to perform the same things without selecting the
ranges first. I couldn't say it for sure, of course, but I guess it's worth
a try: it should run faster this way.

Sub U_Ausfüllen()
'
' U_Ausfüllen Makro

Sheets("KD").Select
'VVK
Range("B220:I220").Select
Selection.AutoFill Destination:=Range("B220:I237"), Type:=xlFillDefault
Range("B220:I250").Select

'GZ
Range("B255:M255").Select
Selection.AutoFill Destination:=Range("B255:M336"), Type:=xlFillDefault
Range("B255:M336").Select

'A-Seite:
Range("B447:K447").Select
Selection.AutoFill Destination:=Range("B447:K467"), Type:=xlFillDefault
Range("B447:K467").Select

Try this:
Sheets("KD").Activate
'VVK
Range("B220:I220").AutoFill Destination:=Range("B220:I237")
'GZ
Range("B255:M255").AutoFill Destination:=Range("B255:M336")
'A-Seite:
Range("B447:K447").AutoFill Destination:=Range("B447:K467")
Range("B447:K467").Select ' is this line really needed?
 
J

JE McGimpsey

Bernard Rey said:
Try this:
Sheets("KD").Activate
'VVK
Range("B220:I220").AutoFill Destination:=Range("B220:I237")
'GZ
Range("B255:M255").AutoFill Destination:=Range("B255:M336")
'A-Seite:
Range("B447:K447").AutoFill Destination:=Range("B447:K467")
Range("B447:K467").Select ' is this line really needed?

Can tweak it still further:

Application.ScreenUpdating = False
With Sheets("KD")
With .Range("B220:I220")
.AutoFill Destination:=.Resize(18)
End With
With .Range("B255:M255")
.AutoFill Destination:=.Resize(82)
End With
With .Range("B447:K447")
.AutoFill Destination:=.Resize(20)
End With
End With
Application.ScreenUpdating = True

It's almost instantaneous on my machine (1.33 GHz PB). Don't blink, or
you'll waste the couple of milliseconds it saves you over Bernard's...
 
M

margulies

Thank you Bernard Rey and JE McGimpsey

I am back from Christmas and will now comb my VBA's as you suggest

Francesca
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top