Read / Write VBA

[texasalynn]

Even better is to put lots of comments in but never change them when you change the code - gets really confusing, really fast!

been there - LOL

 

[Greg Truby]

For me, it's almost always easier to write it than read it. When I'm in the middle of a hot coding streak, I know right where I am and where I want to go! It's when I come back three years later, that it's hard to recall all of the flashes of genius that the muses were laying on me way back when. I did a lot of coding in Costa Rica and got into the habit of naming variables in Spanish. I retained the habit even after returning here and discovered it's not a good one. Trying to share code with someone else is hard enough, having to translate it too just adds needless complexity. After numerous episodes where I struggled to understand what the h*** I wrote years ago, I have become something of an evangelist for using the Hungarian/Simonyi naming conventions and commenting more thoroughly. Using naming conventions helps enormously when reading someone else's code here on the board. If a person does that and uses code tags and comments and good indentation? Shoot, it's hard not to help a feller like that. :wink:

As for simple integer counters in loops that don't have too many lines of code, I too use i, j, & k. It's a rare day when I need to nest more than three loops, so I'll tend to use l, m & n for long loop counters (note to newbies: in most cases, if you need a long counter in a loop -- and heaven forbid two longs in a nested loop -- then you are probably doing something the hard way). My favorite trick is to have option explicit up top and forget to dim i so the compiler gets a chance to whine at me.

 

[RoryA]

Why would you ever use integers (as opposed to Longs) for anything??

 

[DonkeyOte]

Agreed...
(though I should add "only because Rory has already told me why you should never bother with Integer type")

And I despise reading others' VBA... especially when there are multiple calls outside of the main sub routine to sub sub (sub) routines ... my old boss had a penchant for that approach and it was horrendous to debug.

 

[Greg Truby]

Why would you ever use integers (as opposed to Longs) for anything??

Because once upon a time, back in the dark ages when I first started programming, memory mattered [the computer's RAM was 8K (yes K)]. So I'll still use frequently use integers. Because I will, on the rare occasion, use BYTE variables for items I know won't exceed values of 255. Because, for quick and dirty little routines I still use the ancient stuff like

DIM s$, i%, l&, f!, d#

 

[DonkeyOte]

I think Rory's point was that Integer type is converted to Long "under the hood" thus there is no real value in using it... unless as some sort of sanity check that a given variable should not exceed those boundaries ie to generate overflow.

Can you explain the ancient stuff ?

 

[yytsunamiyy]

I have become something of an evangelist for using the Hungarian/Simonyi naming conventions and commenting more thoroughly.

Please evangelize me and tell me about the naming conventions - how does naming variables in Hungarian improve readability?

 

[Joe4]

Its really not that you are naming objects in "Hungarian", rather it is a methodology that one uses that helps to identify/denote what the objects are simply by their names.

For example, name all string variables with a prefix of "str", i.e. (strName, strJob, etc). Then, whenever you see something named like this, you can immediately identify that is is a "string" variable.

Likewise, in Microsoft Access, you might prefix all table named with "tbl", etc.

There is a write-up on it here in Wikipedia:
http://en.wikipedia.org/wiki/Hungarian_notation

 

[Greg Truby]

I think Rory's point was that Integer type is converted to Long "under the hood" thus there is no real value in using it...

Wow, now I'm having a senior moment. Part of me is surprised to read this. And part of me is going -- "you know, now that you mention it, I think I might have heard that once and forgotten."

unless as some sort of sanity check that a given variable should not exceed those boundaries ie to generate overflow.

Yes, that might indeed still be a good application for using integers -- to keep an erroneous loop from executing much longer than it would using longs.

Can you explain the ancient stuff ?

You can still use the shortcut suffixes to type variables; though most folks now do the AS TYPExyx version.

• $ = string
• % = integer
• & = long
• ! = single
• # = double

Please evangelize me and tell me about the naming conventions - how does naming variables in Hungarian improve readability?

I more or less use Reddick's take on implementing Simonyi's conventions.

Example as to why? You are provided the following code snippet where 1 & 4 are the same (correct), 2 errors out and 3 returns erroneous info. You cannot tell me why #2 raises an error nor why #3 executes, but returns bad results

    Date1 = Now: Date2 = Now: Date3 = Now: Date4 = Now
 
    On Error GoTo ErrorHandler
 
    MsgBox Day(Date1 + 1) & vbCr & Hour(Date1), vbInformation, "One"
    MsgBox Day(Date2 + 1) & vbCr & Hour(Date2), vbInformation, "Two"
    MsgBox Day(Date3 + 1) & vbCr & Hour(Date3), vbInformation, "Three"
    MsgBox Day(Date4 + 1) & vbCr & Hour(Date4), vbInformation, "Four"
    Exit Sub
 
ErrorHandler:
    Debug.Print Err.Description
    Resume Next
End Sub

Now see if you can guess:

    dtmDate = Now
    strDate = Now
    lngDate = Now
    dblDate = Now
 
    On Error GoTo ErrorHandler
 
    MsgBox Day(dtmDate + 1) & vbCr & Hour(dtmDate), vbInformation, "Eins"
    MsgBox Day(strDate + 1) & vbCr & Hour(strDate), vbInformation, "Zwei"
    MsgBox Day(lngDate + 1) & vbCr & Hour(lngDate), vbInformation, "Drei"
    MsgBox Day(dblDate + 1) & vbCr & Hour(dblDate), vbInformation, "Vier"
    Exit Sub
 
ErrorHandler:
    Debug.Print Err.Description
    Resume Next
End Sub

 

[NateO]

I think Rory's point was that Integer type is converted to Long "under the hood" thus there is no real value in using it... unless as some sort of sanity check that a given variable should not exceed those boundaries ie to generate overflow.

While the limitations certainly aren't coerced to that of a Long, I can't get a certain Access MVP to agree with me that Integers are processed as Longs:

http://www.access-programmers.co.uk/forums/showthread.php?p=724034

I'm not exactly sure if I trust that manner of testing, either... What if it's flipping back and forth, and by the time you hit it in the queue, it's long gone?

The documentation appears to indicate Integers in 32-bit VBA are coerced to Long (careful with .Net), but I think I need to track someone down and have a come-to-Jesus about it, before I fully bite.