Visual Basic incompatibility with the CHR function between MAC Excel and Windows Excel.

Question

I am writing a Visual Basic Excel Application that needs to run in both Windows 7 and Mac OS 10.

The program runs fine on Windows. I am stymied by one error. The CHR function is not recognized in the Mac.

I have tried CHR, CHAR with no avail. I tried it in two different Macs.

Please advise what I can do.

• indra

Answer 1

CHR definitely works in VBA in Mac.

CHAR also should work in a worksheet.

Both are 100% cross-platform compatible.

Try this simple function:

Sub test()

MsgBox Chr(97)

End Sub

Let us know if this macro fails.

Regards,

Edwin Tam

Excel Power Expander - http://www.vonixx.com

Answer 2

I have seen this before, and unfortunately it has nothig whatsoever to do with the Chris function. There is another issue in vb that is causing this. Are all references resolved? Does it compile without errors, even with the chr lines commented out?

VBA on the Mac is identical to vb on Windows with regard to variable declaration. You must have an option explicit at the beginning of the module that you have not found. And it has nothing to do with VBA "seeing" you use dimensioned arrays, it doesn't do this or care.

fsundamental stuff like char(10) is indeed the same, this is an ascii line feed character. However Mac and pc threat line feeds (10) and carriage returns (13) differently.

Answer 3

I have a fairly elaborate Excel VBA code that has the same problem with Chr() on the Mac version.  The message says "Can't find project or library."  

On the other hand, if I create a simple routine on a separate sheet, such as 

     Sub Test()

        Msgbox "a" & Chr(10) & "a"

     End Sub

it works fine.  (It was Chr(10) that didn't work.  If it were just a character, I'd use quotes and move on.) If I were guessing, I'd say that VBA has seen me using dimensioned arrays and thinks this is one.  BTW, VBA on the Mac insists that I dimension all variables, while, of course, Windows does not.

I understand that the Mac does not use the same character set as Windows, but I'm hoping the fundamental stuff like 10 is the same.

Answer 4

As i said, CHR is a VB function and will NOT work in a worksheet. CHAR is an Excel worksheet function and will NOT work in VB. The only way to use CHAR in VB is to use Application.char.

Please be more specific about the code you are using and what does not work. List the code here. List any error messages. If there are no errors describe in detail what "doesn't work" and what you think should happen.