Problem converting Excel application from PC to Mac

[greg]

I have an application which has been written in Excel 2000 (SP3) on a PC and
works ok. I have been asked to make it run on a Mac. When I tried this, it
fails in a big way.



The application uses a combination of excel sheets with embedded VB controls
and user forms created with VBA. There seem to be at least three problem
areas...



1) Any user form that contains an image box does not translate from PC to
Mac correctly



2) The controls embedded on the excel worksheets no longer appear to be
controls - they appear to have been converted to graphics (backed up by the
Mac spending a great deal time converting metafiles when the file is
opened). The controls were added using the VB toolbar in Windows Excel,
which does not seem to exist on the Mac.



3) Certain functions do not work - i.e. Round()



So the question is; how can I get this to work on a Mac without resorting to
a major rewrite.



Thanks,



Greg

 

[Bob Greenblatt]

I have an application which has been written in Excel 2000 (SP3) on a PC and
works ok. I have been asked to make it run on a Mac. When I tried this, it
fails in a big way.



The application uses a combination of excel sheets with embedded VB controls
and user forms created with VBA. There seem to be at least three problem
areas...



1) Any user form that contains an image box does not translate from PC to
Mac correctly



2) The controls embedded on the excel worksheets no longer appear to be
controls - they appear to have been converted to graphics (backed up by the
Mac spending a great deal time converting metafiles when the file is
opened). The controls were added using the VB toolbar in Windows Excel,
which does not seem to exist on the Mac.



3) Certain functions do not work - i.e. Round()



So the question is; how can I get this to work on a Mac without resorting to
a major rewrite.



Thanks,



Greg

Greg,

Sorry, but you'll have to do a major rewrite. The problem is that the
Windows application use Active X controls which do not exist on a Mac.
You'll have to change the controls to those found on the forms toolbar. I do
not know why round does not work - it should. What is happening incorrectly?

 

[J.E. McGimpsey]

3) Certain functions do not work - i.e. Round()

Bob's given you the scoop on ActiveX controls. VBA's Round() was
introduced in VBA6. All Mac VBA versions are 5.0 (equivalent to
XL97's VBA version).

You can use Application.Round() instead, but note that VBA's Round()
function uses what MS calls "Banker's Rounding" (BR) while XL's
ROUND() function uses what MS calls Symmetric Arithmetic Rounding
(SAR). The only difference is in how a last digit of 5 is rounded.
In SAR, the number is always rounded away from zero:

?Application.Round(1.5,0) ==> 2
?Application.Round(2.5,0) ==> 3
?Application.Round(3.5, 0) ==> 4
?Application.Round(-1.5,0) ==> -2
?Application.Round(-2.5,0) ==> -3
?Application.Round(-3.5, 0) ==> -4

while in BR, the number is rounded to the nearest EVEN digit:

?Round(1.5,0) ==> 2
?Round(2.5,0) ==> 2
?Round(3.5, 0) ==> 4
?Round(-1.5,0) ==> -2
?Round(-2.5,0) ==> -2
?Round(-3.5,0) ==> -4

From a mathematics and statistics point of view, BR is sometimes
superior, and never inferior, to SAR but SAR is what most people
learned in school (unless they did upper level science or
mathematics) and are familiar with.

What I do is roll my own BR Round() function for cross-platform
applications and use conditional compilation to use VBA's Round()
function for Win VBA6+ platforms and my Round() function for others.

You can find several VBA5 compatible Rounding routines at

http://support.microsoft.com/default.aspx?scid=kb;EN-US;196652

 

[Jim Gordon]

Hi Greg,

Bob & J.E. filled you in on 2 out of 3 of the big things you need to know.

Here's the 3rd of 3...

If you put images onto userforms you need completely separate versions of
your macros. Internally, VB hard codes a file path to the picture. Since
file path separators are different on Mac and Windows and since VB does not
account for the difference, userforms with pictures made on a Mac can't be
opened in Windows and vice-versa.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

 

[Bob Greenblatt]

Bob's given you the scoop on ActiveX controls. VBA's Round() was
introduced in VBA6. All Mac VBA versions are 5.0 (equivalent to
XL97's VBA version).

You can use Application.Round() instead, but note that VBA's Round()
function uses what MS calls "Banker's Rounding" (BR) while XL's
ROUND() function uses what MS calls Symmetric Arithmetic Rounding
(SAR). The only difference is in how a last digit of 5 is rounded.
In SAR, the number is always rounded away from zero:

?Application.Round(1.5,0) ==> 2
?Application.Round(2.5,0) ==> 3
?Application.Round(3.5, 0) ==> 4
?Application.Round(-1.5,0) ==> -2
?Application.Round(-2.5,0) ==> -3
?Application.Round(-3.5, 0) ==> -4

while in BR, the number is rounded to the nearest EVEN digit:

?Round(1.5,0) ==> 2
?Round(2.5,0) ==> 2
?Round(3.5, 0) ==> 4
?Round(-1.5,0) ==> -2
?Round(-2.5,0) ==> -2
?Round(-3.5,0) ==> -4

From a mathematics and statistics point of view, BR is sometimes
superior, and never inferior, to SAR but SAR is what most people
learned in school (unless they did upper level science or
mathematics) and are familiar with.

What I do is roll my own BR Round() function for cross-platform
applications and use conditional compilation to use VBA's Round()
function for Win VBA6+ platforms and my Round() function for others.

You can find several VBA5 compatible Rounding routines at

http://support.microsoft.com/default.aspx?scid=kb;EN-US;196652

Thanks for the great scoop on round. Everyone (especially me) always learns
something from hanging out on these forums.

 

[Nigel King]

Thanks for all these answers. The application now works on both plaforms
after a lot of fiddling.

Nigel and Greg