Can a UDF access the value in a named worksheet cell?

[JenniferMurphy]

Suppose in Sheet1, I have given the cell C4 the name "MaxValue" and MaxValue (C4) contains the value "100".

Is there a way that a UDF called from any cell in Sheet1 can access the value in C4 using the name MaxValue without passing it as an argument?

I am working on a UDF that needs a bunch of values (8-10 and growing). Passing them as parameters is getting tedious and error-prone as they can easily get out of order. I would prefer that the UDF be able to access the values in the named cells using the cell names.

This is what I tried, but it gets a Value error.

Public Function MyFun()
  . . .
Const MaxValue as String = "MaxValue"   'Name of cell in Sheet1
Dim MaxVal as Integer
MaxVal = Range(MaxValue).Value
  . . .
End Function

Thanks

 

[Marcelo Branco]

I don't understand what you mean by "monitor". Does a UDF monitor the value of a passed parameter?



I don't understand what you mean by "refreshed".

When a UDF is called, control remain with the UDF until it exits. However a value gets into a UDF, whether passed as a parameter or extracted by the UDF from the sheet, once it has it, it will not change until the next call, no?

I think what pgc is trying to say is

Suppose you have these two functions (notice in the second function the named range is passed as an argument)

Function FuncV1()
    FuncV1 = Range("MaxValue").Value
End Function

Function FuncV2(r As Range)
    FuncV2 = r.Value
End Function

Excel

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

MaxValue​

[/TD]
[TD][/TD]
[TD]

FuncV1​

[/TD]
[TD]

FuncV2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[/TR]
</tbody>[/TABLE]


Range A2 named as MaxValue

Formula in C2
=FuncV1()

Formula in D2
=FuncV2(MaxValue)

Then change the value in A2 to 200. You see...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

MaxValue​

[/TD]
[TD][/TD]
[TD]

FuncV1​

[/TD]
[TD]

FuncV2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD]

200​

[/TD]
[/TR]
</tbody>[/TABLE]


The value in C2 remains (???) - this means the function doesn't monitor the named range.

To get the proper value you need to re-enter the formula in C2 (refresh)

Hope i made myself clear

M.

 

[pgc01]

Hi

I see that Marcelo has already explained it, but since I had finished writing this maybe it will complement the help.

I don't understand what you mean by "monitor". Does a UDF monitor the value of a passed parameter?

Yes, or, more exactly, excel does.

When you use a formula, for ex. C1 = SUM(A1, B1), you expect that when you change A1 or B1 the formula in C1 is reevaluated and the value in C1 is updated.
That's the point in excel, you write formulas and when any of its precedents changes value the formula value is updated.
When you define a udf with a parameter, like

Public Function MyFun(lMaxValue as Long) as Long

You'll use in a cell something like =MyFun(A1) and excel knows that if A1 changes value this formula should be reevaluated

Let's say now that you use

Public Function MyFun() as Long

and you use the value of A1 in the code of the udf.

You'll use in a cell something like =MyFun() and if you change the value of A1 excel will not know that the formula should be reevaluated.

This means that the only way you have to be sure that a formula that uses a udf like this one is displaying the correct result is by refreshing it, meaning recalculating the worksheet or the workbook, or confirming the formula again.

This is why I said that it is usually considered bad practice to use an external value in a udf that is not included in the parameters. You cannot trust the result unless after a recaclulation.

In this specific case, you are using the named range MaxValueName reading it inside your code, not passing it as a parameter.
This means that if you change it's value all the formulas that use the udf may display an incorrect value until they are recalculated.

Hope it helps.

 

[JenniferMurphy]

I think what pgc is trying to say is

. . .

The value in C2 remains (???) - this means the function doesn't monitor the named range.

To get the proper value you need to re-enter the formula in C2 (refresh)

Hope i made myself clear

M.

OK, now I get it, thanks. I tested it and it works as you describe. However, I added an Application.Volatile statement to the UDF and now it does update automatically.

Any problems with that?

 

[JenniferMurphy]

Hi

I see that Marcelo has already explained it, but since I had finished writing this maybe it will complement the help.

. . . snip . . .

Hope it helps.

Got it, thanks.

 

[pgc01]

OK, now I get it, thanks. I tested it and it works as you describe. However, I added an Application.Volatile statement to the UDF and now it does update automatically. Any problems with that?

Well, the usual problems with volatile functions.
I'd point out 2:
. performance - They're very inefficient, that's why they are avoided.
. dependence logic: hidden

Performance:
As you may know, a volatile function is recalculated everytime any value in the workbook changes.
If you use it in many places it may render the workbook unusable.

Ex. you have a table with 10000 rows and 2 columns A and B. Each row has just a value in column A and in column B a function that depends only on the cell to the left.
In case of a non-volatile function, you change A123 and B123 is automatically recalculated.
In case of a volatile function, you change A123 and all the 10000 cells in column B are recalculated.

This said, if you use just a few volatile functions, you may not notice any performance problem.

Dependence logic
When you look at a formula you don't just see how the value is calculated, you also see what it depends on.
This is important when you want to understand what's happening, especially when you don't know the workbook (or you forgot how it works).
For ex., I see in a cell =A1+MIN(F2,F5), I know that the value of the cell depends on A1, F2 and F5. If I change F2 and the value changes, I understand why.
If I have volatile functions like =MyFun(), if I change A1 I have no idea which cells values will be affected.
If you have tried to understand a workbook that you did not build you understand what this means.

Conclusion: I'm not saying don't write volatile udfs. It's just important to understand how they will impact the workbook.

 

[JenniferMurphy]

Just a reminder

It is usually bad practice to use an external value not passed as a parameter to the udf.
The udf will not monitor the value.
In case the value changes the udf result may display a wrong result until refreshed.

I just discovered another reason in support of your warning.

The UDF I am working on needs to access 6-7 rows of data. They all start and stop on the same columns, but occupy different rows. All but one of them are static (absolute). That is, they are defined with double absolute addresses, such as $D$10:$U$10. These are all "reference" rows. But one of the rows has absolute columns, but a relative row. This is the row that is compared to the reference rows. The function is called from a cell on that row.

Because I didn't fully understand your warning, I went ahead and coded the UDF to access all of these rows internally by their names rather than passing them as parameters. As you predicted, I found out that changing values in those rows did not cause the UDF to be re-executed, so the results were incorrect. While debugging, I got into the habit of forcing the sheet to recalculate using
Alt+Ctrl+Shift+F9.

Today, I spent about an hour trying to find a bug that turned out to be the other reason not to access data not passed as an argument. I had changed a value in one of the static rows and wanted to see the results, so I did the ACS+F9. I got a data error. After a lot of head scratching, I realized that the named range with the variable row looked to the UDF as if it were on the row where the cursor was and not where the call was.

So, lesson learned. Now I have to go put the code back to pass everything as a parameter.

Thanks for the tip!

 

[pgc01]

I'm glad it helped.

 

[JenniferMurphy]

Just a reminder

It is usually bad practice to use an external value not passed as a parameter to the udf.
The udf will not monitor the value.
In case the value changes the udf result may display a wrong result until refreshed.

As a result of this warning, which I have since discovered is entirely valid, I am in the process of converting the way data is passed to the UDF, but have run into a bizarre (to me) situation that I cannot understand.

This UDF currently needs access to 10 ranges in the calling sheet and there may be more in the future. Previously, in an effort to avoid a long, complicated, error-prone calling syntax, I decided to require the calling sheet to give the ranges standard names that the UDF could then access by those names without passing any parameters. I am now going back to the longer calling syntax with the calling sheet needing to explicitly pass each range to the UDF (10+ parameters).

I am about half way through that process. I have 6 of the 10 ranges now being passed as range parameters. The other 4 are still being accessed by the UDF using standard range names. If I run test calls, the UDF works correctly. But if I put a breakpoint (F9) on the Function statement of the UDF, the UDF gets called 4 times and returns the wrong answer. It also sometimes gets a circular reference error. If I remove the breakpoint, everything works properly.

Why would a breakpoint cause the UDF to operate differently? I am not doing anything when the break occurs other than hitting resume (F5).

 

[pgc01]

Sorry, I cannot think of a reason for that to happen.