How to define a variable in one UDF that will be available in a sub-UDF?

[JenniferMurphy]

Is there a way for me to declare a variable in a UDF so that it will be available to any UDF that it calls?

I tried

Public varname as double
Global varname as double

but they both got error messages.

A search turned up several webpages about global variables, but they all seem to indicate that I need to define the variables outside of the main UDF and that they then continue to exists as long as that workbook is open. I don't want that.

I just want the variables to be available to the main UDF and any that it calls and then go away when the main UDF exits.

Can that be done?

Thanks

 

[JenniferMurphy]

Let me see if I can be more clear. Here's a very simple example of what I want to do.

Public Function Main()
Dim A    'Declare A so that Sub1 can access it
If Sub1() = "OK" then ...
  . . .
End Function


Public Function Sub1()
If A > 10 then
  Sub1 = "OK"
Else
  Sub1 = "Low"
End If
End Function

How do I declare A in Main so that any UDF it calls (Sub1) can see it and change it.

Thanks

 

[Marcelo Branco]

Maybe something like this

Option Explicit

Public myvar As Long

Sub Main()
    MsgBox Func1(10)
End Sub

Function Func1(a As Long)
    myvar = a
    Func1 = Func2
End Function

Function Func2()
    myvar = myvar + 1
    If myvar > 10 Then
        Func2 = "OK"
    Else
        Func2 = "Low"
    End If
End Function

M.

 

[Marcelo Branco]

See
http://www.cpearson.com/excel/Scope.aspx

M.

 

[JenniferMurphy]

See
http://www.cpearson.com/excel/Scope.aspx

I had read that page and also another one by the same guy about declarations, but it didn't seem to answer my question unless I'm too dense to get it.

From your example and those articles, it sounds like there is no way for me to declare a variable within a UDF that will be available to a called UDF in the same module. I have to declare it outside the UDF and then deal with it persisting after the UDF has terminated. Is that correct?

Public Function Main() 
'Declare A so that Sub1 can access it
Dim A    
Public A
Private A
Global A
????? A

If Sub1() = "OK" then ...
  . . .
End Function

Public Function Sub1()
If A > 10 then
  Sub1 = "OK"
Else
  Sub1 = "Low"
End If
End Function

 

[offthelip]

Why don't you pass it as a parameter to the sub UDF

 

[Marcelo Branco]

I had read that page and also another one by the same guy about declarations, but it didn't seem to answer my question unless I'm too dense to get it.

From your example and those articles, it sounds like there is no way for me to declare a variable within a UDF that will be available to a called UDF in the same module. I have to declare it outside the UDF and then deal with it persisting after the UDF has terminated. Is that correct?

Yes, it is.

Why don't you pass it as a parameter to the sub UDF

This should work.

M.

 

[JenniferMurphy]

Maybe something like this

Option Explicit

Public myvar As Long

Sub Main()
    MsgBox Func1(10)
End Sub

Function Func1(a As Long)
    myvar = a
    Func1 = Func2
End Function

Function Func2()
    myvar = myvar + 1
    If myvar > 10 Then
        Func2 = "OK"
    Else
        Func2 = "Low"
    End If
End Function

M.

I tested that and it works. Thanks.

Unfortunately, as I was testing it, I realized that I didn't explain the situation correctly. Let me try again.

I am working on a UDF that processes rows of data in several passed ranges. The rows are products and the columns are features or properties of those products. Each row contains ratings on some scale for that product for the feature in that column. The UDF is called once on each row (B3:B5). It loops through the columns on that row (C:F) and returns a weighted rating based on relative weights in another fixed row. It looks something like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD="align: right"]Features[/TD]
[TD="align: center"]Feat #1[/TD]
[TD="align: center"]Feat #2[/TD]
[TD="align: center"]Feat #3 [/TD]
[TD="align: center"]Feat #4 [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Products[/TD]
[TD="align: right"]Weights[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Product #1[/TD]
[TD="align: center"]78.75[/TD]
[TD="align: center"]$225[/TD]
[TD="align: center"]3.8[/TD]
[TD="align: center"]144[/TD]
[TD="align: center"]90[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Product #2[/TD]
[TD="align: center"]90.25[/TD]
[TD="align: center"]$236[/TD]
[TD="align: center"]4.7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Product #3 [/TD]
[TD="align: center"]39.01[/TD]
[TD="align: center"]$400[/TD]
[TD="align: center"]3.2[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]58[/TD]
[/TR]
</tbody>[/TABLE]

The UDF is called in cells B3:B5, once for each product. For each call, it returns the weighted rating based on the values in that row given the weights in $C$2:$F$2. In this example, the UDF is called 3 times and on each call, it loops through 4 sets of data.

In real life, there could be 50 columns (features) and 30 rows (products). That would be 1500 data points, any one of which could be incorrect. And once entered incorrectly, they could be difficult to find by looking at the table. For example, all values must be numbers, the values for Features #3 & #4 must all be integers, etc.

So, the UDF does a lot of data validation. To make the data correction easier, I wrote an error handling subroutine that the UDF calls when it detects any errors. The error handler generates an error message that includes the cell address where the UDF was called (eg, $B$4) and the cell address where the error occurred (eg, $E$8). It puts this message up in a MsgBox with buttons that allow me to continue or issue a Stop statement so I can do some testing.

Here's the problem. If the error is in a single data cell, a single error message will be generated. But if the error is in one of the weights, it will generate an error message for every product. If there are 30 products, I have to clear 30 MsgBox messages before I can stop the code from running so I can correct the error. Worse, if the error is caused by a bug in the code, such as testing for a condition to be True when it should have been False, it could generate an error message on every data cell for every product. Then I would have to clear 1500 MsgBoxes.

Your global variable solution allowed me to solve the second problem. I defined a global variable, ErrMsgSw, that the error handler checks. If it's False, it does not generate the message.

Here's a snippet of that code:

Option Explicit
Public ErrMsgSw As Boolean
  . . .

Public Function WtdRtg(  . . . )
  ErrMsgSw = True
  . . .
For iCol = RngColBeg To RngColEnd
  . . .
  If (some condition) then Call WtdRtdErr()
    . . .
Next i
  . . .
End Function

Public Sub WtdRtgErr(FnName, Caller)
If Not ErrMsgSw Exit Sub
  . . .
Dim Button As Integer   'The button that was clicked
Button = MsgBox(msg, vbYesNoCancel + vbDefaultButton2, FnName & " (" & Caller & ")")
If Button = vbYes Then Stop
If Button = vbCancel Then ErrMsgSw = False

End Sub

This all works great. Thanks for the help.

But when the UDF is called for the next product, that switch setting is lost and an error message is again displayed for that product. So if there are N products in the table, I will have to clear N messages.

The only solution I have been able to come up with so far is to select the Stop option and then comment out the statement at the top of the UDF that sets ErrMsgSw = True. This works, but then I have to remember to change it back.

I would much rather have an option to set some super-global variable that will persist between UDF calls for as long as the workbook is open.

Is there any such beast?

Thanks

 

[RoryA]

No. There is nothing special about UDFs from a code perspective, so all the same rules of variable scope and lifetime apply.

FWIW, I don't think it's good practice for a UDF to produce message boxes, precisely for reasons like this.

 

[offthelip]

Why not add an extra parameter a Boolean, message on/off, you can reference the same cell from all the calls