Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

The Formula Bar reads:

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

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.

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.

Related Topics:

- help requested for using excel's built-in function in coding
- user-designed Excel math functions
- description/help with own VBA functions in Excel
- Excel User Functions
- Excel Function
- Create Excel Function with Visual Basic
- ABS function in Excel
- VBA Function in Excel 2000
- Accessing help file info for non-Excel 2007 functions from VBA
- Excel Function
- VBA Excel Coding for dynamically changing function range
- Wait function and Invisible function
- Wait function and Invisible function
- write a program that enters double data into an ArrayList and calculate the total
- How to calculate IRR
- Calculate Total Sum
- Easier method to capture & paste calculated value?
- File formats ( Excel 2003 & Excel 97 ) ?
- Copy Excel Object Within Word to Excel
- Calculating the no. of containers
- Calculating duration
- pasting word table to Excel but needing to match excel
- Convert string like "=123+456+789" to Long and then calculate
- Excel User Form and Active Excel Work Sheets Question
- Application.Calculate fails