Using sin and factorials in VBA... NEED HELP!!!

[pesh88]

Hi, I am of a low level of experience in VBA, and I am struggling with
trying to do something.

I would like to use the sin function within vba.

The function is:

sin (x) = x - (x^3/3!) +(x^5/5!)-(x^7/7!)+(x^9/9!)-...

I understand that there is in fact a sin function inbuilt in vba
"sin(x)" however I would like the function to follow the steps of the
function I have expressed above. The main trouble I am having concerns
the factorials. VBA does not seem to understand the "!", and I cannot
simply type out the whole factorial, as it makes it too long that vba
cant seem to hack it!

If this could be done to the most simplified level it would be most
helpful

I hope this can be dealt with.

 

[Karl E. Peterson]

The main trouble I am having concerns
the factorials. VBA does not seem to understand the "!", and I cannot
simply type out the whole factorial, as it makes it too long that vba
cant seem to hack it!

Factorials are the classic "recursive" example. You can calculate them like this:

Public Function Factorial(ByVal N As Long) As Variant
If N < 0 Then
' Undefined
ElseIf N = 1 Then
Factorial = CDec(1)
ElseIf N < 28 Then
Factorial = N * Factorial(N - 1)
Else
Err.Raise Number:=6 'overflow
End If
End Function

As you're surely aware, they get incredibly huge incredibly quick! By using the
Decimal subtype, we can calculate up to 27! without error. If you were to stick
strictly to Long values, you'd be limited to 12!

Public Function Factorial(ByVal N As Long) As Long
If N < 0 Then
' Undefined
ElseIf N = 1 Then
Factorial = 1
ElseIf N < 13 Then
Factorial = N * Factorial(N - 1)
Else
Err.Raise Number:=6 'overflow
End If
End Function

Test either with a simple loop like this:

For i = 1 To 30
Debug.Print i, Format$(Factorial(i), "#,##0")
Next i

 

[Karl E. Peterson]

As you're surely aware, they get incredibly huge incredibly quick! By using the
Decimal subtype, we can calculate up to 27! without error. If you were to stick
strictly to Long values, you'd be limited to 12!

Actually, if you can deal with the "imprecision", using a Double will buy you up to
170!

Public Function Factorial(ByVal N As Long) As Variant
If N < 0 Then
' Undefined
ElseIf N = 1 Then
' Double allows up to 170!
' Decimal allows up to 27!
' Long only allows up to 12!
Factorial = CDbl(1)
Else
Factorial = N * Factorial(N - 1)
End If
End Function

Fwiw...

170! =
7,257,415,615,307,990,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000

(Say, is this homework, by any chance?)

 

[pesh88]

thanks guys this is great help

factorials and vba dont seem to go well, but this has helped loads.

its not homework, but i was told to have a look making a sine function
within vba, and came up with the issue of factorials.

 

[Jonathan West]

Factorials are the classic "recursive" example. You can calculate them
like this:

Public Function Factorial(ByVal N As Long) As Variant
If N < 0 Then
' Undefined
ElseIf N = 1 Then
Factorial = CDec(1)
ElseIf N < 28 Then
Factorial = N * Factorial(N - 1)
Else
Err.Raise Number:=6 'overflow
End If
End Function

As you're surely aware, they get incredibly huge incredibly quick! By
using the Decimal subtype, we can calculate up to 27! without error. If
you were to stick strictly to Long values, you'd be limited to 12!

Public Function Factorial(ByVal N As Long) As Long
If N < 0 Then
' Undefined
ElseIf N = 1 Then
Factorial = 1
ElseIf N < 13 Then
Factorial = N * Factorial(N - 1)
Else
Err.Raise Number:=6 'overflow
End If
End Function

Test either with a simple loop like this:

For i = 1 To 30
Debug.Print i, Format$(Factorial(i), "#,##0")
Next i

Of course, while you *can* write Factorial as a recursive function, in
practice it usually executes much quicker if written as a loop

Public Function Factorial(ByVal N As Long) As Double
Dim f as Double
Dim i As Long
If N > 0 Then
f = 1
For i = 1 to N
f = f * i
Next i
Factorial = f
End If
End Function

(You would of course add overflow handling code to that)

 

[Karl E. Peterson]

Of course, while you *can* write Factorial as a recursive function,

It's a great introduction to the concept. Easy to follow.

in practice it usually executes much quicker if written as a loop

Public Function Factorial(ByVal N As Long) As Double
Dim f as Double
Dim i As Long
If N > 0 Then
f = 1
For i = 1 to N
f = f * i
Next i
Factorial = f
End If
End Function

(You would of course add overflow handling code to that)

You could also squeak out an extra quarter-nanosecond by altering that loop to:

For i = 2 To N

<gd&r>

 

[Karl E. Peterson]

factorials and vba dont seem to go well, but this has helped loads.

Now you have a couple of approaches.

its not homework, but i was told to have a look making a sine function
within vba, and came up with the issue of factorials.

Might I ask why? Your question actually caused me to consider the issue, as a bit
of a diversion, myself. The formula you posted, even with 25-30 elements in the
sequence, only provides a good estimation of the result returned by the native Sin()
function.

 

[Jonathan West]

It's a great introduction to the concept. Easy to follow.


Measurably so? <g>

In comparison to the time taken to access the Word object model, no. But if
you have a math-heavy program, then all the time savings available are worth
having. The disadvantage of a heavily-used recursive function are that every
recursion pushes and pulls stuff off the call stack, which is essentially
dead time. A loop avoids that.

For the purpose of creating a Sin function by hand, I would go one step
further. The 3!, 5! etc terms are constants. So I would calculate all the
necessary factorials *once*, and store them in an array of facorials. There
aren't so very many of them after all before the values overflow the limits
of a Double.

For a Sin(x) function, there are of course all sorts of other speed
optimisations you can make.

First of all, if x is outside the range -Pi to +Pi, then add or subtract
multiples of 2*Pi until it is. sin(x) is a periodic function, with a period
of 2*Pi, i.e. sin(x) always equals sin(x + 2*Pi). Dealing with smaller
values of X is a good idea since the terms of the function reduce to
insignificance much quicker, leaving you with less calculation to do

Then, when implementing the "sin (x) = x - (x^3/3!)
+(x^5/5!)-(x^7/7!)+(x^9/9!)-..." function, calculate and store each term and
check if it is so small as to be insignificant. If it is, all the subsequent
terms will be even smaller and therefore don't need to be calculated at all,
since factorials increase faster than power sequences. So you would do
something like this.

Function MySin(byVal x as Double) as Double
Dim dblTotal as Double
Dim dblTerm as Double
Dim iLoop As Long
Const Pi As Double = 3.14159265358979
Dim y as Long

'get x into the range -Pi to +Pi
If x < -Pi Then
y = Int((x + Pi) / (2 * Pi))
x = x + y * 2 * Pi
ElseIf x > Pi Then
y = Int((x + Pi) / (2 * Pi))
x = x - y * 2 * Pi
End If

'calculate the terms of the expansion
For iLoop = 1 to 120 Step 2
dblTerm = (x ^ iLoop) / Factorial(iLoop)
If dblTotal = dblTotal + dblTerm Then Exit For
dblTotal = dblTotal + dblTerm
Next iLoop

MySin = dblTotal

Exit Function

Note that the significance test was to see if dblTotal = dblTotal + dblTerm,
not to see if dblTerm = 0. Since we are dealing with floating-point math &
rounding errors, it is possible that dblTotal is sufficiently large compared
to dblTerm that even though dblTerm calculates to being non-zero, the
rounding errors are such that when added to dblTotal it actually makes no
difference!

These are the sorts of optimizations that you try to make when writing math
routines.

 

[Jonathan West]

Just realised that my earlier version of the function neglected to subtract
alternate terms in the series. It should be as follows.

Function MySin(ByVal x As Double) As Double
Dim dblTotal As Double
Dim dblTerm As Double
Dim iLoop As Long
Const Pi As Double = 3.14159265358979
Dim y As Long

'get x into the range -Pi to +Pi
If x < -Pi Then
y = Int((x + Pi) / (2 * Pi))
x = x + y * 2 * Pi
ElseIf x > Pi Then
y = Int((x + Pi) / (2 * Pi))
x = x - y * 2 * Pi
End If

'calculate the terms of the expansion
For iLoop = 1 To 120 Step 2
dblTerm = (x ^ iLoop) / Factorial(iLoop)
If dblTotal = dblTotal + dblTerm Then Exit For
If iLoop Mod 4 = 3 Then
dblTotal = dblTotal - dblTerm
Else
dblTotal = dblTotal + dblTerm
End If
Next iLoop
MySin = dblTotal

End Function

 

[Karl E. Peterson]

In comparison to the time taken to access the Word object model, no. But if
you have a math-heavy program, then all the time savings available are worth
having.

In that case, you'd simply want to precalculate an array of responses, eh?

Public Function Factorial(ByVal n As Long) As Double
Const Max As Long = 170 ' Limits of double-precision
Static Factorials(1 To Max) As Double
Dim i As Long
' Initialize array...
If Factorials(1) = 0 Then
Factorials(1) = 1
For i = 2 To Max
Factorials(i) = Factorials(i - 1) * i
Next i
End If
' Return result, given valid input.
If n >= 1 And n <= Max Then
Factorial = Factorials(n)
End If
End Function

The disadvantage of a heavily-used recursive function are that every
recursion pushes and pulls stuff off the call stack, which is essentially
dead time. A loop avoids that.
True.

For the purpose of creating a Sin function by hand, I would go one step
further. The 3!, 5! etc terms are constants. So I would calculate all the
necessary factorials *once*, and store them in an array of facorials.

For a Sin(x) function, there are of course all sorts of other speed
optimisations you can make.

First of all, if x is outside the range -Pi to +Pi, then add or subtract
multiples of 2*Pi until it is.

That's a good idea!

sin(x) is a periodic function, with a period
of 2*Pi, i.e. sin(x) always equals sin(x + 2*Pi). Dealing with smaller
values of X is a good idea since the terms of the function reduce to
insignificance much quicker, leaving you with less calculation to do

Then, when implementing the "sin (x) = x - (x^3/3!)
+(x^5/5!)-(x^7/7!)+(x^9/9!)-..." function, calculate and store each term and
check if it is so small as to be insignificant. If it is, all the subsequent
terms will be even smaller and therefore don't need to be calculated at all,
since factorials increase faster than power sequences.

I like that one, too.

So you would do something like this.

Here's my take on it, together with your ideas...

Public Function Sine(ByVal Radians As Double) As Double
Dim Result As Double
Dim This As Double
Dim Sign As Long
Dim n As Long

' Get x into the range -Pi to +Pi, by shifting
' by period multiples (2 * Pi)
Sign = 2 * Sgn(Radians)
Do While Abs(Radians) > Pi
Radians = Radians - (Sign * Pi)
Loop

' Set some starting values for loop.
Result = Radians
Sign = 1
n = 1

' sine(x) = x - (x^3)/3! + (x^5)/5! - (x^7)/7! + (x^9)/9! ...
Do
Sign = -Sign ' Subract every-other term
n = n + 2 ' 2n-1
This = Sign * ((Radians ^ n) / Factorial(n))
' Jump out of loop when results don't change.
If (Result + This) = Result Then Exit Do
Result = Result + This
Loop
Sine = Result
End Function

VB is clearly using a different algorithm, though, as this shows:

Public Sub test()
Dim i As Long
For i = 0 To 720 Step 5
Debug.Print D2R(i), Sin(D2R(i)), Sine(D2R(i)), _
Sin(D2R(i)) - Sine(D2R(i)), Sin(D2R(i)) = Sine(D2R(i))
Next i
End Sub

Public Function D2R(ByVal Degrees As Double) As Double
Const PiOver180 As Double = Pi / 180
D2R = Degrees * PiOver180
End Function

Ah well...