A very simple user defined function in vba with two arguments results in "there's a problem with this formula"

[SweMan]

I have the following code in a module in my excel macro enable excel file:

Public Function Test1(x)
    
Test1 = x

End Function



Public Function Test2(x, y)
    
Test2 = x - y

End Function

When I call the first function from excel =Test1(10) I get 10 in the cell

When I call the second function from excel =Test2(10,5) I get "there's is a problem with this formula"

I have been trying to figure out what the problem is for hours now.

 

[Johnny C]

Which version of Excel are you using?

I copied your functions and they work fine in Excel 2013

Are you sure you don't have another function somewhere else called test2? That might cause confusion if it has a different number of parameters

 

[SweMan]

I use Microsoft Office 365 version 1707 (Build 8326.2076) so it is excel 2016. No its the only function called test2 in the module and in all my excel files (I think). I have other Public Sub that uses a variable defined a x but I don't think that is the problem because even if I change the argument in the very simple function to a different name it does not solve the problem. I glad you could run the function though indicating it is possible.

 

[SweMan]

Ok I have solved the problem after many hours �� ���� I would open a bottle of champagne if I had one lol.

You have to call the user defined function as =Test2(10;5) from within excel

 

[RoryA]

That's to do with your regional settings. You use the same separator as you do with any built-in function. (I use commas for both as I have UK settings, as would anyone with US settings).

 

[SweMan]

I hate regional settings so much because they can produce a lot of headache! I wish I could just get the default version of excel. Regional setting appears to be hard to get rid of.

 

[RoryA]

You can change your regional settings to anything you like, but it will affect every program on your computer.