Visual Basic Forgets What Variables are

  • Thread starter Thread starter Jack Marks
  • Start date Start date
J

Jack Marks

I have a terrible problem that I guess I am too stupid to debug. It seems
my VBA macro works, except for one part. It is supposed to see if an
Excel cell already contains the data which is in the HoldName variable. So
I say if Cells(2,2)= Holdname, then InList=1. But when I try to debug it,
Holdname seems to be blank, even though it used to hold a name. Where did
it go? Do I need more than Dim Holdname, inlist. ?
 
You probably declared Holdname in another macro, and if you don't use Option
Explicit it will create a new one, a blank one.

Post the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Here is the code. I get an error in the 2nd line of printit(), the one
that is supposed to print the result.

Sub MainAddsNamesToResults()
'
Dim NotEmpty, AddNameLine, Holdname, ToShow, Homer, PrintName

'Procedure:

'1 is the cell empty? if so, then do not do compare
'2 is it already in the RESULTS list? Then mark it as such
'3 if no mark, then add it

'2. Determine AddNameLine - it does this first, the m 5 to 400

For m = 5 To 400
pullname = Worksheets("RESULTS").Cells(m, 4)
If pullname = "" Then AddNameLine = m: m = 400

Next m

'Begin Main Loop
Holdname = ""
For i = 10 To 400
Holdname = Worksheets("PhysicalSchedule").Cells(i, 5)
PrintName = Holdname ' this is what we will use to print, since holdname
disappears at some point
Worksheets("results").Cells(8, 2) = Holdname
ToShow = 1 ' which means it will print, unless we change the 1 to
something else
If Holdname >= "A" Then Call CompareLoop Else ToShow = 0
If ToShow = 1 Then Call PRINTIT
Call SHOWME
Next i
'next line is added so alphabetization routine finds the block, the one with
data in it
AddNameLine = AddNameLine - 1
'END Main Loop
End Sub



Private Sub CompareLoop()
'The following sets a flag if the name is already there
'InList = 0
For j = 5 To 400
Homer = Worksheets("RESULTS").Cells(j, 4)
Cells(9, 2) = Homer
If Homer = Holdname Then ToShow = 0: j = 400

Cells(10, 2) = j

Next j

End Sub



Private Sub ALPHA()
'This alphabetizes the lines we have
Range("C4").Select
Selection.Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

Private Sub SHOWME()
'This sub displays the values of our variables whenever we call it up
Worksheets("RESULTS").Cells(12, 2) = Holdname
Cells(13, 2) = ToShow
Cells(14, 2) = j
Cells(15, 2) = Homer
End Sub

Private Sub PRINTIT()
z = AddNameLine
Worksheets("RESULTS").Cells(z, 4) = PrintName
AddNameLine = AddNameLine + 1
End Sub
 
HoldName will go back to the default (empty) in your code.

One way to keep its value is to declare it as static:

Sub MainAddsNamesToResults()
'
Dim NotEmpty as variant
dim AddNameLine as variant
Static Holdname as variant
dim ToShow as variant
dim Homer as variant
dim PrintName as variant
'rest of code.

=======
another way is to declare it outside the procedure (at the top of the module):

dim Holdname as variant
Sub MainAddsNamesToResults()
Dim NotEmpty as variant
dim AddNameLine as variant
dim ToShow as variant
dim Homer as variant
dim PrintName as variant
'rest of code

By dimming it outside the procedure, it'll be able to be seen by all procedures
in that module.

If you want to have other procedures in other modules see it, too, use:

Public HoldName as Variant
Sub MainAddsNamesToResults()
Dim NotEmpty as variant
dim AddNameLine as variant
dim ToShow as variant
dim Homer as variant
dim PrintName as variant
'rest of code

You have a few options.
 
I guess this makes sense. I will have to try it. I did not realize that
my dims were inside the procedure.
 
It worked! I put all of the dim statements in front of the sub (as you
said below, outside of the procedure) and it recognized them and ran
correctly. Error gone! Thank you!
 
Remember that if you have any accumulators that are dimmed outside the
procedure, then you're responsible for setting them back to zeroe (probably when
you start).

It's not always a good idea to use variables this way--it really depends.
 
Thanks again.
Dave Peterson said:
Remember that if you have any accumulators that are dimmed outside the
procedure, then you're responsible for setting them back to zeroe
(probably when
you start).

It's not always a good idea to use variables this way--it really depends.
Click to expand...
 
Back
Top