Using a variable as an optional parameters default

Hi all.

You see when you make a procedure that has an optional parameter and you want to give the optional parameter a default, you do something like:

Please Login or Register  to view this content.

or you can use a constant (either a system constant or a user defined constant will work) for the default like:

Please Login or Register  to view this content.

So that's all cool.
But I tried to use a variable for the default and Excel wasn't having any of it. I get an error message saying "Compile error: Constant expression required". So that means I can't use a variable! FS

In my code there is a variable that holds the name of the current "thing" that the code is working on (a filename actually). When I call this particular procedure, I want it to execute based on whatever I pass to it as the parameter, or if there's no parameter passed, I want it to execute on whatever the current "thing" is that the code is working on instead.

It seems to me that I should be able to use a variable but clearly I'm not allowed.

Anyone any idea how I can achieve something similar here?

OK, so I've just realised that within the procedure I can test if the parameter holds the default value, say 'vbNullstring" (or more likely, something crazier that definitely won't be passed like "azazazazaz"), and then if it does, just replace the value of the parameter with the value of my variable.

Please Login or Register  to view this content.

So, I answered my own thread. But anyone have any idea why you can't just include a variable there? It seems reasonable.

The value for an optional parameter default must be known at compile time, so it cannot contain a variable or any other expression that is known only at runtime (like a function call). The code for a Sub call is generated at compile time, not evaluated at runtime. This is a fundamental VBA design decision, and IMHO a good one. When you start doing things like dynamic defaults it creates all kinds of opportunities to introduce bugs that are very difficult to diagnose.

Where is the filename variable declared relative to your Sub? You have two options:

1. Simply pass the file name as the parameter.

2. If the filename variable is visible within the scope of the Sub, you can do this:

Please Login or Register  to view this content.

Frankly, I am not so sure your desired approach is a sound software design, but it's hard to say without seeing all your code.

Originally Posted by 6StringJazzer

The value for an optional parameter default must be known at compile time...

Well, that explains it then. I don't really get yet which things have to be known at compile time and which don't but if that's the case for optional parameters then at least it makes sense to me now why I can't use a variable. Thanks for the knowledge boost Jazzer.