Getting #VALUE in cells with User Defined Functions

[MikeZz]

Hi,
I have a User Defined Function in a module for a workbook that is referenced
in some cells.

Occasionally, I get the #VALUE error even when I have Auto Calc On and the
error doesn't even clear when I do F9 Update Calculations.

The only way I can get it to calculate is if I click on a cell Formula Bar
that is referenced in the formula and hit "enter". This seems to trigger a
refresh or calculation.

For example:
UDF "Get_Range_Add" is called in cell C2,
A range named "RangeName" points to cell $A$4 on Sheet1
The result in C2 should say "$A$4" which is the address of the range called
"RangeName" found on "Sheet1".

However, it says "#VALUE" unless I click somehwere in either cell B1 or A2
Formula Bar, then press "Enter". Then all formulas that look at linked the
cell I just clicked on get refreshed. Again, it only calculates if Excel
thinks I actually modified one of those cells.

Thanks for any help,
MikeZz

A B C D
1 "Sheet1"
2 "RangeName" =Get_Range_Add(G6,F7)
3



Here is my User Defined Function:

Function Get_Range_Add(shtName As String, rngName As String)
Dim add As String

add = Sheets(shtName).Range(rngName).Address
Get_Range_Add = add

End Function

 

[Niek Otten]

Please supply the code of the UDF and the way it is called.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
| I have a User Defined Function in a module for a workbook that is referenced
| in some cells.
|
| Occasionally, I get the #VALUE error even when I have Auto Calc On and the
| error doesn't even clear when I do F9 Update Calculations.
|
| The only way I can get it to calculate is if I click on a cell Formula Bar
| that is referenced in the formula and hit "enter". This seems to trigger a
| refresh or calculation.
|
| For example:
| UDF "Get_Range_Add" is called in cell C2,
| A range named "RangeName" points to cell $A$4 on Sheet1
| The result in C2 should say "$A$4" which is the address of the range called
| "RangeName" found on "Sheet1".
|
| However, it says "#VALUE" unless I click somehwere in either cell B1 or A2
| Formula Bar, then press "Enter". Then all formulas that look at linked the
| cell I just clicked on get refreshed. Again, it only calculates if Excel
| thinks I actually modified one of those cells.
|
| Thanks for any help,
| MikeZz
|
| A B C D
| 1 "Sheet1"
| 2 "RangeName" =Get_Range_Add(G6,F7)
| 3
|
|
|
| Here is my User Defined Function:
|
| Function Get_Range_Add(shtName As String, rngName As String)
| Dim add As String
|
| add = Sheets(shtName).Range(rngName).Address
| Get_Range_Add = add
|
| End Function
|

 

[MikeZz]

The UDF is in my original post....

Here is my User Defined Function:
| Function Get_Range_Add(shtName As String, rngName As String)
| Dim add As String
|
| add = Sheets(shtName).Range(rngName).Address
| Get_Range_Add = add
|
| End Function

It's called the following way:
where cell G6 has the name of a sheet and F7 has the name of the range.

=Get_Range_Add(G6,F7)