Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Same Results - which is better?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,885
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 690
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 690
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
+ or - % Results mahlandj Excel Worksheet Functions 4 March 4th 10 12:15 AM
IF/THEN Results bgrayduck Excel Discussion (Misc queries) 1 May 28th 09 04:08 PM
More MIN() results..? Mac Excel Worksheet Functions 1 October 16th 07 03:52 PM
Conditional Sum Argument results do not equal cell results Excel Randy R Mullins Excel Worksheet Functions 3 August 9th 06 07:16 PM
How can I list the results of my macro without overwritng previous results? mattip Excel Programming 3 November 28th 03 03:45 AM


All times are GMT +1. The time now is 01:57 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement