Some VBA functions not working in Excel 97

[larsson7]

Hello,

A workbook that I have created in Excel 2000 contains some functions in the VBA code that will not run in Excel 97. These functions are round() and formatCurrency() among others.

Is there any way of getting them to run in Excel 97, or is there an altenative?

Thankyou for your time.

Regards,

Cameron

 

[dk]

Hi,

Instead of FormatCurrency you should be able to use Format e.g.

Sub TestOfFormat()
Dim x
x = 100200.23
MsgBox Format(x, "$#,##0.00;($#,##0.00)")
End Sub

As for Round, I didn't know that was only added in Excel 97 (it's been a long time since I saw it/used it). I would double check that Round doesn't work because I don't recall it being new in Excel 2000/XP. If not, here's one possibility.

Sub TestOfRound()
Dim p As Double
p = 3.14159265358979

MsgBox WorksheetFunction.Round(p, 2)
End Sub

 

[RalphA]

...and, one can always fall back to that old BASIC method of rounding:

NumToRound = 123.4567
d = 2 'number of decimals desired
NumRounded = Int(NumToRound*10^d+0.5)/10^d

Success to you!

 

[Chitosunday]

I am using excel 97 and the application.worksheetfunction.round() works for me

 

[larsson7]

Hello and thankyou for your replies,

I have tried out the 'format' function specified above and it works perfectly!

I have also used the BASIC rounding technique, which seems to be more efficient than the round function anyway.

Once again, many thanks to you all.

Regards,

Cameron