# Read / Write VBA



## squiresk (Dec 22, 2008)

Hi,

Quick question.

Does anyone else find it easier to write VBA than to read it?

(and comprehend).



Kai.


----------



## MrKowz (Dec 22, 2008)

I find it much easier to write VBA than to read it (unless I am the one who previously wrote the code).  The reason why is because I know exactly what each line is referencing to and the like.  However, almost everything I have learned about VBA has come from picking apart other people's coding from these forums.


----------



## RoryA (Dec 22, 2008)

Generally yes, but it definitely depends who wrote the VBA, whether they use the same naming conventions (if they use any!) as I do, and whether they bothered commenting any of it! I also find it _much_ harder when people use the colon to put multiple instructions on one line.


----------



## MorganO (Dec 22, 2008)

You can use a colon to put more than one instructions on each line?!? Now I can further obfuscate my code to complicate my life several years down the road when I attempt to decipher it!


----------



## RoryA (Dec 22, 2008)

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


----------



## Cbrine (Dec 22, 2008)

Ah, my favorite code obfusication method(I read about it in some wacky guide), was to use a nondescriptive varilable, like "i", and use it as a totally different variable, depending on the scope you are looking at it.


----------



## Norie (Dec 22, 2008)

My favourite is when the code hasn't been indented, or indented weirdly.

And to understand code it's always nice to actually have an idea of it's purpose.

Oh, and then there's the (non) use of code tags.


----------



## MrKowz (Dec 22, 2008)

Cbrine said:


> Ah, my favorite code obfusication method(I read about it in some wacky guide), was to use a nondescriptive varilable, like "i", and use it as a totally different variable, depending on the scope you are looking at it.


 

```
Sub CONFUSING()
Dim i as Integer, LR as Integer, currenttime as Integer, panda as Integer
i = Range("A" & rows.count).End(xlUp).Row
'This will insert a new row at potato
currenttime = Sheets.count
'Insert a sheet counter here
For LR = 2 to i
    For panda = 1 to currenttime
        sheets(panda).Cells(LR,panda).Value = ""
        'Populates the cell
    Next panda
Next LR
End Sub
```


----------



## ExcelIsEvil (Dec 29, 2008)

Norie said:


> My favourite is when the code hasn't been indented, or indented weirdly.  And to understand code it's always nice to actually have an idea of it's purpose.


 
descriptive identification of data item. 
I'm often 'fixing' others work and have to deal with the following:
iCount
R 
cnt
Field3TXT

Come on, how hard is it to write out the shortest identifier?


----------



## GlennUK (Dec 30, 2008)

I'm a lot better with naming variables compared to when I started writing VBA. I was taught FORTRAN as my first computer language, and the person teaching me had a convention of using "i" for outer loop counter, "j" for next level loop, "k" for the next, and so on for "l", "m", and "n" ... and I was writing like that for many years, and was still the same when I started with VBA. 

I guess people write the way that they are used to, and without taking into account readability. I am trying much harder these days, even though I am the only one ever likely to look at the code.


----------



## texasalynn (Dec 30, 2008)

rorya said:


> 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 (Dec 30, 2008)

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 (Dec 30, 2008)

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


----------



## DonkeyOte (Dec 31, 2008)

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 (Dec 31, 2008)

rorya said:


> 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 (Dec 31, 2008)

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 (Dec 31, 2008)

Greg Truby said:


> 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 (Dec 31, 2008)

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 (Dec 31, 2008)

DonkeyOte said:


> 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."



DonkeyOte said:


> 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.


DonkeyOte said:


> 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



yytsunamiyy said:


> 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 (Dec 31, 2008)

DonkeyOte said:


> 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.


----------



## Greg Truby (Dec 31, 2008)

NateO said:


> ...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


Dammit Nate,  would it have friggin' killed ya to have posted that link before I posted : 


Greg Truby said:


> Because once upon a time...memory mattered ...Because I will, on the rare occasion, use BYTE variables for items I know won't exceed values of 255.


So, what the dickens is the benefit of the BYTE type? Unless, perhaps to deliberately employ the value limitations as traps against unexpected values and the like? Is it just a legacy thing for backwards compatibility?

And for those of us who can barely find the time to stop by here, never mind visiting UA - do we have a thread here where you've gone into detail on that code where you guys were trying to get a bead on the memory footprint of the different variable types?


----------



## NateO (Dec 31, 2008)

It probably wouldn't have killed me, Greg, but I've been off on holliday! 

The beauty of the Byte Data Type? To understand the beauty of the Byte Type, you need to understand why it was created. Richard knows the answer to this one: String Operations.

That's the only time I ever use it, as a character array (Byte Array). Although this can be tricky. This Data type was actually created with this specific task in mind, not to just house numbers:

http://vb.mvps.org/hardcore/html/whatisunicode.htm



> When Basic sees that you want to pass a string to an outside function, it conveniently squishes the internal Unicode strings into ANSI strings. But if the function expects Unicode, you must find a way to make Basic leave your 16-bit characters alone. The new Byte type was added specifically for those cases in which you don’t want the languages messing with data behind your back.


 
It's fast - really fast, for String Parsing in an Array context. Looks like I'm using it, correctly! 

I can see using its limitations in a validation context as well, but also in a String context, limiting the input to a String that's to be built.

Happy new year!


----------



## NateO (Dec 31, 2008)

Greg Truby said:


> And for those of us who can barely find the time to stop by here, never mind visiting UA - do we have a thread here where you've gone into detail on that code where you guys were trying to get a bead on the memory footprint of the different variable types?


 
Not that I know of, but we could start one. And I'm not 100% sure I trust that approach. I might tempted to test CopyMemory():

http://www.devx.com/vb2themax/Tip/18519

Against the Variable and see what gets moved given a certain number of Bytes. However, I can't be sure that what is stored as a certain size is processed in the same manner, without coercion, which is what the documentation appears to be saying.

I'm not sure if VB[A] can get low-enough, as a high-level language, to properly test this.

*Edit: *The thread at UA isn't much more insightful than the one I linked to, the only additional readings in the thread of use are a couple of MSDN links I provided which claim the Integer is coerced to a Long when it's processed.


----------



## yytsunamiyy (Jan 2, 2009)

Greg Truby said:


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



Thanks Greg, I shall read and learn


----------

