Why Am I getting Variable Undefined in VBA For Each Loop

Question

I have been getting a large number of answers from Stackoverflow and I really appreciate all of the information you experts have provided! This is my first time asking a question - so thank you.

I am running various VBA programs in the backend of a large Excel 2010 workbook and I am completely stumped on my current issue. In a Module, I have a public sub with a For Each loop that iterates through a variant which is loaded with a range of string values in a worksheet.

When I run this loop, I either get an immediate compile error "Variable not defined" and the for each variable is highlighted or the program runs but loops without end. I have literally commented everything out in the loop itself and I still get this issue. When the loop does run, I see the string value being correctly retrieved into the for each variable. If I comment out the loop then the sub runs without any issues.

Here is what I have to be clear:

Dim showWord as String
showWord = ""
For Each thisWord In allWords
     'showWord = CStr(thisWord)
     'MsgBox showWord
Next x

Now I have this exact same loop elsewhere in a different module and it always runs without issue. I did change both the for each variable and variant variable names but that was it.

Can somebody please help me figure out what is going on here?

Thank you.

Stupid question: have you already defined thisWord and allWords earlier in the code? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Sep 24, 2014 at 18:26

Rafa Barragan · Accepted Answer · 2016-03-23 23:50:35Z

3

your loop doesn't end because in Next x must be the same variable you are looping, something like this Next ThisWord and you must declare allWords.

Dim showWord as String
showWord = ""

For Each thisWord In allWords
    ....
Next thisWord

answered Mar 23, 2016 at 23:50

Rafa Barragan

6209 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4039065 Over a year ago

Good catch! That was completely missed by the original responder (and hte OP it seems!).

Andre Taron · Accepted Answer · 2014-09-24 18:30:48Z

0

Pretty sure you didn't define your variable allWords, as follow

Dim showWord as String
showWord = ""

dim allWords as (wtv)
allWords = (something)

'if not allWords is nothing then
  For Each thisWord In allWords
     'showWord = CStr(thisWord)
     'MsgBox showWord
  Next x
'end if

answered Sep 24, 2014 at 18:30

Andre Taron

1216 bronze badges

Collectives™ on Stack Overflow

Why Am I getting Variable Undefined in VBA For Each Loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related