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

 

[Marcelo Branco]

Jennifer,

I would take another approach.

First of all I would create a routine, completely separate from the UDF, to validate the data. No message box would be issued by this routine. When dealing with each line, it could insert in an adjacent cell something like:
OK (no errors)
or
A message describing the location and type of error as best as possible.

So the UDF, free to validate the data, would be cleaner and faster. Assuming the validation routine writes the message in column Z, the UDF in B3 (for example) would be called as follows:
=IF(Z3="OK", MyUDF(arg1, arg2..argN), "Check for errors")

Of course I do not know your specific case exactly and I do not know if such an approach is feasible. But I think the idea of encapsulating the data validation can be a safer and cleaner solution.

Hope this helps.

M.

 

[JenniferMurphy]

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

Maybe I don't understand your comment, but I am specifically not talking about the UDF, but something outside of it that will persist between calls.

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

In general I agree with you, but I don't have a better solution. Do you?

 

[JenniferMurphy]

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

Do you mean a cell in the worksheet? I thought of that, but maybe I need to think again. Thanks.

 

[JenniferMurphy]

Jennifer,

I would take another approach.

First of all I would create a routine, completely separate from the UDF, to validate the data. No message box would be issued by this routine. When dealing with each line, it could insert in an adjacent cell something like:
OK (no errors)
or
A message describing the location and type of error as best as possible.

So the UDF, free to validate the data, would be cleaner and faster. Assuming the validation routine writes the message in column Z, the UDF in B3 (for example) would be called as follows:
=IF(Z3="OK", MyUDF(arg1, arg2..argN), "Check for errors")

Of course I do not know your specific case exactly and I do not know if such an approach is feasible. But I think the idea of encapsulating the data validation can be a safer and cleaner solution.

Hope this helps.

M.

I like this in principle and I love the idea of simplifying the mainline code because it is hella complicated now, although I've moved most of the error checking to subroutines so it's just one-liners in the mainline code.

But when and how would it be called? It kinda needs to be called every time any data is entered or changed or at least before the main routine is called if any data has changed since the last validation call. Can I make that routine volatile so that it runs whenever anything changes and then have the mainline code just check that cell to see that everything is OK?

But how is that different from what I'm doing now if I just replace the MsgBoxes with output to a file and a final Ok/Nok to the Z cell?

 

[RoryA]

Maybe I don't understand your comment, but I am specifically not talking about the UDF, but something outside of it that will persist between calls.

You appeared to be asking for some magically persistent variable. I was merely pointing out that everything in Chip's article(s) applies equally to UDF code, since there's nothing special about the code per se. So your options for variables are module-level or public, or you pass an argument.

In general I agree with you, but I don't have a better solution. Do you?

There isn't really enough information as to what exactly your process is, but in general I'd probably suggest validating the data at its point of entry. If you really need your function to indicate the source of its errors, you could output that information as its result rather than use a message box but in my opinion that's making the UDF do more than it should. It should simply indicate that there is an error, and leave the user to determine what that is - perhaps using a separate manually initiated validation routine as Marcelo suggested.

 

[Marcelo Branco]

... in my opinion that's making the UDF do more than it should. It should simply indicate that there is an error, and leave the user to determine what that is...

I strongly agree! In my opinion, too, it should not be the task of the UDF to check for errors in the data. The UDF checking for some user errors such as ranges of arguments, data type etc seems to me correct. In short, I think that before calling the UDF the raw data must be checked and corrected.

M.

 

[JenniferMurphy]

You appeared to be asking for some magically persistent variable.

I don't think persistent variables are magic. I find that just a tad condescending.

There isn't really enough information as to what exactly your process is, but in general I'd probably suggest validating the data at its point of entry.

??? The point of entry is the cell in the sheet. Is there a way that I can check the validity of the data in each cell as it is entered? And then, I would have to recheck some of those cells if some global parameters change.

If you really need your function to indicate the source of its errors,

I think I made that pretty clear. There could be hundreds or even thousands of data cells. Just getting back a Value error from the routine that is processing them all is not that helpful.

you could output that information as its result rather than use a message box

The "result" is a number that is a kind of sum product over hundreds of data cells. A 5-6 line error message is going to be just a tad cramped. The message box gives me exactly what I need to identify and fix the error. I just need to be able to turn it off temporarily.

but in my opinion that's making the UDF do more than it should. It should simply indicate that there is an error, and leave the user to determine what that is

Did you work for the Microsoft VBA development team? That apparently was their philosophy. An error occurs, they know a lot about the details of what it was and where it was, but they chose to simply return #VALUE !. And I found it annoying that they chose to add the "!" -- kind of like, "Ha-ha-ha-ha-ha. Now you go figure out what happened, 'cause we ain't gonna tell you".

perhaps using a separate manually initiated validation routine as Marcelo suggested.

I'm looking into that suggestion, but isn't software all about replacing "manual" with "automatic"?

ymmv

 

[JenniferMurphy]

I strongly agree! In my opinion, too, it should not be the task of the UDF to check for errors in the data. The UDF checking for some user errors such as ranges of arguments, data type etc seems to me correct. In short, I think that before calling the UDF the raw data must be checked and corrected.

I wold love to be able to check the input data as it is entered. I just don't know how to do that in Excel. This probably should have been a database application. Then I could check each data point as it is entered. Or maybe I need to create a data entry form, but I was hoping to avoid that extra work. I'm not that good with forms.

I'll look into your suggestion of a separate UDF to validate the data. If the main routine encounters an error, I could then run the data validation routine.

Thanks for that suggestion.

 

[offthelip]

If yoiu stay with your current system of using the UDF to do the validation, but add the extra boolean parameter using a cell on the worksheet, you can then throw up a three way message, 1: carry on but continue throwing messages, 2: carry on but set the cell flag to false and so throw no more message, 3: abort

 

[JenniferMurphy]

If yoiu stay with your current system of using the UDF to do the validation, but add the extra boolean parameter using a cell on the worksheet, you can then throw up a three way message, 1: carry on but continue throwing messages, 2: carry on but set the cell flag to false and so throw no more message, 3: abort

Yes, that's exactly what I have in mind. There will be one more option: Execute a Stop statement so I can do some debugging.