I was flicking through John's recently opened Hard Core XL VBA site (well forum), theres a lot of content there, it's a nice way to store a lot of code. One thing that i had never seen before was the go sub method
Basically you goto a Lable, excute whatever code follows and finish with a return line, viz
Sub Test()
x = Array(1, 10, 100)
For i = 0 To UBound(x)
If x(i)> 10 Then
GoSub ValueError
End If
Next
Exit Sub
ValueError:
MsgBox "the " & i & "th vlaue in the set X is more than 10."
Return
End Sub
x = Array(1, 10, 100)
For i = 0 To UBound(x)
If x(i)> 10 Then
GoSub ValueError
End If
Next
Exit Sub
ValueError:
MsgBox "the " & i & "th vlaue in the set X is more than 10."
Return
End Sub
Now i didn't know you could do that!!!. I think i'd still be inclined to farm it out into another sub or function, but it could be useful all the same.
Hi Ross,
Yes, I usually would do the same, but it's useful when you may have (say) lots of variables that you don't want to declare as public (but then a function would probably be the way to go) anyway, it's another choice that's always available in our bag of tricks.
Regards,
John :)
Not to mention the static var dec too John - it's a stange one, but it guess it just another tool like toy say.
Hi Ross,
Yes, not to mention the static vars... Gosub and Return was used in a lot of earlier programming languages, notably BASIC (the grandfather of VBA).
You can have as many Gosub/Returns as you like in a procedure but its use has largely declined due to the propensity for novice programmers to use it in a loose unstructered manner, with GoSubs and Returns popping up everywhere in the main body of code. It then becomes difficult to follow the flow of the code.
Never-the-less, it can be handy occasionally as long as it's kept structured and not 'overdone'. I've updated the example on my site to show the difference, here it is (I don't know if the code tags will show properly here)
---------------------------------------------------------------------------------
A simple example use of Gosub and Return...
Dim Cell As Range, TargetSheet As String
With Sheets("Sheet1")
For Each Cell In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Select Case Cell.Offset(0, 4)
Case "Match"
TargetSheet = "Sheet2": GoSub DoCopy
Case "No Match"
TargetSheet = "Sheet3": GoSub DoCopy
Case "Part Match"
TargetSheet = "Sheet4": GoSub DoCopy
Case "Negative Match"
TargetSheet = "Sheet5": GoSub DoCopy
Case Else
TargetSheet = "Sheet6": GoSub DoCopy
End Select
Next
Exit Sub
DoCopy:
.Range(Cell.Address, Cell.Offset(0, 2)).Copy _
Sheets(TargetSheet).Range("A" & Rows.Count) _
.End(xlUp).Offset(1, 0)
Return
End With
End Sub
As a comparison, the alternative code below to accomplish exactly the same task is 'wordier', more obscure for a coder to 'read', and it's difficult to ascertain the differences for each case (the only differences being the sheets name :))
Dim Cell As Range
With Sheets("Sheet1")
For Each Cell In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Select Case Cell.Offset(0, 4)
Case "Match"
.Range(Cell.Address, Cell.Offset(0, 2)).Copy _
Sheets("Sheet2").Range("A" & Rows.Count) _
.End(xlUp).Offset(1, 0)
Case "No Match"
.Range(Cell.Address, Cell.Offset(0, 2)).Copy _
Sheets("Sheet3").Range("A" & Rows.Count) _
.End(xlUp).Offset(1, 0)
Case "Part Match"
.Range(Cell.Address, Cell.Offset(0, 2)).Copy _
Sheets("Sheet4").Range("A" & Rows.Count) _
.End(xlUp).Offset(1, 0)
Case "Negative Match"
.Range(Cell.Address, Cell.Offset(0, 2)).Copy _
Sheets("Sheet5").Range("A" & Rows.Count) _
.End(xlUp).Offset(1, 0)
Case Else
.Range(Cell.Address, Cell.Offset(0, 2)).Copy _
Sheets("Sheet6").Range("A" & Rows.Count) _
.End(xlUp).Offset(1, 0)
End Select
Next
End With
End Sub
thanks John, Sorry that the code is not very well displayed, when time permits i will look into it ;-)