Trig Functions in VBA

[Rumpkin]

Good Evening all.
I have a formula I use and would like to make it a User Defined Function (UDF)
I have replace the dell references with actual numbers for clarity.
The worksheet formula is:
=1/(SIN(RADIANS(DEGREES(ATAN(3/1)))))*12.5*20

The code I tried to write is:

Function SLOPES(VerticleSlopeLeg, HorzSlopeLeg, HorzDist, Width) 'To Find Area of Slope
SLOPES = 1 / (Application.WorksheetFunction.Sin * (Application.WorksheetFunction.Radians(Atn(VerticleSlopeLeg / HorzSlopeLeg)))) * HorzDist * Width
End Function
Will you why it will not work?

 

[Rick Rothstein]

I have a formula I use and would like to make it a User Defined Function (UDF)
I have replace the dell references with actual numbers for clarity.
The worksheet formula is:
=1/(SIN(RADIANS(DEGREES(ATAN(3/1)))))*12.5*20

Give this UDF a try...

[table="width: 500"]
[tr]
	[td]Function SLOPES(VerticleSlopeLeg, HorzSlopeLeg, HorzDist, Width) As Double
  SLOPES = HorzDist * Width / Sin(Atn(VerticleSlopeLeg / HorzSlopeLeg))
End Function[/td]
[/tr]
[/table]

By the way, your "formula" can be simplified somewhat like this...

=12.5*20/SIN(ATAN(3/1))

The ATAN already returns a radian value, so there is no need to convert it to degrees only to later convert it back to radians. Since you are multiply 12.5*20 times the reciprocal value, you can do that multiplication on either side... putting it on the left lets you multiply it by 1 so that, in effect, the 1 goes away. Moving it also lets you remove a set of parentheses which turn out to no longer be needed.

 

[LockeGarmin]

The Excel.Worksheet object doesn't have a method called 'Sin', however that's probably because the VBA library does. Since this is in VBA I demonstrated how you can split your UDF into parts instead of trying to do it all in one line like in Excel. I probably wouldn't normally go as granular as doing the multiplication on two separate lines but thought the example might help make the point.

Function SLOPES(VerticleSlopeLeg, HorzSlopeLeg, HorzDist, Width) 'To Find Area of Slope

Dim d As Double

  d = Atn(VerticleSlopeLeg / HorzSlopeLeg)
  d = Excel.WorksheetFunction.Degrees(d)
  d = Excel.WorksheetFunction.Radians(d)
  d = Sin(d)
  d = 1 / d
  d = d * HorzDist
  d = d * Width
  
  SLOPES = d

End Function

 

[Rick Rothstein]

The Excel.Worksheet object doesn't have a method called 'Sin', however that's probably because the VBA library does. Since this is in VBA I demonstrated how you can split your UDF into parts instead of trying to do it all in one line like in Excel. I probably wouldn't normally go as granular as doing the multiplication on two separate lines but thought the example might help make the point.

Function SLOPES(VerticleSlopeLeg, HorzSlopeLeg, HorzDist, Width) 'To Find Area of Slope

Dim d As Double

  d = Atn(VerticleSlopeLeg / HorzSlopeLeg)
[B][COLOR="#FF0000"]  d = Excel.WorksheetFunction.Degrees(d)
  d = Excel.WorksheetFunction.Radians(d)
[/COLOR][/B]  d = Sin(d)
  d = 1 / d
  d = d * HorzDist
  d = d * Width
  
  SLOPES = d

End Function

The red highlighted lines of code are unnecessary (see explanation in Message #2 ).

 

[LockeGarmin]

The red highlighted lines of code are unnecessary (see explanation in Message #2 ).

Yes, I saw your message when it was just the formula before you edited in your explanation of it being unnecessary. I still decided not to edit out those lines in my post since it still had the right answer and it closely aligned with the OP's formula as well as showed how to break it down into procedural programming in VBA.

 

[Rumpkin]

Bingo!! - Rick

Thanks and thanks for cleaning up the formula.
“There is a Difference
Between Right & Wrong So
Choose Wisely” CF