Excel VBA From PC Excel 2010 not working in Mac Excel 2011

[karate718]

I am pretty new to VBA and I am trying to run a script written by someone else on their PC running excel 2010 on my mac running excel 2011.

The line creating the error is: x = Application.WorksheetFunction.Norm_S_Dist(d1, True). The error received is Compile error: Method or data member not found

I have tried running it as x = Application.WorksheetFunction.Norm.S.Dist(d1, True) as it would be called if typing it into a cell in a worksheet but this gives the same error.

The last thing I tried is: x = Application.WorksheetFunction.NormSDist(d1, True). The error received is Compile error: Wrong number of arguments or invalid property assignment.

Any idea how to get this command to work on a mac? I am not sure if it matters, but the mac is running OS X, 10.8.4.

Thank you very much for your help.

 

[mole999]

I have been told Mac Excel, dosen't support ActiveX etc from PC EXcel, I don't know what can and cannot work

 

[RoryA]

Unfortunately the newer worksheet function (NORM.S.DIST) doesn't appear to be exposed in Mac VBA and the older one (NORMSDIST) only takes one argument, hence the error you get with your last version.

 

[karate718]

Unfortunately the newer worksheet function (NORM.S.DIST) doesn't appear to be exposed in Mac VBA and the older one (NORMSDIST) only takes one argument, hence the error you get with your last version.

Thanks Rory. I will find another solution since I am not able to code this myself as my knowledge of VBA is basic at this point.

 

[mole999]

I found this that says Mac excel 2011

NORM.S.DIST function - mac excel - Office.com

 

[karate718]

I found this that says Mac excel 2011

NORM.S.DIST function - mac excel - Office.com

Yes, From what I understand, this doesn't mean that it will work in a VBA script. I would have to calculate it in a cell somewhere else and then import it.

 

[Jim Gordon]

I experimented and confirm that NormSDist is not working in VBA in XL 2011. The following code runs on PC XL

Sub test2()
Dim x
x = Application.WorksheetFunction.Norm_S_Dist(d1, True)
MsgBox x
End Sub

However, it fails to run in XL 2011. I just reported this to Microsoft as a bug.

 

[mole999]

I thought it came with dots and not underscore

 

[RoryA]

Dots would confuse the compiler so they are replaced in VBA.

Thanks for reporting that Jim - I completely forgot!