Suspending 'Require Variable Declaration'

[IanGalletly]

Can anyone point me in the right direction to setting the 'Require Variable Declaration' in code so it can be temporarily set to false?

I have a form which breaks down an SQL string to a string which can be pasted into VBA module to a assign it to a variable. The Form also allows a block of VBA code to be converted back into an SQL statement.

The latter is achieved using VBA Extensibility functionality by creating a temporary module and function containing the code block. The function is then called to return the SQL string.

Everything works correctly unless the code block fails to compile for one reason or another.

When working I have the option 'Require Variable Declaration' set. This means the temporary module is getting 'Option Explicit' included. If I set this option to false the call to the newly created function just returns nothing in most of the problem cases. A result I can easily live with as the form has 'undo' functionality allowing the user to get back to the code display and fix the problem.

If the 'Require Variable Declaration' can not be set, can anyone suggest an alternative method to handle a failure for the code to compile?

 

[Zathras]

You could edit the module and remove the "Option Explicit" line, but it would be simpler to set the Preference to not have "Require Variable Declaration" checked. See Tools/Options Editor tab in the Code Settings group for that option.

 

[TonyJollans]

Hi Ian,

I agree you could edit the line out, but why, if you're generating the code, do you not generate the Dim as well so that there isn't an error whether or not you have Option Explicit?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[IanGalletly]

Zathras

The module should only exist for the time the code takes to run, once the function has been written out, it is called, the result written back to the form and the module is then deleted. I want to avoid having to edit it manually if at all possible.

Having "Require Variable Declaration" set to True for normal work just stops silly errors when you forget to add the "Option Explicit" line to a new module. I would prefer not to set it permanently to False if possible.

Tony

The problem is I do not know in advance what variables the user has left in the VB block.

 

[TonyJollans]

Hi Ian,

To remove the line, try this ..

[blue]Set mymod = Application.VBE.VBProjects("[i]YourProject[/i]").VBComponents.Add(vbext_ct_StdModule)
If mymod.CodeModule.CountOfLines > 0 Then mymod.CodeModule.DeleteLines 1, mymod.CodeModule.CountOfLines[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[IanGalletly]

Thanks Tony

Thats all I needed - a little bit of lateral thinking

The using DeleteLines I can remove the Option Explicit between creatingthe module and inserting the temporary function.

Thanks again.