By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,631 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

multiple ifs converted to case statement in vb function

P: n/a
have code below:

Function ClassColl(strClass As Variant, strColl As Variant) As String

' Comments :
' Parameters : strClass
' strColl
' Returns : String Description
' Created : 2-5-2004 - Mark Mullins
' Modified :
'
' --------------------------------------------------------

On Error GoTo PROC_ERR

'declare variables
Dim class As String
Dim coll As String

class = UCase(CStr(strClass))
coll = UCase(CStr(strColl))

' 1-4 Family Residential

' Investement Real Estate
If class = "1A" And (coll = "83" Or coll = "84") Then
ClassColl = "1-4 Family Residential*Construction!Investment
RE"
ElseIf class = "C1" And (coll = "83" Or coll = "84" Or coll =
"90") Then
ClassColl = "1-4 Family Residential*Non
Construction!Investment RE"
ElseIf class = "C2" And (coll = "83" Or coll = "84" Or coll =
"90") Then
ClassColl = "1-4 Family Residential*Non
Construction!Investment RE"
ElseIf class = "C3" And (coll = "83" Or coll = "84" Or coll =
"90") Then
ClassColl = "1-4 Family Residential*Non
Construction!Investment RE"

I would like to convert this to a select case statement as there are
many more comparisons to do... can someone explain the use of a case
statement in this scenario... that is, if statement with an and
clause?

Thanks!

Mark Mullins
Union Planters
ma**********@upbna.com
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
rkc

"Mark Mullins" <ma**********@upbna.com> wrote in message
news:6b**************************@posting.google.c om...
have code below:

Function ClassColl(strClass As Variant, strColl As Variant) As String

' Comments :
' Parameters : strClass
' strColl
' Returns : String Description
' Created : 2-5-2004 - Mark Mullins
' Modified :
'
' --------------------------------------------------------


Do you really want to maintain all that information in your source code?
Why not use the power of SQL and your database?


Nov 12 '05 #2

P: n/a
On 9 Feb 2004 08:56:48 -0800, Mark Mullins wrote:
have code below:

Function ClassColl(strClass As Variant, strColl As Variant) As String

' Comments :
' Parameters : strClass
' strColl
' Returns : String Description
' Created : 2-5-2004 - Mark Mullins
' Modified :
'
' --------------------------------------------------------

On Error GoTo PROC_ERR

'declare variables
Dim class As String
Dim coll As String

class = UCase(CStr(strClass))
coll = UCase(CStr(strColl))

' 1-4 Family Residential

' Investement Real Estate
If class = "1A" And (coll = "83" Or coll = "84") Then
ClassColl = "1-4 Family Residential*Construction!Investment
RE"
ElseIf class = "C1" And (coll = "83" Or coll = "84" Or coll =
"90") Then
ClassColl = "1-4 Family Residential*Non
Construction!Investment RE"
ElseIf class = "C2" And (coll = "83" Or coll = "84" Or coll =
"90") Then
ClassColl = "1-4 Family Residential*Non
Construction!Investment RE"
ElseIf class = "C3" And (coll = "83" Or coll = "84" Or coll =
"90") Then
ClassColl = "1-4 Family Residential*Non
Construction!Investment RE"

I would like to convert this to a select case statement as there are
many more comparisons to do... can someone explain the use of a case
statement in this scenario... that is, if statement with an and
clause?

Thanks!

Mark Mullins
Union Planters
ma**********@upbna.com


Few notes:
- If your arguments are to be strings (you have use the common name
convention for string) then declare them as strings. Variants use up far
more resources.
- I would have thought "Class" was a reserved word. You may come into
problems with this. Use a better var name.
- I don't think enough info about all the possible choices are here to help
with the exact structure. So far, only "1A" can have be a "1-4 Family
Residential*Construction!Investment RE" and all others having 83,84 or 90
are the same, so that makes the cases very short. But this should be a good
enough guidline.

Function ClassColl(strClass As String, strColl As String) As String

' Comments :
' Parameters : strClass
' strColl
' Returns : String Description
' Created : 2-5-2004 - Mark Mullins
' Modified :
'
' --------------------------------------------------------

On Error GoTo PROC_ERR

strClass = UCase(strClass)
strColl = UCase(strColl)

' 1-4 Family Residential

' Investement Real Estate
Select Case strColl
Case "83", "84"
Select Case strClass
Case "1A"
ClassColl = "1-4 Family Residential*Construction!Investment RE"
Case Else
ClassColl = "1-4 Family Residential*NonConstruction!Investment RE"
End Select
Case "90"
ClassColl = "1-4 Family Residential*NonConstruction!Investment RE"
End Select
--
Mike Storr
veraccess.com
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.