Is variable defined?

Q: How would I go about seeing if a variable exist / is declared in excel, when Option Explicit is on?

I am searching for a pattern in a text file and when it finds it, it retrieves the location (chars length from the left) of that pattern. This occurs each time it finds the pattern. When it does not find this pattern it should assign a variable (for example: 80).

The solutions I found online (IsEmpty, TypeName) all require Option Explicit to be off, and I was really hoping for it to be able to stay on. I was thinking about declaring the matches and giving them a default value of 0, then check if the value is > 0 or not. The problem with this is that I don't know how many matches there might be, and thus I don't know how many matches I should define.

This is how a text file would like:

                                     2012        2011
title 1                              123,342     123,435
title 2                              234,431     645,234
title 3                              324,234        - 

The script reads each line and extracts the data into columns (based on the years). It does this by finding a pattern (" [0-9]") and then finding when that patterns occurs. This all works great, however when no pattern can be found (eg at "title 3 - 2011") the script stops. (Note: "-" could also be a blank space, or "--", or pretty much anything).

Here is an excerpt of the code I have right now:

**Top of document**
Dim re As Object, matches As Object, match As Object, submatch
Dim iNoColumns As Integer, ii As Integer, NumberMatch As Variant
Dim sLine As String

ReDim iColumns(1 To iNoColumns) As Integer

          Set re = CreateObject("vbscript.regexp")                  
              re.Pattern = " [0-9]"
              re.IgnoreCase = True
              re.Global = True
Set matches = re.Execute(sLine)
For Each match In matches
   NumberMatch = match

If VBA.InStr(1, sLine, NumberMatch) > 0 Then
   iColumns(1) = VBA.InStr(1, sLine, matches(0).Value)
   For ii = 2 To iNoColumns
      If matches(ii - 1).Value Is Nothing Then
        iColumns(ii) = 70 + (ii * 10)
        iColumns(ii) = VBA.InStr(iColumns(ii - 1) + 1, sLine, matches(ii - 1).Value)
        Debug.Print "Column " & ii & " starts at: " & iColumns(ii)
      End If
   Next ii
End If

It is mainly about If matches(ii - 1).Value Is Nothing Then iColumns(ii) = 70 + (ii * 10) Else part. This is the part that doesn't work, and I am not sure what to do to fix it. The rest works of the code works great except if matches (1) or matches(2) or matches (3) (etc..) doesn't exist.

If I have to define or explain any of the variables used above, please let me know.

  • sLine is current line (reading in a text file with Scripting.FileSystemObject)
  • iNoColumns is number of columns (this determines how many matches are needed: if there are 2 columns, the script looks for two matches (matches(0) and matches(1)).
  • iColumns(ii) is the lenght of each column; determined by where a match is found.

I tried this stackoverflow solution (and all of its combinations), also tried IsNull.
TypeName returns matches(ii - 1).Value as String.


For Each match In matches
   NumberMatch = match

First, is this actually doing what you want it to do? Do you only want the last object in matches to be set (regardless of what the previous ones are)?

Second, do the following:

dim matches As Variant

If matches(ii - 1) = "" Then

This should fix this problem (you may need to include an or statement to check if it equals 0 as well).

VBA (source):

| Type                                 | Test                            | Test2
| Numeric (Long, Integer, Double etc.) | If obj.Property = 0 Then        | 
| Boolen (True/False)                  | If Not obj.Property Then        | If obj.Property = False Then
| Object                               | Is obj.Property Is Nothing Then |
| String                               | If obj.Property = "" Then       | If LenB(obj.Property) = 0 Then
| Variant                              | If obj.Property = Empty Then    |

or enderlands solution (this post).

Javascript (source):

if(typeof variable_here === 'undefined'){  
   // your code here.  


if(! variable_here){  
   // your code here.  

Phython (source):

except NameError:
  print "well, it WASN'T defined after all!"
  print "sure, it was defined."

or (source) To check the existence of a local variable:

if 'myVar' in locals():
  # myVar exists.

To check the existence of a global variable:

if 'myVar' in globals():
  # myVar exists.

To check if an object has an attribute:

if hasattr(obj, 'attr_name'):
  # obj.attr_name exists.

Other: Easy way to check that a variable is defined in python? and How do I check if a variable exists?.


 ? What is the difference between null and undefined in JavaScript?
 ? What is the difference between null and undefined in JavaScript?
 ? What is the difference between null and undefined in JavaScript?
 ? When is null or undefined used in JavaScript?
 ? Undefined vs null in javascript
 ? How do I check for null values in JavaScript?
 ? Can I set variables to undefined or pass undefined as an argument?
 ? Why is there a `null` value in JavaScript?
 ? How to pass the value 'undefined' to a function with multiple parameters?
 ? 'Undefined' or 'Null' Variable in JavaScript