IF Statement with any combination of 11 variables

[pgrad]

Hi All,

I have adapted a automated email code to send targetted email to my colleagues.

I have 11 variables which are displayed as either 1 or 0.

In the body of the email I want to add a bullet point and some standard text where a 1 is present. E.g If all 11 variables display a 1 then there will be 11 bullet points.

I am not sure if this is the best approach but so far all I can think of is a great big IF statement.

Any other suggestions would be greatefully receieved.

Thanks
Paul

 

[bertie]

You will have to check the value assigned to each variable but the body of an email is a text string which we can built up depending on this value.

In the example below I have coded for three variables. Output is to a message box.

[COLOR=darkblue]Sub[/COLOR] BuildTextString()
   [COLOR=green]'[/COLOR]
   'declare variables
   [COLOR=green]'[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] var01 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] var02 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] var03 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sEmailBody [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sBullet [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   
   sBullet = "#"
   var01 = 1
   var02 = 0
   var03 = 1
   
   [COLOR=green]'first variable[/COLOR]
   [COLOR=darkblue]If[/COLOR] var01 = 1 [COLOR=darkblue]Then[/COLOR]
      sEmailBody = sBullet & " " & "variable01"
   [COLOR=darkblue]Else[/COLOR]
      sEmailBody = "  " & "variable01"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   [COLOR=green]'second variable, etc[/COLOR]
   [COLOR=darkblue]If[/COLOR] var02 = 1 [COLOR=darkblue]Then[/COLOR]
      sEmailBody = sEmailBody & vbCrLf & _
         sBullet & " " & "variable01"
   [COLOR=darkblue]Else[/COLOR]
      sEmailBody = sEmailBody & vbCrLf & _
         "  " & "variable01"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   [COLOR=green]'third variable, etc[/COLOR]
   [COLOR=darkblue]If[/COLOR] var03 = 1 [COLOR=darkblue]Then[/COLOR]
      sEmailBody = sEmailBody & vbCrLf & _
         sBullet & " " & "variable01"
   [COLOR=darkblue]Else[/COLOR]
      sEmailBody = sEmailBody & vbCrLf & _
         "  " & "variable01"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   MsgBox sEmailBody
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[pgrad]

Bertie,

Thanks for the suggestion. Using your example I have managed to achieve what I set out to do.

And in a much more efficient way that writing 121 IF statements!!

Very usefull learning for me.

Have a great day!

Paul