Using excel built-in functions with a variable

Question

I am an excel noob trying to make a custom excel function that uses degrees while calculating sin of an angle.

Public Function SIND(number As Double)
Formula = "SIN(RADIANS(number))"
Formula = Replace(Formula, "number", number)
SIND = Evaluate(Formula)
End Function

So far I have this but it doesn't work

It just gives an error: #VALUE! :A value used in the formula is of wrong data type. The cell works with regular SIN btw. — ertucode, May 15 '22 at 17:21
I tested your code and it gave me a number... maybe "it's not working" because of what you are doing with the function? https://i.stack.imgur.com/EPdan.png — braX, May 15 '22 at 17:21
It does work with =SIND(2), but when I divide by a number, it doesnt work: =SIND(2/365) — ertucode, May 15 '22 at 17:23
Didn't work for me.. [pic](https://www.hizliresim.com/l6yvnld) — ertucode, May 15 '22 at 17:34
regional settings? works for me... https://i.stack.imgur.com/mcUT0.png — braX, May 15 '22 at 17:41
What does your debug/immediate window show? (like my first 2 screenshots) — braX, May 15 '22 at 17:45
Maybe the scientific notation? Try changing this: `Formula = Replace(Formula, "number", Round(number, 8))` — braX, May 15 '22 at 17:59
That did not make a difference. https://www.hizliresim.com/2w1be9z — ertucode, May 15 '22 at 18:05
Your debug is using a comma, not a period. In VBA Code, you have to use a period even if your regional settings are set otherwise. — braX, May 15 '22 at 18:08
I need to probably change some setting somewhere but I can not figure it out. Even this does not work https://www.hizliresim.com/bylhxx0 . — ertucode, May 15 '22 at 18:09
Using a dot results in a date, and normal SIN works with commas. So, I think I'll ask excel support to change this behaviour. — ertucode, May 15 '22 at 18:12
Is there a reason you need to use `Evaluate` to do this? It's not necessary. It's just over-complicating things. — braX, May 15 '22 at 18:15
No, I just don't know excel. If you have a better way of doing this, I would happily use that one. — ertucode, May 15 '22 at 18:18
I'm guessing that your settings have Excel using a comma in scientific notation (instead of a period), and Evaluate does not know how to handle that. — braX, May 15 '22 at 18:41

braX · Accepted Answer · 2022-05-15T18:53:21.413

2

Here's a better way:

Public Function SIND(degrees As Double) As Double
  Dim Rads As Double
  Rads = WorksheetFunction.Radians(degrees)
  SIND = Sin(Rads)
End Function

The main problem with your way is that you were mixing string functions to do math calculations, and that's just not the best method.

edited May 15 '22 at 18:53

answered May 15 '22 at 18:19

braX

11,506
5
20
33

Using excel built-in functions with a variable

1 Answers1