Using a VBA function in a cell

[Jeffrey Smith]

Is it possible to use a VBA function in a cell like this:

=QPTan2x(123,301.76) < entered into a cell

In VBA:

Function QPTan2X(X!, Y!)

Dim Tmp!, PI As Double
PI = Atn(1) * 4

If X! = 0! Then
Tmp! = Sgn(Y!) * (PI / 2!)
ElseIf X! > 0! Then
Tmp! = Atn(Y! / X!)
ElseIf Y! >= 0! Then
Tmp! = PI + Atn(Y! / X!)
Else
Tmp! = -PI + Atn(Y! / X!)
End If
Tmp! = 180 * Tmp! / PI

If Tmp! < 0! Then Tmp! = Tmp! + 360!

QPATAN2x = Tmp!

End Function

 

[Joe4]

Sure, you just need to fix the typo in the last line of your code:

QPATAN2x = Tmp!
should be
QPTan2x = Tmp!

It gave me a result of 67.82376

 

[Jeffrey Smith]

Thanks, I didn't know it was case sensitive.

 

[Joe4]

It isn't a matter of Case Sensitivity, you have an extra "A" in there.

Developer Tip:
In the VB Editor under Tools | Options | Editor, click the "Require Variable Declaration" box.

Then every time you start a new macro, the words Option Explicit will appear at the top, and whenever you try to run a macro, if it finds any undeclared variables, it will tell you. That would have caught this typo.