开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜