If...Then...ElseIf written as a Select Case Statement

[Sue]

Sorry for the double post (I posted this to VBA Beginners) but I saw
there was a bit more activity over here.

Hello. Select Case Statements confuse me.

The scenario - In MS Word, I have a UserForm with a listing of choices
(AutoText) using OptionButtons. The choice will be inserted at a
bookmark.

I know it would be much more efficient to write the below as a Select
Case Statement. (There's 44 options.) Can someone get me started?

If UserForm1.OptionButton1.Value = True Then
With ActiveDocument
.AttachedTemplate.AutoTextEntries("atAtlanta").Insert
Where:=.Bookmarks("bmAddressBlock").Range, RichText:=True
UserForm1.Hide
UserForm2.Show
End With

ElseIf UserForm1.OptionButton2.Value = True Then
With ActiveDocument
.AttachedTemplate.AutoTextEntries("atBellevue").Insert
Where:=.Bookmarks("bmAddressBlock").Range, RichText:=True
UserForm1.Hide
UserForm2.Show
End With
End If

Thanks so much.
Sue Hunter

 

[Shauna Kelly]

Hi Sue

There are basically two rules:

1. You can always re-cast a Select Case / End Select block as an If / Else
If / End If block. But not vice versa.

2. Select Case assesses the value of *one* variable. The Select Case block
executes code depending on the value of that *one* variable.

So conceptually, it's like asking:

What day is it?
Monday?
'Do this stuff
Tuesday?
'Do this stuff
Wednesday?
'Wednesday stuff
Any other day
'Other stuff

Or

What's the cat's name?
Fred?
'Do this stuff
Spot?
'Do this other stuff
Felix?
'Do this
Any other cat's name
'Whatever

Or

How old is the cat?
<1
'Do kitten stuff
1 To 5
'Adult cat stuff

5

'Older cat stuff



An If statement and its subsequent Else If statements can assess different
variables. Conceptually, you could have

If it's Monday then
'Do this
Else If it's raining then
'Do something else
Else If the cat's name is Fred then
'Do this stuff
End If

That example probably wouldn't make for good quality code. But it shows that
the various If / Else If tests are assessing different variables.

So, in your case, you're assessing the value of 44 different variables. You
can't cast an If / End If block as a Select Case block if the If statements
assess more than one variable. So in your case, Select Case wouldn't apply.

The reason it seems like it should be possible to use a Select Case block is
that we humans might think:

Which button did the user click?
Button 1?
'Do this
Button 2?
'Do that
...
Button 44?
'Whatever

But that's not how VBA is thinking. It's got 44 separate option buttons.
There's no variable in a VBA UserForm that answers the "Which button did the
user click?' question. In practical terms, the only way to tell which option
button the user clicked is to use If / Else If / End If. So you may as well
use If / Else If / End If to start with!

I know it would be much more efficient

Depends on what you mean by "efficient". If you want to type less, then you
can always omit "= True" and tidy up the code a bit. If by "efficient" you
mean speed of execution, then there's evidence that a very long chain of If
/ Else If / Else If / .... / End If statements executes faster than the
equivalent Select Case block.

What might be better than what you have now is something like:

Dim sAutoTextToInsert as String

With UserForm1
If .OptionButton1.Value Then
sAutoTextToInsert = "atAtlanta"
ElseIf .OptionButton2.Value Then
sAutoTextToInsert = "atBellevue"
End If
End with

With ActiveDocument
.AttachedTemplate.AutoTextEntries(sAutoTextToInsert).Insert _
Where:=.Bookmarks("bmAddressBlock").Range, RichText:=True
End With

UserForm1.Hide
UserForm2.Show


Hope this helps. Write back if you need more info.

Cheers

Shauna

Shauna Kelly
http://www.shaunakelly.com/word

 

[That Guy]

I agree with most of what Shauna is saying but I feel there is one
more approach that can clean up the routine that is checking those
options but adds a lot more code for event handlers.

If you create a click event handler for each button (Since the are
options and not check boxes from what I can tell) that modifies a
single variable (like an integer) then you can have one select
statement that evaluates the results of all the options.

Take this overly simplified example:

Private theFlag as integer

Option1_Click() handles Option1.click
theFlag = 1
end sub

Option2_Click() handles Option2.click
theFlag = 2
end sub

Button1_Click() handles Button1.click

Select Case theFlag
case 1: 'Insert stuff here
case 2: 'Insert diffferent stuff here
end Select.

So basically you can have all 44 buttons handled in one routine it is
just that you need to get them to a central location before you do it.

Good luck,

Ian

 

[Sue]

Shauna - thank you for the great explanation in non-VBA terms. It
really helps.

<<If by "efficient" you mean speed of execution, then there's evidence
that a very long chain of If / Else If / Else If / .... / End If
statements executes faster than the
equivalent Select Case block. >>

Huh. That's interesting as I have always been told the opposite.

Sue

 

[Shauna Kelly]

Hi Sue

I'm glad it helped.

Speed of execution

In the end, it's an empirical question. If you've got code that's too slow
for the user to bear, try one way and time it, try the other and time it,
and choose the fastest. What's fastest in one circumstance is not
necessarily fastest in another.

Now, you are going to re-name all those option buttons and give them
meaningful names, aren't you<g>?

Cheers

Shauna

Shauna Kelly
http://www.shaunakelly.com/word


Shauna - thank you for the great explanation in non-VBA terms. It
really helps.

<<If by "efficient" you mean speed of execution, then there's evidence
that a very long chain of If / Else If / Else If / .... / End If
statements executes faster than the
equivalent Select Case block. >>

Huh. That's interesting as I have always been told the opposite.

Sue

 

[Shauna Kelly]

Hi That

I think Sue is using VBA, so the "Option1_Click() handles Option1.click"
syntax doesn't apply.

On the other hand, if Sue were not using VBA, you wouldn't create 44 option
buttons. You'd create one control array, and make most of the problem just
go away.

However, you're right, you could use VBA UserForm Click events and a
module-level variable. FWIW, I this is how I chose between these two ways to
do it:

1. A module-level variable is a more complex thing than a local variable.
It's more likely to be hit with unwanted side effects, and is harder to
maintain. All other things being equal (and they rarely are!), then I'd
choose a solution with a local variable rather than a module-level variable.
I'm not saying module-level variables are evil. Indeed, we couldn't live
without them. But simple is good.

2. I'd never use an Integer as a flag like this. The chance of getting those
flags wrong is extremely high. What happens when Sue decides, in 6 months'
time, to insert a new option button between current number 27 and 28? You
somehow have to remember what each number meant. Depending on the nature of
the change, you may need to re-number all the items from 28 to 44. If you
need a flag, use an Enum.

3. One way or another, Sue is going to end up with 44 somethings. 44 items
in an If / Else If structure, or 44 event handlers, or 44 something elses.
Any maintenance is going to require changing one or more of those 44 things.
I'd rather have *one* place in which they need to be changed. With an event
handler, a flag and then a separate routine to respond to the button click,
you have three things that need to be updated, or at least checked, each
time you make a change. That massively increases the chance of error, and
makes maintenance a much bigger job than it needs to be.

Cheers

Shauna

Shauna Kelly
http://www.shaunakelly.com/word

 

[Harold Druss]

Sorry for the double post (I posted this to VBA Beginners) but I saw
there was a bit more activity over here.

Hello. Select Case Statements confuse me.

The scenario - In MS Word, I have a UserForm with a listing of choices
(AutoText) using OptionButtons. The choice will be inserted at a
bookmark.

I know it would be much more efficient to write the below as a Select
Case Statement. (There's 44 options.) Can someone get me started?

If UserForm1.OptionButton1.Value = True Then
With ActiveDocument
.AttachedTemplate.AutoTextEntries("atAtlanta").Insert
Where:=.Bookmarks("bmAddressBlock").Range, RichText:=True
UserForm1.Hide
UserForm2.Show
End With

ElseIf UserForm1.OptionButton2.Value = True Then
With ActiveDocument
.AttachedTemplate.AutoTextEntries("atBellevue").Insert
Where:=.Bookmarks("bmAddressBlock").Range, RichText:=True
UserForm1.Hide
UserForm2.Show
End With
End If

Thanks so much.
Sue Hunter

Hi Sue
Here is another idea to add to Shauna's great tip.
A list box and a command button.
===============================================
Option Explicit
Dim l As Long

Private Sub CommandButton1_Click()
Select Case l
Case 0
Case 1
End Select

End Sub

Private Sub ListBox1_Click()

l = ListBox1.ListIndex

End Sub

Private Sub UserForm_Initialize()
With ListBox1
.AddItem "harry"
.AddItem "doreen"
End With
End Sub
===============================================

 

[Sue]

Harold - I considered a ListBox but felt with 44 options it would be
too much scrolling. I have my UserForm laid out with four columns of
11 options for users to choose from. We still have users that don't
know how to cut and paste (?!) which is beyond me - but that's who I
have to design for.

Thanks for the suggestion though!
Sue

 

[Sue]

<<Now, you are going to re-name all those option buttons and give them
meaningful names, aren't you<g>? >>

Of course Shauna! LOL

Thanks again!

Sue