Solved: Better code again

[gibbo1715]

All

Please tell me if im asking to many questions here but as before im looking for a better way of coding the following and feel sure you will know one

Cheers

Gibbo

[VBA] If UserForm1.OptionButton1.Value = True Then
mTo = "GateKeeper@Bmth"
End If
'East
If UserForm1.OptionButton2.Value = True Then
mTo = "GateKeeper@Eastern"
End If
'Poole
If UserForm1.OptionButton3.Value = True Then
mTo = "GateKeeper@Poole"
End If
'West
If UserForm1.OptionButton4.Value = True Then
mTo = "GateKeeper@Western"
End If
'HQCID
If UserForm1.OptionButton5.Value = True Then
mTo = GateKeeper@center
End If [/VBA]

[Killian]

I often find the Tag property of controls useful
You can easily loop through all the controls on a form and test them by type (or use a naming convention for groups of control and test for that) to get the ones you want.
Then use the tag property to contain the value for that control
So, for your example, first set the mTo value for each OptionButton in its Tag property at design time, then run this on the event to get the value[VBA]Dim ctrl As Control

For Each ctrl In UserForm1.Controls
If ctrl.Tag <> "" Then
If ctrl Then
mTo = ctrl.Tag
Exit For
End If
End If
Next[/VBA]

[Tommy]

No not too many questions. Gotta ask to know

You could use esleif here and reduce the number of lines. The "revised" code I am posting will accept only one answer. There fore there is a possibility of mTo = nothing/ Empty
[VBA]
If UserForm1.OptionButton1.Value = True Then
mTo = "GateKeeper@Bmth"
'East
ElseIf UserForm1.OptionButton2.Value = True Then
mTo = "GateKeeper@Eastern"
'Poole
ElseIf UserForm1.OptionButton3.Value = True Then
mTo = "GateKeeper@Poole"
'West
ElseIf UserForm1.OptionButton4.Value = True Then
mTo = "GateKeeper@Western"
'HQCID
ElseIf UserForm1.OptionButton5.Value = True Then
mTo = GateKeeper@center
End If
[/VBA]
HTH

[TonyJollans]

There aren't too many questions

Although I'm quite sure that they have interpreted correctly, neither Killian's nor Tommy's code necessarily does exactly the same as yours!

I prefer, usually to use Case statements for multiple choices - it always seems neater to me:[vba]With UserForm1
Select Case True
Case .OptionButton5.Value: mTo = "GateKeeper@center" 'HQCID
Case .OptionButton4.Value: mTo = "GateKeeper@Western" 'West
Case .OptionButton3.Value: mTo = "GateKeeper@Poole" 'Poole
Case .OptionButton2.Value: mTo = "GateKeeper@Eastern" 'East
Case .OptionButton1.Value: mTo = "GateKeeper@Bmth"
End Select
End With[/vba]

[XL-Dennis]

In their book Professional Excel Development Stephen Bull et al claim that the Elseif approach is better then the Case-approach.

I believe it refer to larger evaluation processes then this as I've not been able to verify it for small populations.

Gibbo - In my opinion there exist not too much questions but it sure exist too much stupid answers

Kind regards,
Dennis

[gibbo1715]

Thanks all lots of different appraoches all improvements on my inital method so im gonna mark this as solved

Gibbo

[TonyJollans]

Interesting, Dennis.

Of course it doesn't matter in this case but I might try and run some tests to see. I can't imagine there's a huge difference though.

[Tommy]

Whoa!!

I made a userform in Excel and in VB the form has 3 labels label1 etc, textbox, and command button. That is it. in the text box I entered 26 for both applications/macros. these are the results, I would be interested to know the results from someone else. Or even if I did not setup and run the test correctly, it seems way off to me LOL
[VBA]
Private Sub CommandButton1_Click()
Dim mStart#, mStop#, mI&, mStartr#, mStopr#
mStart = Timer
For mI = 1 To 50000
CaseSelect
Next
mStop = Timer
Label1.Caption = Format(mStop - mStart, "0.00000") '<-0.4 in Excel VBA!! 0.8 in VB
mStartr = Timer
For mI = 1 To 50000
suElseif
Next
mStopr = Timer
Label2.Caption = Format(mStopr - mStartr, "0.00000") '<-2.4 in Excel VBA!! 8.1 in VB
End Sub
Sub CaseSelect()
Select Case TextBox1.Text
Case "1"
Label3.Caption = TextBox1.Text
Case "2"
Label3.Caption = TextBox1.Text
Case "3"
Label3.Caption = TextBox1.Text
Case "4"
Label3.Caption = TextBox1.Text
Case "5"
Label3.Caption = TextBox1.Text
Case "6"
Label3.Caption = TextBox1.Text
Case "7"
Label3.Caption = TextBox1.Text
Case "8"
Label3.Caption = TextBox1.Text
Case "9"
Label3.Caption = TextBox1.Text
Case "10"
Label3.Caption = TextBox1.Text
Case "11"
Label3.Caption = TextBox1.Text
Case "12"
Label3.Caption = TextBox1.Text
Case "13"
Label3.Caption = TextBox1.Text
Case "14"
Label3.Caption = TextBox1.Text
Case "15"
Label3.Caption = TextBox1.Text
Case "16"
Label3.Caption = TextBox1.Text
Case "17"
Label3.Caption = TextBox1.Text
Case "18"
Label3.Caption = TextBox1.Text
Case "19"
Label3.Caption = TextBox1.Text
Case "20"
Label3.Caption = TextBox1.Text
Case "21"
Label3.Caption = TextBox1.Text
Case "22"
Label3.Caption = TextBox1.Text
Case "23"
Label3.Caption = TextBox1.Text
Case "24"
Label3.Caption = TextBox1.Text
Case "25"
Label3.Caption = TextBox1.Text
Case "26"
Label3.Caption = TextBox1.Text
Case "27"
Label3.Caption = TextBox1.Text
Case "28"
Label3.Caption = TextBox1.Text
Case "29"
Label3.Caption = TextBox1.Text
End Select
End Sub
Sub suElseif()
If TextBox1.Text = "1" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "2" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "3" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "4" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "5" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "6" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "7" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "8" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "9" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "10" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "11" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "12" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "13" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "14" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "15" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "16" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "17" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "18" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "19" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "20" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "21" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "22" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "23" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "24" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "25" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "26" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "27" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "28" Then
Label3.Caption = TextBox1.Text
ElseIf TextBox1.Text = "29" Then
Label3.Caption = TextBox1.Text
End If
End Sub

[/VBA]

[Tommy]

When I enter a 6 in the textbox I get 0.3 for select and 0.6 for elseif
When I enter a 26 in the textbox I get 0.4 for select and 2.4 for elseif
When I enter a 1 in the textbox I get 0.28 for select and 0.266 for elseif

So in my testing Case Select is faster. Elseif gets faster with the fewer checks, but still slower than select case. I wonder if I set up a false condition, or a numeric check instead of a string check if there would be a difference.

[Norie]

Tommy

What's the actual question?

Is it connected to the original post?

Do you want to optimize that code?

Why do you have Select Case/Ifs when you are doing exactly the same thing no matter what the value of Textbox1?

[Tommy]

Norie I was just running a test on select case vs if elseif I strayed because of XL-Dennis's post sorry.
I also found that if the tests are numeric if elseif is faster but by 0.1 only, oh well enough fun for one day.

[Norie]

Tommy

I only realised what you were doing after your 2nd post.

When I first saw the code, without actually reading it fully I just though whoa, what's going on here.

I was just about to copy it and then repost with a 'better' version.

[fumei]

The difference is simply a result of the number of characters that must be run through the parser, PLUS the need to check the value of the object property.

ElseIf TextBox1.Text = "12" - this is 27 characters (total in code 2,201 characters)

Case "12" - this is 9 characters (total in code 1,572 characters)

Further, if the input = 27, then the code must parse and RUN:

ElseIf TextBox1.Text = "1" (need to check the value)
ElseIf TextBox1.Text = "2" (need to check the value)
ElseIf TextBox1.Text = "3" (need to check the value)
etc
etc
whereby it must check the value of Textbox1.Text EACH TIME, until it reaches "27"

as opposed to:

Case "1"
etc
etc
whereby it needs to only check the value of Textbox1.Text ONCE - which is passed to the Select.

Between the need to check the value so many times, AND the actual character count by the parser, Select generally will run faster. As you have found out.

It helps to think about what the code is actually doing. In the case of ElseIf check value, each individual value must be formally checked and logic applied to it. That is, ElseIf applies a SINGLE logic statement against multiple inputs. Select Case on the other hand applies MULTIPLE logic statements against a single input.

While the end result may be similar - the actual processing, and the allocation of memory address, is quite different. ElseIf requires memory addresses for each instruction (eg. ElseIf Textbox1.Text = "23"), while Select Case requires ONE memory address (for Textbox1.Text) with the Select Case instructions applying logic ByRef. The ElseIf instructions apply logic ByVal - which creates a different memory address.

[TonyJollans]

Interesting.

I have to disagree with Gerry about the number of characters being significant - its effect is a one-off at compile time, not at run time.

Apart from that I got very similar results to Tommy - except I must have a slower machine

When I enter a 6 in the textbox I get 0.734 for select and 1.75 for elseif
When I enter a 26 in the textbox I get 0.984 for select and 6.61 for elseif
When I enter a 1 in the textbox I get 0.75 for select and 0.75 for elseif

I thought that repeated evaluation of the textbox object would be a significant factor so I changed the suElseif procedure to use a With ..[vba]Sub suElseif()
With TextBox1
If .Text = "1" Then
Label3.Caption = .Text
ElseIf .Text = "2" Then
Label3.Caption = .Text
ElseIf .Text = "3" Then
Label3.Caption = .Text
' :
' :
' :
ElseIf .Text = "29" Then
Label3.Caption = .Text
End If
End With
End Sub [/vba]

This gave these (surprisingly little changed) results

When I enter a 6 in the textbox I get 0.75 for select and 1.547 for elseif
When I enter a 26 in the textbox I get 0.921 for select and 5.141 for elseif
When I enter a 1 in the textbox I get 0.719 for select and 0.656 for elseif

To level the playing field I also added a With Textbox1 to the Case construct and this gave these results

When I enter a 6 in the textbox I get 0.734 for select and 1.531 for elseif
When I enter a 26 in the textbox I get 0.875 for select and 5.218 for elseif
When I enter a 1 in the textbox I get 0.672 for select and 0.672 for elseif

I don't see anything obviously wrong in the test so the implication does seem to be that case is significantly faster - contrary to Messrs Bullen et al.

I seem to remember last time we had a discussion on relative performance we found that our tests disagreed with the referenced material. I don't know whether this book quotes any tests or just baldly states the fact and there may be many factors involved.

[fumei]

Actually....uh...Tony is correct about the characters. I have been doing some tests myself. It IS a one off.

However, I stand by my explanation of why Case is (generally) faster than ElseIf. It really IS a factor of ByRef vs ByVal, and the fact that ElseIf requires more uses of memory allocation - even with the With.

My results:
When I enter a 6 in the textbox I get 0.281 for select and 0.657 for elseif
When I enter a 26 in the textbox I get 0.360 for select and 2.234 for elseif
When I enter a 1 in the textbox I get 0.266 for select and 0.265 for elseif

Just as a curiousity, I changed the Case to:
[vba]Case 1 To 26
Label3.Caption = Textbox1.Text[/vba]

Because actually the result instruction is the same, regardless of what Textbox1.Text is. The value of it always goes to the label.

Input 6 - Select = 0.151; ElseIf = 0.656
Input 26 - Select = 0.296; ElseIf = 2.235
Input 1 - Select = 0.297; ElseIf - 0.250

Here is something I find rather odd.
Input 1 - Select = 0.297; ElseIf - 0.250
Input 2 - Select = 0.297; ElseIf - 0.344
Input 3 - Select = 0.125; ElseIf - 0.422
Input 4 - Select = 0.151; ElseIf - 0.484
Input 5 - Select = 0.151; ElseIf - 0.562
Input 6 - Select = 0.151; ElseIf - 0.656
Input 7 - Select = 0.140; ElseIf - 0.735

I have run a number of these. I can not figure out why 1 and 2 are consistently higher (double!) than say 3 or 5, or 6 or 8. One would think they would be the same. They are integers just like the others.

Any thoughts?

[MOS MASTER]

Very interesting topic guys!

I myself like to use the same structure as Killian proposed. Looping the controls and examine only those controls that are of a specific type.

Why is simple, normally I have to check more then only a controls value (The tag for instance as K proposed) and therefore using a control loop can give you a speedy structure to retrieve all the data in one loop. (With nested if or select case structures of course)

But if you only need to retrieve the value (and only that) I have to agree on you all that Select Case is the fastest (and most readable IMO).

But like always I love the way we test out stuff over here....You'll provide so much fresh content to this site!

Later guys.