Same Results - which is better?
 

Below are two snippets of code, both producing the same results.
Which is more elegant and proper to use and easiest to understand? (I
realize the limitations of nested IF's). Being relatively new to VBA
(this is my first attempt at code) - I'm curious to know what's more
acceptable.

Thanks,

-Tony

(code 1)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
If txt_ethy_flags = "B" Then
Worksheets("con1").Cells(134, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "D" Then
Worksheets("con1").Cells(135, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "E" Then
Worksheets("con1").Cells(136, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "J" Then
Worksheets("con1").Cells(137, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "U" Then
Worksheets("con1").Cells(138, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "UB" Then
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
ElseIf txt_ethy_flags = "JB" Then
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
Else
' do nothing
End If

(code 2)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
Select Case txt_ethy_flags
Case "B"
Worksheets("con1").Cells(134, 4).Value = txt_ethy_flags
Case "D"
Worksheets("con1").Cells(135, 4).Value = txt_ethy_flags
Case "E"
Worksheets("con1").Cells(136, 4).Value = txt_ethy_flags
Case "J"
Worksheets("con1").Cells(137, 4).Value = txt_ethy_flags
Case "U"
Worksheets("con1").Cells(138, 4).Value = txt_ethy_flags
Case "UB"
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
Case "JB"
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
End Select

Same Results - which is better?
 

Hi Tony,

There is no such thing as right and proper, just perspective and what works
(see many heated discussions on the topic in these NGs).

I personally prefer code 2 as it is morev elegant (IMO), and easier tgo
read.

It ca be simplfife further with

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
With Worksheets("con1")
.Cells(131, 4).Value = txt_ethy
.Range("D134:D138").ClearContents
Select Case txt_ethy_flags
Case "B"
.Cells(134, 4).Value = txt_ethy_flags
Case "D"
.Cells(135, 4).Value = txt_ethy_flags
Case "E"
.Cells(136, 4).Value = txt_ethy_flags
Case "J"
.Cells(137, 4).Value = txt_ethy_flags
Case "U"
.Cells(138, 4).Value = txt_ethy_flags
Case "UB"
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
.Cells(138, 4).Value = UFlag
.Cells(134, 4).Value = BFlag
Case "JB"
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
.Cells(134, 4).Value = BFlag
.Cells(137, 4).Value = JFlag
End Select


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony" wrote in message
om...
Below are two snippets of code, both producing the same results.
Which is more elegant and proper to use and easiest to understand? (I
realize the limitations of nested IF's). Being relatively new to VBA
(this is my first attempt at code) - I'm curious to know what's more
acceptable.

Thanks,

-Tony

(code 1)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
If txt_ethy_flags = "B" Then
Worksheets("con1").Cells(134, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "D" Then
Worksheets("con1").Cells(135, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "E" Then
Worksheets("con1").Cells(136, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "J" Then
Worksheets("con1").Cells(137, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "U" Then
Worksheets("con1").Cells(138, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "UB" Then
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
ElseIf txt_ethy_flags = "JB" Then
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
Else
' do nothing
End If

(code 2)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
Select Case txt_ethy_flags
Case "B"
Worksheets("con1").Cells(134, 4).Value = txt_ethy_flags
Case "D"
Worksheets("con1").Cells(135, 4).Value = txt_ethy_flags
Case "E"
Worksheets("con1").Cells(136, 4).Value = txt_ethy_flags
Case "J"
Worksheets("con1").Cells(137, 4).Value = txt_ethy_flags
Case "U"
Worksheets("con1").Cells(138, 4).Value = txt_ethy_flags
Case "UB"
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
Case "JB"
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
End Select

Same Results - which is better?
 

Hi Tony
I personally prefer Select Case (but this depends on personal taste).
In your case I'd additional use a worksheet object reference.
also always declare your variables and use Option Explicit

So you may try

option explicit
sub foo()
Dim wks as worksheet
Dim txt_ethy
Dim txt_ethy_flags
Dim UFlag
Dim BFlag
Dim JFlag

'[....]some code

Set wks = Worksheets("con1")

with wks
'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
.Cells(131, 4).Value = txt_ethy
.Range("D134:D138").ClearContents
Select Case txt_ethy_flags
Case "B"
.Cells(134, 4).Value = txt_ethy_flags
Case "D"
.Cells(135, 4).Value = txt_ethy_flags
Case "E"
.Cells(136, 4).Value = txt_ethy_flags
Case "J"
.Cells(137, 4).Value = txt_ethy_flags
Case "U"
.Cells(138, 4).Value = txt_ethy_flags
Case "UB"
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
.Cells(138, 4).Value = UFlag
.Cells(134, 4).Value = BFlag
Case "JB"
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
.Cells(134, 4).Value = BFlag
.Cells(137, 4).Value = JFlag
End Select
end with

'[...]some code
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


Tony wrote:
Below are two snippets of code, both producing the same results.
Which is more elegant and proper to use and easiest to understand? (I
realize the limitations of nested IF's). Being relatively new to VBA
(this is my first attempt at code) - I'm curious to know what's more
acceptable.

Thanks,

-Tony

(code 1)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
If txt_ethy_flags = "B" Then
Worksheets("con1").Cells(134, 4).Value =
txt_ethy_flags
ElseIf txt_ethy_flags = "D" Then
Worksheets("con1").Cells(135, 4).Value =
txt_ethy_flags
ElseIf txt_ethy_flags = "E" Then
Worksheets("con1").Cells(136, 4).Value =
txt_ethy_flags
ElseIf txt_ethy_flags = "J" Then
Worksheets("con1").Cells(137, 4).Value =
txt_ethy_flags
ElseIf txt_ethy_flags = "U" Then
Worksheets("con1").Cells(138, 4).Value =
txt_ethy_flags
ElseIf txt_ethy_flags = "UB" Then
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
ElseIf txt_ethy_flags = "JB" Then
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
Else
' do nothing
End If

(code 2)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
Select Case txt_ethy_flags
Case "B"
Worksheets("con1").Cells(134, 4).Value =
txt_ethy_flags
Case "D"
Worksheets("con1").Cells(135, 4).Value =
txt_ethy_flags
Case "E"
Worksheets("con1").Cells(136, 4).Value =
txt_ethy_flags
Case "J"
Worksheets("con1").Cells(137, 4).Value =
txt_ethy_flags
Case "U"
Worksheets("con1").Cells(138, 4).Value =
txt_ethy_flags
Case "UB"
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
Case "JB"
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
End Select

Same Results - which is better?
 

I found that your "Case" statements a little easier to follow.
Would combining a few lines help?

Sub Demo()
Const N As Double = 20801155080#
Dim t As Double

Dim txt_ethy_flags
txt_ethy_flags = "U" 'Pick a letter for testing

Select Case txt_ethy_flags
Case "B", "D", "E", "J", "U"
t = Asc(txt_ethy_flags) + 77
Worksheets("con1").Cells(N - t * Int(N / t), 4) = txt_ethy_flags
Case Else
'Your remaining code...
End Select
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Tony" wrote in message
om...
Below are two snippets of code, both producing the same results.
Which is more elegant and proper to use and easiest to understand? (I
realize the limitations of nested IF's). Being relatively new to VBA
(this is my first attempt at code) - I'm curious to know what's more
acceptable.

Thanks,

-Tony

(code 1)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
If txt_ethy_flags = "B" Then
Worksheets("con1").Cells(134, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "D" Then
Worksheets("con1").Cells(135, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "E" Then
Worksheets("con1").Cells(136, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "J" Then
Worksheets("con1").Cells(137, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "U" Then
Worksheets("con1").Cells(138, 4).Value = txt_ethy_flags
ElseIf txt_ethy_flags = "UB" Then
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
ElseIf txt_ethy_flags = "JB" Then
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
Else
' do nothing
End If

(code 2)

'ethyl round
txt_ethy = Application.WorksheetFunction.Round(txt_ethy, 2)
'ethyl ether flag
Worksheets("con1").Cells(131, 4).Value = txt_ethy
Worksheets("con1").Range("D134:D138").ClearContent s
Select Case txt_ethy_flags
Case "B"
Worksheets("con1").Cells(134, 4).Value = txt_ethy_flags
Case "D"
Worksheets("con1").Cells(135, 4).Value = txt_ethy_flags
Case "E"
Worksheets("con1").Cells(136, 4).Value = txt_ethy_flags
Case "J"
Worksheets("con1").Cells(137, 4).Value = txt_ethy_flags
Case "U"
Worksheets("con1").Cells(138, 4).Value = txt_ethy_flags
Case "UB"
UFlag = Trim(Mid(txt_ethy_flags, 1, 1))
BFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(138, 4).Value = UFlag
Worksheets("con1").Cells(134, 4).Value = BFlag
Case "JB"
BFlag = Trim(Mid(txt_ethy_flags, 1, 1))
JFlag = Trim(Mid(txt_ethy_flags, 2, 1))
Worksheets("con1").Cells(134, 4).Value = BFlag
Worksheets("con1").Cells(137, 4).Value = JFlag
End Select

Same Results - which is better?
 

Oops. Didn't look the other way. Again, just an idea.

Sub Demo()
Dim txt_ethy_flags
txt_ethy_flags = "B" 'Pick a letter for testing

Select Case txt_ethy_flags
Case "B", "D", "E", "J", "U"
Cells(134 + (492844 Mod (Asc(txt_ethy_flags) - 55)), 4) =
txt_ethy_flags
Case Else
'Your remaining code...
End Select
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Dana DeLouis" wrote in message
...
I found that your "Case" statements a little easier to follow.
Would combining a few lines help?

Sub Demo()
Const N As Double = 20801155080#
Dim t As Double

Dim txt_ethy_flags
txt_ethy_flags = "U" 'Pick a letter for testing

Select Case txt_ethy_flags
Case "B", "D", "E", "J", "U"
t = Asc(txt_ethy_flags) + 77
Worksheets("con1").Cells(N - t * Int(N / t), 4) = txt_ethy_flags
Case Else
'Your remaining code...
End Select
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Case "B"
Worksheets("con1").Cells(134, 4).Value = txt_ethy_flags
Case "D"
Worksheets("con1").Cells(135, 4).Value = txt_ethy_flags
Case "E"
Worksheets("con1").Cells(136, 4).Value = txt_ethy_flags
Case "J"
Worksheets("con1").Cells(137, 4).Value = txt_ethy_flags
Case "U"
Worksheets("con1").Cells(138, 4).Value = txt_ethy_flags

Same Results - which is better?
 

Thanks all. This is really great help... and I learned something in
between. I understood everyone's post except Dana's. Dana, I'll have
to study your response (it's a little advanced for me at this point,
but I'll comprehend it soon). I didn't know about the 'With'
statement, but I do now. Also, I like the cleaned up versions
everyone supplied. I knew my code was cumbersome, I knew it could be
shortened - thanks to all for the suggestions.

My next trick is that I have 28 instances of this code. One for each
different 'flag' I have identified in my routine. They all do the
same logic, but write to different cells in my worksheet... meaning
each 'flag' (B D E J U or a combo I strip and seperate down) each gets
its own specific cell. i.e. - txt_ethy_flag equaling "B" is not the
same as txt_meth_flag equaling "B". My quest is to make this into a
sub function so that it gets ran when needed, and not repeated for
each of the 28 instances... suggestions?

-Tony