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 Next 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) Else 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.
sLineis current line (reading in a text file with
iNoColumnsis 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
matches(ii - 1).Value as String.