Excel-VBA - Call Sub and ask it to regconise variable defined outside the Sub
I've tried to call a sub Lum() from my program callLum(), to ask Lum to get value 11,12,13 respective, but seem like the Lum can't recognise my earlier definition of counter in callLum(), illustrate as script below. The reason being is that in my actual Main script i have a long For-next loop, if i don't interrupt the loop with Msgbox, the program seem like cannot find the "For" word when it reached "next". That's why i end up wrap up the main script and later on create a simple sub to call it.
Sub callLum()
Dim counter As Integer
Dim LR As Long, j As Long
counter = 10
'checking number of rows in column A
LR = Range("A" & Rows.C开发者_如何学运维ount).End(xlUp).Row
For j = 1 To LR
If Not IsEmpty(Range("A" & j)) And Not IsEmpty(Range("B" & j)) Then _
counter = counter + 1
Call Lum
Next j
MsgBox "THE END"
End Sub
Sub Lum()
MsgBox "Counter value is " & counter
End Sub
Change Lum to be:
Sub Lum(ByVal counter as Integer)
MsgBox "Counter value is " & counter
End Sub
For your loop, rather than testing every element of the range separately, specify the range as a Ax:Ay where x and y are filled in by your loop.
精彩评论