Is the original #NAME error resolved then.

In the snippet of code below each use of Range(address) will refer to
cell(s) on the active sheet. If you always want to refer to cells on the
same sheet as the formula cell that called the UDF try something like this -

Function computeEL(station As Double) As Double
Dim ws As Worksheet
Set ws = Application.Caller.Parent
' code

x = ws.Range("A1").Value

In passing, instead of -
sta1 = ws.Range(staColumn & aRow - 1).Value

consider doing -
sta1 = ws.Cells(lngRow - 1, lngColumn).Value

Regards,
Peter T




"Paul Schrum" wrote in message
...
Thanks to both Don Guillett and Peter T. After a few days delay, I
have more information.

I always have multiple spreadsheets opened simultaneously. (One is a
time sheet spreadsheet.)

It looks like the VBA function does not always refer to the right
spreadsheet. Here is a simplified version of the function to help me
explain:

Function computeEL(station As Double) As Double
Application.Volatile
Dim firstRow As Integer
.. . .
Dim el1 As Double, el2 As Double

computeEL = -999#

firstRow = 5
aRow = firstRow
staColumn = "B"
ELcolumn = "D"
VClengthColumn = "F"

count = 0
sta1 = Range(staColumn & aRow).Value
While sta1 0#
aRow = aRow + 1
count = count + 1
sta1 = Range(staColumn & aRow - 1).Value

VClen = Range(VClengthColumn & aRow).Value

sta2 = Range(staColumn & aRow).Value
begVC = sta2 - (VClen / 2#)
endVC = begVC + VClen

If station = sta2 And VClen = 0 Then
[snip]


When I switch to my time sheet spread sheet to update my time, then
switch back to GoreGrades.xls, now the cells with the custom function
all show values of -999.0. It looks like I am refering to the range
which has the data I need is actually the range in the sheet that I
last typed in or last entered data into. So when the range for, say,
sta2 is "B5", it is looking in the B5 of a different spreadsheet.

If this hunch is correct, what is the best way for me to tell it
always to look in the spreadsheet that the VBA module is a part of and
look on the worksheet from which the function was called?

- Paul

"Paul Schrum" wrote in message
...
I am using Excel 2003. I am familiar with VBA programming, but I am
new to VBA programming in Excel.

A few days ago I developed a non-trivial custom function in VBA. I
got it to work and everything was fine. I closed the file, went home,
and slept pretty okay.

The next day I wanted to work in the spreadsheet more. But when I
opened it, all the cells which call my custom function display #NAME.
These same cells worked fine on the same session in which I developed
the function. Can someone tell me what I must do to get the custom
function to work seamlessly -- as if it were just another function
available to all cells in that work book?

TIA