M
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
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