identify code error

N

Nancy

I am learning to write code but miss basic concepts. On this one I get an
error message "expected End Sub" but it appears to be at the wrong place.
This is the code. Please help.

Private Sub SeminarID_AfterUpdate()

Function FindWorkshopLocation(varSeminarID As String) As String

If varSeminarID = "070226WMel" Then
WorkshopLocation = West_Melbourne
ElseIf varSeminarID = "070227Orl" Then
WorkshopLocation = Orlando
ElseIf varSeminarID = "070228Gai" Then
WorkshopLocation = Gainsville
ElseIf varSeminarID = "070301Sar" Then
WorkshopLocation = Sarasota
ElseIf varSeminarID = "070302Tam" Then
WorkshopLocation = Tampa

End If

End Function
End Sub
 
R

Rick Brandt

Nancy said:
I am learning to write code but miss basic concepts. On this one I get an
error message "expected End Sub" but it appears to be at the wrong place.
This is the code. Please help.

Private Sub SeminarID_AfterUpdate()

Function FindWorkshopLocation(varSeminarID As String) As String

If varSeminarID = "070226WMel" Then
WorkshopLocation = West_Melbourne
ElseIf varSeminarID = "070227Orl" Then
WorkshopLocation = Orlando
ElseIf varSeminarID = "070228Gai" Then
WorkshopLocation = Gainsville
ElseIf varSeminarID = "070301Sar" Then
WorkshopLocation = Sarasota
ElseIf varSeminarID = "070302Tam" Then
WorkshopLocation = Tampa

End If

End Function
End Sub
Click to expand...

You have a function begin and end inside of a sub begin and end (incorrect).
Your code is either a sub OR it is a function. You can CALL a function inside
of a sub, but you cannot define a function inside a sub.

Since your function is not returning anything it would appear that all you need
here is a sub...

Private Sub SeminarID_AfterUpdate()
If varSeminarID = "070226WMel" Then
WorkshopLocation = West_Melbourne
ElseIf varSeminarID = "070227Orl" Then
WorkshopLocation = Orlando
ElseIf varSeminarID = "070228Gai" Then
WorkshopLocation = Gainsville
ElseIf varSeminarID = "070301Sar" Then
WorkshopLocation = Sarasota
ElseIf varSeminarID = "070302Tam" Then
WorkshopLocation = Tampa
End If
End Sub

....and if I were writing this I would use a Case block instead...

Private Sub SeminarID_AfterUpdate()
Select Case varSeminarID
Case "070226WMel"
WorkshopLocation = West_Melbourne
Case "070227Orl"
WorkshopLocation = Orlando
Case "070228Gai"
WorkshopLocation = Gainsville
Case "070301Sar"
WorkshopLocation = Sarasota
Case "070302Tam"
WorkshopLocation = Tampa
End If
End Sub
 
D

Daniel

You have a function place within a sub. You can't do that. You can either
have a sub or have a function. Or you can call a function within a sub. But
you cannot place a function within a sub.

Basically depending on your needs you either want

Private Sub SeminarID_AfterUpdate()

If varSeminarID = "070226WMel" Then
WorkshopLocation = West_Melbourne
ElseIf varSeminarID = "070227Orl" Then
WorkshopLocation = Orlando
ElseIf varSeminarID = "070228Gai" Then
WorkshopLocation = Gainsville
ElseIf varSeminarID = "070301Sar" Then
WorkshopLocation = Sarasota
ElseIf varSeminarID = "070302Tam" Then
WorkshopLocation = Tampa

End If
End Sub

OR

Function FindWorkshopLocation(varSeminarID As String) As String

If varSeminarID = "070226WMel" Then
WorkshopLocation = West_Melbourne
ElseIf varSeminarID = "070227Orl" Then
WorkshopLocation = Orlando
ElseIf varSeminarID = "070228Gai" Then
WorkshopLocation = Gainsville
ElseIf varSeminarID = "070301Sar" Then
WorkshopLocation = Sarasota
ElseIf varSeminarID = "070302Tam" Then
WorkshopLocation = Tampa

End If
End Function

OR

Private Sub SeminarID_AfterUpdate()

'I used "070226WMel" but you can pass whatever value you need
Call FindWorkshopLocation("070226WMel")

End Sub

Daniel
 
R

Rick Brandt

Rick Brandt said:
...and if I were writing this I would use a Case block instead...

Private Sub SeminarID_AfterUpdate()
Select Case varSeminarID
Case "070226WMel"
WorkshopLocation = West_Melbourne
Case "070227Orl"
WorkshopLocation = Orlando
Case "070228Gai"
WorkshopLocation = Gainsville
Case "070301Sar"
WorkshopLocation = Sarasota
Case "070302Tam"
WorkshopLocation = Tampa
End If
End Sub
Click to expand...

The final "End If" should be "End Select".
 
R

Rick Brandt

Daniel said:
Rick,

Is there a performance difference between Case and EndIf?
Click to expand...

On modern hardware the difference is probably miniscule, but the Case statement
only has to evaluate the value one time whereas a series of If-Then statements
has to evaluate the same value every time.
 
Top