VBA GoTo Hell
-
I found this piece of VeryBAd VBA code in an Excel spreadsheet I was amending today:
For counter = avgfirstrow To avglastrow cellvalue = Sheets(1).Cells(counter, 3).Value If cellvalue < 0 Then GoTo ignore If cellvalue > 99 Then GoTo ignore totalsum = totalsum + cellvalue GoTo nextone ignore: entries = entries - 1 nextone: NextI can assure you my left eyebrow is still twitching. Gibber gibber.... :wtf: My only guess is that Satan must have been whispering into my colleague's ear at the time.
You always pass failure on the way to success.
-
I found this piece of VeryBAd VBA code in an Excel spreadsheet I was amending today:
For counter = avgfirstrow To avglastrow cellvalue = Sheets(1).Cells(counter, 3).Value If cellvalue < 0 Then GoTo ignore If cellvalue > 99 Then GoTo ignore totalsum = totalsum + cellvalue GoTo nextone ignore: entries = entries - 1 nextone: NextI can assure you my left eyebrow is still twitching. Gibber gibber.... :wtf: My only guess is that Satan must have been whispering into my colleague's ear at the time.
You always pass failure on the way to success.
Is your anger mainly directed to VBA or to your colleague's code? What if your colleague wrote the following instead?
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 Or cellvalue > 99 Then entries = entries - 1
Else totalsum = totalsum + cellvalue
NextThe reason I ask is many people seem to be happy with denouncing anything with VB in it, not realizing that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#. Thanks.
-
Is your anger mainly directed to VBA or to your colleague's code? What if your colleague wrote the following instead?
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 Or cellvalue > 99 Then entries = entries - 1
Else totalsum = totalsum + cellvalue
NextThe reason I ask is many people seem to be happy with denouncing anything with VB in it, not realizing that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#. Thanks.
Xiangyang Liu wrote:
Is your anger mainly directed to VBA or to your colleague's code?
My colleagues lack of coding proficiency(What a Scrooge I am and it's Chistmas tomorrow). Unfortunately I have not been supervising his coding enough. It's a tricky area as happy coders make better coders - so I try to have a light touch when it comes to others code. Although I do like to have a good old moan on CodeProject occasionally.
Xiangyang Liu wrote:
it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#.
I agree. My experience is that VBA encourages bad techniques and habits. To code in C# you have to have a relatively good understanding of programming methodology(am I wrong?). You can throw anyone at VBA and it will throw something back up.
Xiangyang Liu wrote:
What if your colleague wrote the following instead?
I would have had nothing to complain about except for the formatting(there I go again). Merry Christmas :) Guy
You always pass failure on the way to success.
-
Is your anger mainly directed to VBA or to your colleague's code? What if your colleague wrote the following instead?
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 Or cellvalue > 99 Then entries = entries - 1
Else totalsum = totalsum + cellvalue
NextThe reason I ask is many people seem to be happy with denouncing anything with VB in it, not realizing that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#. Thanks.
I agree with you. Is the average VB programmer who brought discredit on the language. :)
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler. -- Alfonso the Wise, 13th Century King of Castile.
[my articles] -
Is your anger mainly directed to VBA or to your colleague's code? What if your colleague wrote the following instead?
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 Or cellvalue > 99 Then entries = entries - 1
Else totalsum = totalsum + cellvalue
NextThe reason I ask is many people seem to be happy with denouncing anything with VB in it, not realizing that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#. Thanks.
"Omit needless local variables." -- Strunk... if he'd taught programming (
cellvaluein this case) -
"Omit needless local variables." -- Strunk... if he'd taught programming (
cellvaluein this case)Thanks - I missed that one as well. Merry Christmas :)
You always pass failure on the way to success.
-
Is your anger mainly directed to VBA or to your colleague's code? What if your colleague wrote the following instead?
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 Or cellvalue > 99 Then entries = entries - 1
Else totalsum = totalsum + cellvalue
NextThe reason I ask is many people seem to be happy with denouncing anything with VB in it, not realizing that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#. Thanks.
Xiangyang Liu wrote:
that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#.
But it is easier to write bad code in C++/C#, than it is to write good code in VB/VBA! ;P
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
My first real C# project | Linkify!| FoldWithUs! | sighist -
Is your anger mainly directed to VBA or to your colleague's code? What if your colleague wrote the following instead?
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 Or cellvalue > 99 Then entries = entries - 1
Else totalsum = totalsum + cellvalue
NextThe reason I ask is many people seem to be happy with denouncing anything with VB in it, not realizing that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#. Thanks.
Your code is in error. You cannot have the "Else" clause on a separate line if the "Then" clause is on the same line as the condition.
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 or cellvalue > 99 Then
entries = entries - 1
Else
totalsum = totalsum + cellvalue
End If
Next -
"Omit needless local variables." -- Strunk... if he'd taught programming (
cellvaluein this case)Actually his usage of a private variable was correct, because otherwords he would have to keep drilling down the object model to get the value in each conditional, and it's better to incur the performance overhead of allocating a new variable once than to keep traversing the object model more than once in a loop like that. "Real" VB (which is NOT the same as VBA) gives you the With statement which prevents the need for this additional variable. What he did actually makes the code more readable as well. Keep in mind that VBA is meant for simple macros, so there is really no horror in what he did, even with macros. Gotos are considered bad because they lead to spaghetti code smells in applications that have grown beyond a particular size, but used in a small macro situation they can greatly enhance readability if used properly. I'm not trying to wave the VB banner, I actually bailed on it quite some time ago only using it when I have to, simply because most good developers chose to focus on C#, thus it's easier to go with the flow and I like C-like syntaxes. But most VB-bigots are not as smart as they think they are and would feel quite stupid for making blanket statements about VB if they knew all the facts. I've seen really amazing, robust, well-performing apps written in both VB6 and VB.Net, and VB itself incorporates many elements of Pascal which is an excellent language. I do agree that the relative simplicity of getting started with VB led to an influx of bad programmers (many of which got flushed out in the dot com bust), or people who were uneducated in formal CS concepts or simply were not technical or motivated enough to make a career committment to good programming, but at the same time I've worked with some real idiots whose background was in Java or C++. I always thought Microsft screwed up by keeping the whole "BASIC" moniker... when they came out with .Net they should have just named it B++ or B# or something that made more sense, because despite a few syntax differences it is much closer to C# than it is to the original BASIC.
-
I found this piece of VeryBAd VBA code in an Excel spreadsheet I was amending today:
For counter = avgfirstrow To avglastrow cellvalue = Sheets(1).Cells(counter, 3).Value If cellvalue < 0 Then GoTo ignore If cellvalue > 99 Then GoTo ignore totalsum = totalsum + cellvalue GoTo nextone ignore: entries = entries - 1 nextone: NextI can assure you my left eyebrow is still twitching. Gibber gibber.... :wtf: My only guess is that Satan must have been whispering into my colleague's ear at the time.
You always pass failure on the way to success.
Yikes! Why not put
entries = entries - 1afterThen:eek: Also just have oneifstatement :wtf:"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Actually his usage of a private variable was correct, because otherwords he would have to keep drilling down the object model to get the value in each conditional, and it's better to incur the performance overhead of allocating a new variable once than to keep traversing the object model more than once in a loop like that. "Real" VB (which is NOT the same as VBA) gives you the With statement which prevents the need for this additional variable. What he did actually makes the code more readable as well. Keep in mind that VBA is meant for simple macros, so there is really no horror in what he did, even with macros. Gotos are considered bad because they lead to spaghetti code smells in applications that have grown beyond a particular size, but used in a small macro situation they can greatly enhance readability if used properly. I'm not trying to wave the VB banner, I actually bailed on it quite some time ago only using it when I have to, simply because most good developers chose to focus on C#, thus it's easier to go with the flow and I like C-like syntaxes. But most VB-bigots are not as smart as they think they are and would feel quite stupid for making blanket statements about VB if they knew all the facts. I've seen really amazing, robust, well-performing apps written in both VB6 and VB.Net, and VB itself incorporates many elements of Pascal which is an excellent language. I do agree that the relative simplicity of getting started with VB led to an influx of bad programmers (many of which got flushed out in the dot com bust), or people who were uneducated in formal CS concepts or simply were not technical or motivated enough to make a career committment to good programming, but at the same time I've worked with some real idiots whose background was in Java or C++. I always thought Microsft screwed up by keeping the whole "BASIC" moniker... when they came out with .Net they should have just named it B++ or B# or something that made more sense, because despite a few syntax differences it is much closer to C# than it is to the original BASIC.
Tipster wrote:
Keep in mind that VBA is meant for simple macros
Not in my programming experience. I have had to use VBA for some fairly sophisticated image manipulation in Word as well as using it extensively in Access and Excel.
Tipster wrote:
Gotos are considered bad... but used in a small macro situation they can greatly enhance readability if used properly
I'd like to see an example of that. Ok I have used goto's (Oh I know the shame of it :sigh:) but really they should never be used if there is a better alternative. When I was at University the only place a goto was ever allowed was in certain situations in COBOL. I was taught to program properly through PASCAL and I think many people new to programming would benefit from this sort of education - i.e. structured programming. Alright - rant over ;)
You always pass failure on the way to success.
-
Is your anger mainly directed to VBA or to your colleague's code? What if your colleague wrote the following instead?
For counter = avgfirstrow To avglastrow
cellvalue = Sheets(1).Cells(counter, 3).Value
If cellvalue < 0 Or cellvalue > 99 Then entries = entries - 1
Else totalsum = totalsum + cellvalue
NextThe reason I ask is many people seem to be happy with denouncing anything with VB in it, not realizing that it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#. Thanks.
Where are the Goto statements in C++/C#??
Matt
(Find your own niche! This one's mine.) -
I found this piece of VeryBAd VBA code in an Excel spreadsheet I was amending today:
For counter = avgfirstrow To avglastrow cellvalue = Sheets(1).Cells(counter, 3).Value If cellvalue < 0 Then GoTo ignore If cellvalue > 99 Then GoTo ignore totalsum = totalsum + cellvalue GoTo nextone ignore: entries = entries - 1 nextone: NextI can assure you my left eyebrow is still twitching. Gibber gibber.... :wtf: My only guess is that Satan must have been whispering into my colleague's ear at the time.
You always pass failure on the way to success.
Hmmm, that's pretty good compared to some of the code I've had to deal with :laugh:
GuyThiebaut wrote:
I found this piece of VeryBAd VBA code in an Excel spreadsheet I was amending today: For counter = avgfirstrow To avglastrow cellvalue = Sheets(1).Cells(counter, 3).Value If cellvalue < 0 Then GoTo ignore If cellvalue > 99 Then GoTo ignore totalsum = totalsum + cellvalue GoTo nextoneignore: entries = entries - 1nextone:Next I can assure you my left eyebrow is still twitching. Gibber gibber.... My only guess is that Satan must have been whispering into my colleague's ear at the time.
-
Hmmm, that's pretty good compared to some of the code I've had to deal with :laugh:
GuyThiebaut wrote:
I found this piece of VeryBAd VBA code in an Excel spreadsheet I was amending today: For counter = avgfirstrow To avglastrow cellvalue = Sheets(1).Cells(counter, 3).Value If cellvalue < 0 Then GoTo ignore If cellvalue > 99 Then GoTo ignore totalsum = totalsum + cellvalue GoTo nextoneignore: entries = entries - 1nextone:Next I can assure you my left eyebrow is still twitching. Gibber gibber.... My only guess is that Satan must have been whispering into my colleague's ear at the time.
I guess it makes sense, and works, in it's own eccentric manner.:~
You always pass failure on the way to success.
-
Where are the Goto statements in C++/C#??
Matt
(Find your own niche! This one's mine.)Matt Sollars wrote:
Where are the Goto statements in C++/C#??
Surely that is an oxymoron ;)
You always pass failure on the way to success.
-
Matt Sollars wrote:
Where are the Goto statements in C++/C#??
Surely that is an oxymoron ;)
You always pass failure on the way to success.
;)
Matt
(Find your own niche! This one's mine.) -
Xiangyang Liu wrote:
Is your anger mainly directed to VBA or to your colleague's code?
My colleagues lack of coding proficiency(What a Scrooge I am and it's Chistmas tomorrow). Unfortunately I have not been supervising his coding enough. It's a tricky area as happy coders make better coders - so I try to have a light touch when it comes to others code. Although I do like to have a good old moan on CodeProject occasionally.
Xiangyang Liu wrote:
it is possible to write good code in VB/VBA, just like it is possible to write bad code in C++/C#.
I agree. My experience is that VBA encourages bad techniques and habits. To code in C# you have to have a relatively good understanding of programming methodology(am I wrong?). You can throw anyone at VBA and it will throw something back up.
Xiangyang Liu wrote:
What if your colleague wrote the following instead?
I would have had nothing to complain about except for the formatting(there I go again). Merry Christmas :) Guy
You always pass failure on the way to success.
GuyThiebaut wrote:
To code in C# you have to have a relatively good understanding of programming methodology(am I wrong?).
True. VB family supports unstructured error handling like
On Error Resume Nextbut such constructs are totally not supported in C#.Vasudevan Deepak Kumar Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson