0

I have the following function to check for the existence of a variable:

Private Function DoesVariableExist(Optional valuePassed As Variant) As Boolean
    If Not IsMissing(valuePassed) And Not IsEmpty(valuePassed) Then
        DoesVariableExist = True
    End If
End Function

The idea being that if the variable doesn't exist, I can run different code than if it does exist.

The code works fine as long as I remove the Option Explicit line from the module. With that line in there, I get a compiler error, "Variable not defined," because, of course, that's what I'm checking for. This is a small module with only a handful of procedures, so I could probably get away with not requiring variable declaration, but that's not good coding practice. So, what's the alternative?

Improve this question
2
  • Keep Option Explicit and fix the code. You are asking for trouble here by having different code blocks do the same thing depending on a variable declaration. Commented Mar 18, 2015 at 20:34
  • 1
    It isn't my code to "fix". It's general code that gets reused for projects that may or may not have the variable defined. Commented Mar 20, 2015 at 19:40

4 Answers 4

Reset to default
3

fwiw - this isn't a good approach to take for runtime logic with any code. If your code depends on a specific variable, then you should always have that variable declared inside your procedure but with a default value which is essentially why we have Optional params. Instead of checking for the variable's existence just check for the default value.

e.g.

Sub Foo(Optional ByVal Bar As Integer = -99)
    '// My code here....
    If Bar = -99 Then
        '// variable wasn't supplied to the procedure
    Else
        '// Do something with the variable that WAS supplied
    End If
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In general, yes, but my environment is a little odd. This code is part of ThisWorkbook that can be updated for hundreds of Excel spreadsheets automatically, so I need the check to ensure that the functional code of those spreadsheets isn't broken if the variable doesn't exist. One of those "exceptions that proves the rule" things.
0

As much of a pain as it is, since you can't tell if a variable exists, double up on it. When a variable is created, create a bookmark along with it, and check to see if the bookmark exists. If the bookmark exists, then you know the variable exists. It's one extra line instead of multiple lines of programming

Improve this answer

1 Comment

Could you please show the code for your explanation? It would be much easier to understand what you mean
-1

The alternative is to dive into programming the VBE itself. This function loops through the declaration section of the standard modules in the passed project to find any line that declares the passed variable name. It only checks the declarations section because scope matters and any procedural variables won't be seen by this function anyway.

Private Function DoesVariableExist(book As Workbook, varName As String) As Boolean
    Const ProcKind As Integer = 0 'sub or function
    Const vbext_ct_StdModule As Integer = 1 'standard modules only
    Const DIMSTMNT As String = "Dim"
    Const PUBLICSTMNT As String = "Public"
    Const PRIVATESTMNT As String = "Private"
    Const CONSTSTMNT As String = "Const"

    Dim VBProj As Object
    Dim VBComp As Object
    Dim codeMod As Object
    Dim procName As String
    Dim lineNum As Long
    Dim lineText As String

    DoesVariableExist = False

    'set object to components within the project
    Set VBProj = book.VBProject.VBComponents

    'loop through components
    For Each VBComp In VBProj

        'find standard modules
        If VBComp.Type = vbext_ct_StdModule Then

            'set object to code module
            Set codeMod = VBComp.CodeModule

            With codeMod

                'loop through lines of code in the declaration section
                For lineNum = 1 To .CountOfDeclarationLines
                    lineText = .Lines(lineNum, 1)

                    'check for declarartion statement and passed variable name (case-insensitive)
                    If (InStrB(lineText, DIMSTMNT) > 0 Or _
                        InStrB(lineText, PUBLICSTMNT) > 0 Or _
                        InStrB(lineText, PRIVATESTMNT) > 0 Or _
                        InStrB(lineText, CONSTSTMNT) > 0) And _
                        InStrB(LCase(lineText), LCase(varName)) > 0 Then

                        DoesVariableExist = True
                        Exit Function
                    End If
                Next lineNum
            End With
        End If
    Next VBComp
End Function

This comes with its own negatives, of course. You need to trust access to the VBA project object model (found in the Trust Center Settings), which can open you up for malicious code. But in an environment where you are not opening files from unknown sources, the trade-off can be well worth it.

As an aside, I'm using late binding here, so you don't have to set a reference to access the VBE objects.

Improve this answer

2 Comments

Check every line for the entire code base on each function call. Yikes!
It gets called once, so the overhead is minimal.
-1 
Public Class Form1
    Dim varExists As Boolean = False ' This should not be placed within a sub. Choose any valid name, preferably one that makes sense, like "varOneExists," as long as you change all instances of it to the new name.
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click ' This can be any other sub, just take the code within the sub and place it elsewhere.
        Dim otherVar As String = "" ' Doesn't need to be a string. Any valid name is fine, as long as you change all instances of it to the new name.
        varExists = True ' Always put this after the code that declares otherVar.
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click ' This can be any other sub, just take the code within the sub and place it elsewhere.
        If varExists = True Then
            ' The code here will run if otherVar has been declared. Feel free to add more valid lines, as it will still work.
        Else
            ' The code here will run If otherVar has not been declared. Feel free to add more valid lines, as it will still work.
        End If
    End Sub
End Class
Improve this answer

2 Comments

As far as I know, this should work for any other language that supports something that works like If End If. If a language has something analogous to If, End If, but does not have an analogue to If, Else, End If, then do an If, End If for true, and an If, End If for false.
Your answer is VB.NET - the question is regarding VBA, which isn't the same

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.