 Search:

# Excel won't calculate my function

Asked By: Emily    Date: Oct 13    Category: MS Office    Views: 2649

I cannot get Excel to calculate my VBA function; I keep getting a #NUM
error. I think it may be that I have a wrong type somewhere, but I
don't know how strongly typed Excel is.

My code, as it appears in Module1, is:

Option Explicit

Public Function PUp(Wt As Double, FTL As Integer, _
PoI As Integer, stdev As Double) As Double
Dim erfVal As Double

PUp = 0

'If Wt > FTL Then
' PUp = 0
'Else
' erfVal = (Wt - PoI) / stdev * [atpvbaen.xls].sqrt(2)
' If erfVal < 0 Then
' PUp = 1 - 0.5 * (1 + [atpvbaen.xls].ERF(erfVal))
' Else
' PUp = 0.5 * (1 + [atpvbaen.xls].ERF(erfVal))
' End If
'End If
End Function

For reference: the ERF function is the CDF for a normal distribution,
with a mean of PoI; it comes with the Analysis ToolPak add-in.

=PUp(\$D2, FTL, PoI, 500*SQRT(2))
where
\$D2 = 22,100.00
FTL = 42000
PoI = 38500

Most of the code is commented out because I wanted to see if it was
something in the If loops that was screwing up my function;
apparently, it wasn't. Any help would be greatly appreciated.

Share:

The reason for your problem is the inappropriate use of Integer, restricted to
16 bit (=32767). In fact, two of your Integers exceed this value.

There were a couple other bugs in
there, but I got through them. I have a couple more
questions, things which bugged me but I got around
them anyway.

1) I had to replace WorksheetFunction.SQRT(2) with
2^(1/2), because SQRT was giving me a #VAL error. Any
idea why?

2) The only way I can get Excel to recalculate the
value of these cells after changing the code  is if I
go into the formula  bar for each cell and change
something. This is somewhat prohibitive because I
have 9,877 rows. Is there a way to get Excel to
calculate all these at once?

1. Sqrt is not one of the supported worksheet functions. For a list refer to:
msdn.microsoft.com/.../xl\
mscListOfWorksheetFunctions1_HV05202691.asp
2. About calculation - see: http://www.decisionmodels.com/calcsecretsh.htm

Didn't find what you were looking for? Find more on Excel won't calculate my function Or get search suggestion and latest updates.