calling a function from a sub

  • Thread starter Thread starter KRosier
  • Start date Start date
K

KRosier

I'm sure this is a very simple beginner question, but after searching MS
help, these boards, the internet and my VBA book, I am still not
connecting the dots. I am amazed by the infinite patience of you folks
that answer these questions and am extrememly grateful. So here goes...

I have a form with three controls - Cname, totalRP and and unbound text
box, rr_txt. totalRP is a number (long). I need to "read" that number
and have it translated into a realm rank (a string) and placed into the
unbound rr_txt control. I have constructed this select case sub and
placed it in the current event, where it works just fine.

Select Case totalRP

Case 0
rr_txt = "You have no realm points. Get out to the battlegrounds!"
Case 1 To 24
rr_txt = "RR1L1"
Case Is < 125
rr_txt = "RR1L2"
Case Is < 350
rr_txt = "RR1L3"
'....insert 100 other possible cases here
Case Else
rr_txt = "You have entered an invalid number"
End Select

End Sub

However, I would like to use this same sub in several different forms
and or reports. I believe the best option would be to place the code in
a public function and then call it with a private sub. If this isn't
the case, please point me in the right direction.

Once again, the select case function works fine (in the immediate
window) but I am at a loss on how to call it with a sub, or what that
sub should look like. Between my limited knowledge and syntax problems,
I am just not getting this. Any help would be very much appreciated.

Kathy

totalRP is, and always will be, the number that is passed into the function.

Function strRR(lngRPs As Long) As String
Select Case lngRPs
Case 1 To 24
strRR = "RR1L1"
Case Is < 125
strRR = "RR1L2"
Case Is < 350
strRR = "RR1L3"
'....insert 100 other possible cases here
Case Else
strRR = "You have entered an invalid number"
End Select
End Function
 
It looks like you've got everything set up fine, all you need is the call to
the function. You could put the following into the control source of the
unbound textbox in your form and see if it works (without the quotes):
"=strRR(Me.totalRP)"

Otherwise, you can call it from another sub with something like:
strRank=strRR(15)
or
MsgBox strRR(15)
 
Thanks for the reply Mark.

I tried strRR(me.totalRP) as the control source of the unbound box but
it returned #Name in the form.

So with your nudge in the right direction, I tried
rr_txt = strRR(Me.totalRP) in the Current event of the form and it works
like a charm. Thanks for the help!

Kathy
 
I'm glad you got it to work (at least the VBA way). Now that I re-read my
suggestion, I think the control source approach might be:
"=strRR([totalRP])". I was mixing the VBA reference "Me.totalRP" in the
control source, which as you found out, doesn't work.
 
Back
Top