MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel won't calculate my function

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

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
' 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.

The Formula Bar reads:
=PUp($D2, FTL, PoI, 500*SQRT(2))
$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.



3 Answers Found

Answer #1    Answered By: Cais Nguyen     Answered On: Oct 13

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.
Use Long instead of Integer.

Answer #2    Answered By: Jaspreet Kapoor     Answered On: Oct 13

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?

Answer #3    Answered By: Elaine Stevens     Answered On: Oct 13

1. Sqrt is not one of the supported worksheet functions. For a list refer to:
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.