I just came across a coding example in this forum which demonstrated something I have never understood about a certain programming style, and I'd like to get a conversation going here as to why it is so.
The code I am talking about is as follows:
strSQL="Delete * From tblTable;"
Why is it not just:
docmd.execute "Delete * From tblTable;"
I see this all the time and I have yet to understand why people code like this.
Why use up memory to store the strSQL variable?
To me, the code is not cleaner or clearer. In fact I find it more abstract, more difficult to read--you've, at least, got to read twice as much of it.
I notice that when someone uses one of the Access wizards, they generate verbose code like this. For a message box, for every possible option of the message box, the wizard sets up a variable, assigns it, then uses that variable in the message box statement. This style of programming uses 10-15 lines of code to accomplish what should be done in a single line. Aside from the memory, the VBA interpreter has to chug through 10-15 times more than it would have had to with one line.
Are these programming style habits picked up from wizards?
I'm sorry to put anyone on the spot here, but I think this is a serious issue.
The reason why me (a computer expert in an Internet Developer firm in Finland, mostly ASP and Visual Basic -> Access) and many others code like this is the fact that with more complex programs one often needs more complex SQL-queries. I'm using a 19" monitor with 1280 x 960, and I have many queries in components that are splitted into five or even six rows because of their length, two to five InnerJoins etc. IMHO it would be quite frustrating to scroll every time a long way sideways... Sometimes, and in my case very often, the SQL-query needs to be built up based on some variables and conditions, in which case this is the only possible solution. Also debuggin through MsgBox and Clipboard.SetText is very easy in the case something goes wrong and one doesn't know what it is and where it is, then it is advisable to check the SQL-query first. Using Clipboard.SetText one can easily copy the executed query to Access itself and execute it there to see if any errors arise.
I understand where you are coming from with respect to dynamic SQL strings.
But in my experience, the need for the dynamic creation of an SQL string is very rare. In my programming, which involves named queries with query criteria making reference to controls on forms, there may be only one or two queries (out of 200 used in a program) in any of my programs that require a dynamic SQL string. So, at least in my experience, dynamic SQL string-building is a very rare thing.
But even if the breaking of SQL strings into smaller pieces is a handy thing, the fact is that I see this technique used time-and-time-again with just about every coding responsibility, not just SQL string building.
For instance, in Microsoft's own help on the MsgBox function, they post the following example:
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
Now, this example demonstrates a programming style which, I think, most people think is correct because, after all, Microsoft is demonstrating it.
I think it is a HORRIBLE programming style!
Here is what I would have written:
If vbYes=MsgBox("Do you want to continue ?",vbYesNo + vbCritical + vbDefaultButton2,"MsgBox Demonstration","DEMO.HLP",1000 then
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
Now lets think about this. How much debugging can possible go on in the use of a messagebox? If we were thinking about debugging a dynamic SQL string, with all the nuances and perculiarities of SQL syntax, then yes, we would have a justification for breaking the thing in many pieces.
But a messagebox? How much simplier of a programming effort can there be?
Here in Microsoft's example, they have done in 14 lines what should have been done in five. On top of that, they even made other errors. They've dimensioned, by default, all those variables as Variants, the most "expensive" of data types, even when the MsgBox function specifically calls for strings and numbers (integers), much more "economical" data types.
And the messagebox is only one example. I've seen code where not a single function or statement is not preceeded with a half-dozen variable assignments and declarations, all to be used in that single function, or statement. I've gone through programms with thousands of lines of code, reducing that code to a couple-of hundred lines.
My message to beginning programmers is: don't follow Microsoft's examples!
Besides Cipher, let's get a discussion going here. This is certainly a subject that everyone can participate and contribute to. Let's talk about this.
I've read, or refered to, a number of these books (I like SteveMcConnell's work), but that doesn't mean I agree with everything in those books.
I believe that some recommendations in those texts are highfalutin, impractical and expensive to implement. Many of the recommendations made by these books are appropriate to C, or FORTRAN, but not to VB, or specifically, Access. My MsgBox example is an example.
I think that programmers at this forum can contribute to a debate about programming style.
I fear a generation of programmers who have learned from wizards.
I'd like to hear some opinions.
But again, thanks for the list, some of which I have not seen. I'm going to look in to them soon.
Now we're getting somewhere with this conversation.
Let's look into your example, Ken.
In my job, I have had to start from the beginning as a 'bug fixer', having had to go through thousands and again tousands of rows of code, maybe even uncommented. Now this leads to the fact, that it's a real pain to go through someone elses code, not to even mention if it's uncommented, or, as in your case, very unreadable.
I can clearly see the reason for a firm like Microsoft, and many others including ours having specifically specified coding styles just like it was shown in the example. The reason is, that referring to a study about coding (don't know where I saw it and who did it) the fact is that two thirds of the time you 'code', goes into reading the code. My experience shows the same, and the number gets even bigger when it's somebody elses code you're going through.
Now, look at both yours and Microsofts code. In which one would it be easier to spot and change the Context as nessessary? The answer is clear, to me at least. And this isn't even complex code.
Back to the thing, that other people should also be able to read the same complex code. In my firm, there is specific guidelines as how to code, was it ASP or VB. Now we were there, when everybody coded with their own style, nobody giving a heck of how it's done as long as it works. The thing why things have changed since then is that the habit was horrible. When an employee left, and another came in, it took weeks, even months to get into the simplest of code, even if the newcomer was already familiar with the code language itself. Just because of such a tremendiously different coding style. Today leaving employees are a part of everydays life, and just what will you do, if the code is unreadable to all but this one person?
Reading the code, when it is split into small, clear to understand and simple parts, is much easier and waaay faster that reading what you wrote, no offence.
Performance becomes an issue. Maybe not yet in this example you've given us is it a factor, but one doesn't need to go much further for it to become one. Say you'd have to use the same result you got from the MsgBox again, in another place. Storing it once into a variable, and then using the variant many times is much more efficient that using the same collection multiple times. Even sometimes based on my own experience and extensive speed measurements it is faster to put the MsgBox's response to a variable and then compare two variables that it would be to compare a variable to a MsgBox's response. It gets small, but in big projects small things matter the most.
Thanks for responding. I'd like to keep this discussion going, and I'd like to get others involved.
I disagree with much of what you discussed, so let me try and convince you on some of these issues. For this reply, I am only going to pick a single, minor issue, and I will follow up later with other comments.
First, let me state that it is an accepted coding standard that you should scope and declare you variables to the minimum required usage. I believe that is something we can all agree on (maybe not!).
In Microsoft's example, we use the MsgBox function which accepts clearly defined inputs--basically, they are this (from Access' own help system):
Assuming that the string expressions are more than a single character in length, the only applicable Access data type for them is text. As for the buttons field, the largest number you could possibly place in either of these field (by Adding the appropriate constants) is 4,096+768+64+5=4,933. So the minimum data type which should be used is an integer. As for the context field, whether you uses a boolean, a byte, an integer, or a long, would be application specific, but with the help-context-IDs being longs, lets assume that this must be long.
So in reality, the variables for the MsgBox function should be dimensioned:
Dim Msg as string, Style as integer, Title as string, Help as string, Ctxt as integer
But Microsoft dimensions them all as Variants, the most "expensive" of data types, even though the MsgBox function to which they are inextricably devoted can only use types of a lesser declaration.
Did it take any more effort to dimension the variables correctly? No it did not. It takes no more effort to do your dimensioning correctly, than it does to do it poorly. The only difference is the result.
But what's the harm?
The harm is that, like anything else in coding, your code, the running and efficiency of your code, becomes sloppy. If this is not a sufficient reason, then I propose that we never declare a variable other than variant, 'cause there is no reason to do otherwise!
Since I have already gone on too long on this minor portion of this coding style question, I'll end it here. But there is much more to discuss.
--whether, or not, on should be using variables, in these instances, at all.
--whether, or not, it is more efficient to assign the results of a function to a variable, and then use that variable for testing.
Cipher, and others, please keep this discussion going.
Ken, what you wrote in your last post was nothing but true. I agree with every part of it. As a matter of fact I was supposed to mention that in my message too, but I forgot it because of it's selfevidence. I use variant only when it is utterly imperative, not otherwise. I'd also like it very much if there was a possibility to declare variables in ASP also other that variants, but there isn't.
Enough of that. Ken, read my previous post again and bring up the next issue from your point of view!
At this time in the morning I can't think of another way of doing that example of yours really otherway. As I review the code you wrote, there's nothing wrong in it. I'd do it just the same, with minor modifications, but at this point I'd like to bring up the consept of naming variables.
In my opinion all variables should be named very carefully to tell exactly what is in them. Therefore, talking about your example again, Igor, I'd make slight modifications to the code of yours in that part.
1. The case shouldn't change inbetween the code. I'm quite sure this was unintentional of you but I bring it up anyways. If you declare the variable as 'strSQL' then it should be 'strSQL' for the rest of the code and it shouldn't change to 'strSql' in the middle.
2. What, in your example, does the 'strSQL' actually have in it? It isn't a whole SQL string, as it's name would suggest, but merely a table's name. Therefore I'd name it as 'taulu', which is Finnish for 'table' and it would be more appropriate. Do not go and use 'table' to avoid any conflicts. 'table2delete' would be a good choise, if you're coding in english.
3. I use as much identing as possible. It clarifies the code again bringing me to the speed of reading the code and making it at the same time very readable to others, too.
4. This really has nothing to do with anything but my own opinion in clear code but I use capital letters with all keywords.
So my code would look like this:
Dim taulu as String
Select Case me.cboTables
taulu = "tblTable1"
taulu = "tblTable2"
taulu = "tblTable3"
taulu = "tblTable4"
Any of those code snipits are fine. In these cases you are using a variable BECAUSE YOU NEED A VARIABLE. You need to be able to change the value of strSQL to deal with the various cases.
In the MsgBox example, however, you didn't need a variable 'cause the string was only going to be used once, in the MsgBox function.
I got busy yesterday and was not able to post my next installment. When I get to the office this moring I will get my next issue out. It will be about the issue of assigning values to variable for the sole purpose of performing a boolean test.
Thanks a lot guys.
The board is much more interesting when we have a conversation going.
Usually I type all the code in VBA editor, which takes care of case automatically. For example, when I declare [Dim strString as String] and use the variable later VBA automatically corrects typing if necessary. The sample code was typed here in DBforums text box, so it was posted as is. It actually took out all my indentation. BTW, is there any way to enter TAB. I ended up typing several spaces instead of one TAB.
I thought that was the case with you, Igor, but I mentioned it just for everybody else to think of it too. The VBA of course takes care all of that but in e.g. ASP you can code with which ever text editor you prefere, therefore not having the same automations as in VBA.
I actually only now noticed that even the spaces I placed to ident my code didn't appear anywhere. I suppose it's coded into the message board system that it takes out all "needless" spaces.