Howdy folks,

Sorry about the Newbie question here, but I could not find anything
about this in my web and groups searches.

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

- Paul

1. It has to be in a regular module, not a sheet module
2. You may have to use

application.volatile

as the first line


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Paul Schrum" <(E-Mail Removed)> wrote in message
news:b0949730-2ad8-4415-99a3-(E-Mail Removed)...
> Howdy folks,
>
> Sorry about the Newbie question here, but I could not find anything
> about this in my web and groups searches.
>
> 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
>
> - Paul

Sometimes you can get that Name error if there is any ambiguity with the
function name, eg the name exists twice in the same project, or there's a
similar defined name.

Try renaming it to something definitely unique, not forgetting to do
new-name = return-value. Then Replace ctrl-h old-name with new-name in all
cell formulas.

Regards,
Peter T


"Paul Schrum" <(E-Mail Removed)> wrote in message
news:b0949730-2ad8-4415-99a3-(E-Mail Removed)...
> Howdy folks,
>
> Sorry about the Newbie question here, but I could not find anything
> about this in my web and groups searches.
>
> 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
>
> - Paul

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" <paul.sch...@gmail.com> wrote in message
> news:b0949730-2ad8-4415-99a3-(E-Mail Removed)...
> > 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
>

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" <(E-Mail Removed)> wrote in message
news:4325d7d2-47e5-4311-9812-(E-Mail Removed)...
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" <paul.sch...@gmail.com> wrote in message
> news:b0949730-2ad8-4415-99a3-(E-Mail Removed)...
> > 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
>

Peter,

I the original #NAME error resolved? I think it is, but that is just
because I have not seen it lately. I am not sure what I might have
done to make it stop doing that.

The solution you give below has solved my current problem. Thanks for
your help.

Sometimes my problems with this function have been different after a
reboot, which I have not taken the time to try yet, so I can't promise
I won't be back with another twist on the same problem. But for now,
it looks good.

- Paul

On Jun 25, 12:03 pm, "Peter T" <peter_t@discussions> wrote:
> 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" <paul.sch...@gmail.com> wrote in message
>
> news:4325d7d2-47e5-4311-9812-(E-Mail Removed)...
> 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
>

Hello Everyone,

The help I received last month in this thread got me going. I am
resurrecting the thread now because I want to do something different
now, and again I can't figure out how to do it.

What I want to do different is store values in one .xls file and use
the values in a different .xls file. I will give the user a way to
indicate the path and filename of the .xls file to open. Different
data (roadway profiles) will be stored on different worksheets of the
same format, so the user will pass in the worksheet name for the
function to reference.

My problem is, when I do what I guess I ought to do, something happens
and the watch box says everything is out of context. Also, the debug
mode of VBA stops showing the current line of code with a yellow
background. Here is the key new code:

Function computeEL2(profileName As String, station As Double) As
Double
Application.Volatile
' ... snip ...

profilesFile = "Profiles.xls" ' Breakpoint is set
here. Executes okay
Workbooks.Open Filename:=profilesFile ' Executes okay
Set wb = Workbooks(profilesFile) ' Problem happens when I
execute this line
'Set ws = Application.Caller.Parent
Set ws = wb.Sheets(profileName)

I suppose the problem line is working, but that is changing the active
workbook, hence the problem. Can someone advise me on this? I think
my real question is, how do I read and use values in cells in a
different workbook?

Thanks in advance.

- Paul

On Jun 25, 12:03*pm, "Peter T" <peter_t@discussions> wrote:
> 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

I have more information which may be of help.

When I am not in debug mode, the display of the cell that calls the
function macro is #VALUE.

I added a line to the code such that it reads

count = Workbooks.count ' count = 2
Workbooks.Open Filename:=profilesFile, ReadOnly:=True
count = Workbooks.count ' count still = 2

Perhaps this means that the file -> open is failing, but I can't
figure out how to get the status of the file -> open operation.

- Paul

I'm not really following all this but it looks like you want a udf to
populate cells on another sheet, the name of which in turn is passed as an
argument to the UDF.

If that's broadly what you have in mind let me stop you right there. A UDF
can only return a value to the cell formula from which it was called. It
cannot change the interface in any way, which includes writing to some other
cell or changing any of its properties.

Regards,
Peter T

"Paul Schrum" <(E-Mail Removed)> wrote in message
news:9f8785a5-53ab-49a1-8df7-(E-Mail Removed)...
Hello Everyone,

The help I received last month in this thread got me going. I am
resurrecting the thread now because I want to do something different
now, and again I can't figure out how to do it.

What I want to do different is store values in one .xls file and use
the values in a different .xls file. I will give the user a way to
indicate the path and filename of the .xls file to open. Different
data (roadway profiles) will be stored on different worksheets of the
same format, so the user will pass in the worksheet name for the
function to reference.

My problem is, when I do what I guess I ought to do, something happens
and the watch box says everything is out of context. Also, the debug
mode of VBA stops showing the current line of code with a yellow
background. Here is the key new code:

Function computeEL2(profileName As String, station As Double) As
Double
Application.Volatile
' ... snip ...

profilesFile = "Profiles.xls" ' Breakpoint is set
here. Executes okay
Workbooks.Open Filename:=profilesFile ' Executes okay
Set wb = Workbooks(profilesFile) ' Problem happens when I
execute this line
'Set ws = Application.Caller.Parent
Set ws = wb.Sheets(profileName)

I suppose the problem line is working, but that is changing the active
workbook, hence the problem. Can someone advise me on this? I think
my real question is, how do I read and use values in cells in a
different workbook?

Thanks in advance.

- Paul

On Jun 25, 12:03 pm, "Peter T" <peter_t@discussions> wrote:
> 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

Peter,

Thanks for your response.

I think my description was a little rushed, and therefore not
completely clear. I am working on a udf to populate the cell it is
called from via the return value of the function (a double).

Where I would like to have access to another XL file is in reaading
the contents of the other file as part of performing the calculations.

- Paul

On Jul 25, 2:47*am, "Peter T" <peter_t@discussions> wrote:
> I'm not really following all this but it looks like you want a udf to
> populate cells on another sheet, the name of which in turn is passed as an
> argument to the UDF.
>
> If that's broadly what you have in mind let me stop you right there. A UDF
> can only return a value to the cell formula from which it was called. It
> cannot change the interface in any way, which includes writing to some other
> cell or changing any of its properties.
>
> Regards,
> Peter T
>