Proper Programming

[Stuart]

Is the following the corect way to use functions? The following works fine,
however When I look at what the experts do they seem to embellish there code
with remarks like "as Double" "as Integer" "as Variant" and wonder if i
should embellish mine in this way, but, I don't really know what it all
means nor do I see the point in it as code seems to run quite happily
without it?

Anyway, here is some code that I have wrote, it works fine and does the job,
but how should it of been done.

PS. I do really appreciate the help that is freely given by others on this
group, it is invaluable, and take this opportunity to wish you all a very
happy xmas!

Sub KeepImage()
Dim lB1, lB2
lB1 = GetList1Info()
lB2 = GetList2Info()
If lB1 = "" Or lB2 = "" Then Exit Sub
ActiveSheet.Unprotect
With ActiveCell
.Offset(0, 2) = "Yes"
.Offset(0, 3) = lB1
.Offset(0, 4) = lB2
End With
end sub

Private Function GetList1Info()
Dim lBox1 As ListBox
Dim temp, i
Set lBox1 = Sheets("jpegs").ListBoxes("List Box 1")
With lBox1
For i = 1 To .ListCount
If .Selected(i) Then
temp = .List(i)
Exit For
End If
Next i
End With
GetList1Info = temp
End Function

Private Function GetList2Info()
Dim lBox2 As ListBox
Dim temp, i
Set lBox2 = Sheets("jpegs").ListBoxes("List Box 2")
temp = ""
With lBox2
For i = 1 To .ListCount
If .Selected(i) Then
temp = temp & .List(i) & ";"
End If
Next i
End With
GetList2Info = temp
End Function

 

[Dave Peterson]

There are technical reasons for dimming your variables as the correct type.
Your code will execute faster and you'll be reserving less space for them.

In xl2002, there's a "Using Data Types Efficiently" in VBA's help.

But the overwhelming reason I declare variables as the correct type is to get
VBA's intellisense to pop up.

if you use
Dim Wks as Worksheet
then as soon as you type the dot in:
Wks.
You'll see VBA start to help with a list of all the things that come next.

You don't get that with
dim Wks
'cause VBA has no idea what you're working with.

 

[Chip Pearson]

Stuart,

When you "embellish" your variable declarations, you are
declaring those variables to be of a specific data type, such as
Long or Double. Without such embellishment, the variables are
typed as Variant, which as the name implies, can hold any data
type. However, this flexibility comes at a price. VBA must
compile code that examines the sub-type of the Variant to
determine what type of data is actually held in that variable.
This extra code is eliminated if you use explicit type
declarations. (Coding "As Variant" is redundant because the lack
of an "As" clause will cause the variable to be declared as a
variant, but it is still good coding practice to use As Variant
because it makes the code more clear and self-documenting.)

Variant are flexible, but you pay a performance price when you
use them. This price is raised substantially when you store
object-type variables within variants. Good program practice
dictates that you declare variables as the appropriate data type,
using Variants only when absolutely necessary.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tim Zych]

As your projects get longer it becomes much harder to figure out what's
going on. 6 months from now when you go back to your project to update it
and you see "Dim TheThing", are you really going to remember what you meant?

I like explicit declaration because I compile the project and Excel will
point out usage errors.

Dim X As Long
X = 5
'200 lines of code and nested conditions here
MsgBox X.Name

vs

Dim X
X = 5
'200 lines of code and nested conditions
MsgBox X.Name

in the first example, the compiler will catch the problem. Either you or
your end user will have to catch the second one.

 

[Guest]

Something you pointed out that has not yet been explained.. (Probably other things too that could help, but I'm going to cover this one item.)

You said you didn't know what it all means.

As everyone else has said,

Dim thisThing

Provides memory for the variable thisThing as a Variant, by default. However, if you wish to declare your variables as a type, you should use the following as a guide for your variable selection. Remember, that in programming, you should always preplan your job, so that you can try to solve any problems as soon as possible, and not have to redo all the thousands of lines of code you may end up writing. (not hard to get to thousands of lines, if you try to deal with the multitude of potential problems that you identify, and all the things to keep/guide the user to correct and controled data entry.)

For example, if you know that you are only going to be using yes/no, true/false, on/off, etc. type data, then declaring the variable as a boolean by the following line, will give you a variable that is just that, either true or false.

Dim IsTrue as Boolean

So you could write something like.

if IsTrue then
msgbox("The result is true.")
else
msgbox("The result is false.")
end if

Something that helps you, and aids in Excel not crashing, is to use the Option Explicit command at the top of your code, outside of a sub routine or function.

Like

Option Explicit

Public Sub TrueText()
Dim IsTrue as Boolean

IsTrue = True

If IsTrue then
MsgBox("Is True")
Else
MsgBox("Is False")
End If

End Sub

By using the Option Explicit, the debugger will require that each variable that you try to use, must first be declared. Now you don't ***have*** to declare it as a particular type, but as has been discussed, in the long run if you know what type of data it will be then, it realllllllllllly can help.

What I have found, is that if you have extensive code, and you do not declare each variable, VBA will take care of the "assignments" as necessary, but at some point it runs out of memory, or runs into an error, and the code crashes. Obviously if you haven't saved your work, then everything changed since your last save is blasted away.

Anyways, here is the list of data types available, and their restrictions (Range). For an example, if you declare a variable as an Integer, and say you increment the variable by one until you get to 32,767, the next increment will flip the variable back to it's lower limit. So in this case (for an Integer) 32,767 + 1 = -32,768. It can be discouraging, but that's how the variables work. It's all based on the binary representation of the numbers.

I don't remember the command, but it's possible to set the range of Integer instead from -32,768 to 32,767, it can be set to 0 to 65535.

The following information was taken from the help for 'Data Type Summary'
Data type Storage size Range
Byte 1 byte 0 to 255
Boolean 2 bytes True or False
Integer 2 bytes -32,768 to 32,767
Long
(long integer) 4 bytes -2,147,483,648 to 2,147,483,647
Single
(single-precision floating-point) 4 bytes -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45
to 3.402823E38 for positive values
Double
(double-precision floating-point) 8 bytes -1.79769313486232E308 to
-4.94065645841247E-324 for negative values;
4.94065645841247E-324 to
1.79769313486232E308 for positive values
Currency
(scaled integer) 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal 14 bytes +/-79,228,162,514,264,337,593,543,950,335 with no decimal point;
+/-7.9228162514264337593543950335 with 28 places to the right of
the decimal;
smallest non-zero number is +/-0.0000000000000000000000000001
Date 8 bytes January 1, 100 to December 31, 9999
Object 4 bytes Any Object reference
String
(variable-length) 10 bytes + string length 0 to approximately 2 billion
String
(fixed-length) Length of string 1 to approximately 65,400
Variant
(with numbers) 16 bytes Any numeric value up to the range of a Double
Variant
(with characters) 22 bytes + string length Same range as for variable-length String
User-defined
(using Type) Number required by elements The range of each element is the same as the range of
its data type.

 

[Chip Pearson]

until you get to 32,767, the next increment will flip the
variable back to it's lower limit. So in this case (for an
Integer) 32,767 + 1 = -32,768.
<<<

That is false. Incrementing past the upper limit does NOT flip
the value to the lower limit. It causes an error 6: overflow.
I don't remember the command, but it's possible to set the range
of Integer instead from -32,768 to 32,767, it can be set to 0 to
65535.
<<<

This, too, is incorrect. Integers and Longs are always signed.
There is no way to use unsigned variables in VBA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Something you pointed out that has not yet been explained..

(Probably other things too that could help, but I'm going to
cover this one item.)

You said you didn't know what it all means.

As everyone else has said,

Dim thisThing

Provides memory for the variable thisThing as a Variant, by

default. However, if you wish to declare your variables as a
type, you should use the following as a guide for your variable
selection. Remember, that in programming, you should always
preplan your job, so that you can try to solve any problems as
soon as possible, and not have to redo all the thousands of lines
of code you may end up writing. (not hard to get to thousands of
lines, if you try to deal with the multitude of potential
problems that you identify, and all the things to keep/guide the
user to correct and controled data entry.)

For example, if you know that you are only going to be using

yes/no, true/false, on/off, etc. type data, then declaring the
variable as a boolean by the following line, will give you a
variable that is just that, either true or false.

Dim IsTrue as Boolean

So you could write something like.

if IsTrue then
msgbox("The result is true.")
else
msgbox("The result is false.")
end if

Something that helps you, and aids in Excel not crashing, is to

use the Option Explicit command at the top of your code, outside
of a sub routine or function.

Like

Option Explicit

Public Sub TrueText()
Dim IsTrue as Boolean

IsTrue = True

If IsTrue then
MsgBox("Is True")
Else
MsgBox("Is False")
End If

End Sub

By using the Option Explicit, the debugger will require that

each variable that you try to use, must first be declared. Now
you don't ***have*** to declare it as a particular type, but as
has been discussed, in the long run if you know what type of data
it will be then, it realllllllllllly can help.

What I have found, is that if you have extensive code, and you

do not declare each variable, VBA will take care of the
"assignments" as necessary, but at some point it runs out of
memory, or runs into an error, and the code crashes. Obviously
if you haven't saved your work, then everything changed since
your last save is blasted away.

Anyways, here is the list of data types available, and their

restrictions (Range). For an example, if you declare a variable
as an Integer, and say you increment the variable by one until
you get to 32,767, the next increment will flip the variable back
to it's lower limit. So in this case (for an Integer) 32,767 + 1
= -32,768. It can be discouraging, but that's how the variables
work. It's all based on the binary representation of the
numbers.

I don't remember the command, but it's possible to set the

range of Integer instead from -32,768 to 32,767, it can be set to
0 to 65535.

The following information was taken from the help for 'Data Type Summary'
Data type Storage size Range
Byte 1 byte 0 to 255
Boolean 2 bytes True or False
Integer 2 -32,768 to 32,767
Long
(long integer) 4

-2,147,483,648 to 2,147,483,647

Single
(single-precision floating-point) 4

-3.402823E38 to -1.401298E-45 for negative values;
1.401298E-45to 3.402823E38 for positive values

Double
(double-precision floating-point) 8 -1.79769313486232E308 to

-4.94065645841247E-324 for negative values;

4.94065645841247E-324 to

1.79769313486232E308 for positive values

Currency
(scaled integer) 8

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal 14 bytes

+/-79,228,162,514,264,337,593,543,950,335 with no decimal point;+/-7.9228162514264337593543950335 with 28 places to the right of

the decimal;

smallest non-zero number is +/-0.0000000000000000000000000001

Date 8 bytes

January 1, 100 to December 31, 9999

Object 4 bytes Any Object reference
String
(variable-length) 10 bytes + string length 0 to approximately 2 billion
String
(fixed-length) Length of string 1 to approximately 65,400
Variant
(with numbers) 16 bytes

Any numeric value up to the range of a Double

Variant
(with characters) 22 bytes + string length Same

range as for variable-length String

User-defined
(using Type) Number required by elements The

range of each element is the same as the range ofits data type.

 

[KJTFS]

Let us not forget about using Camel casing and Strange Hungarian
Notation to help when it comes to variable declaration. When 6 months
comes and you look at a variable it is much easier to read incode
intRow or bolRow and figure what type of variable it is. When I first
started programming I was a big fan of the x, y, z variables. After a
while you begin to find the benifits of self describing variables and
type declarations. Esp. in a strongly typed lang where you have to
cast all your variables to get them to work.

Keith
www.kjtfs.com

 

[Guest]

Hmm.. I thought I had run into the results on which I have been corrected. Perhaps in a different programming platform, like C or C++. :
Though VBA doesn't offer an unsigned anything (no unsigned integers, no unsigned long integers, etc..) you can use the variables as if they were unsigned at least for addition and subtraction purposes, though multiplication and division would get botched unless you went ahead and redefined the variable type. Anyways I'm babbling.

If you were set on using an integer and needed the 65535 to be displayed, you could always have your signed integer (The default and only option for an integer) and add 32768. At least for the purposes of display. The following code worked and resulted in displaying 65535.

Public Sub TestThis()
Dim intVal As Integer

intVal = 32767
MsgBox ("Unsigned: " & intVal + 32768)

End Sub

I was wrong though on the two statements referred to by Chip. Thank you for the correction, and further "training."

 

[Chip Pearson]

intVal = 32767
MsgBox ("Unsigned: " & intVal + 32768)

The reason for this is that VBA automatically casts the 32768 as
a Long type variable since it can't be held in an Integer, and
does the arithmetic in longs, and then displays the result as a
string from a long. E.g.,

MsgBox "Unsigned: " & TypeName(intVal + 32768)

If you try similar code, but with a value that VBA won't
automatically cast as a long, you'll get an overflow error. E.g.,

intVal = 32767
MsgBox "Unsigned: " & intVal + 1

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Hmm.. I thought I had run into the results on which I have been

corrected. Perhaps in a different programming platform, like C
or C++. :

Though VBA doesn't offer an unsigned anything (no unsigned

integers, no unsigned long integers, etc..) you can use the
variables as if they were unsigned at least for addition and
subtraction purposes, though multiplication and division would
get botched unless you went ahead and redefined the variable
type. Anyways I'm babbling.

If you were set on using an integer and needed the 65535 to be

displayed, you could always have your signed integer (The default
and only option for an integer) and add 32768. At least for the
purposes of display. The following code worked and resulted in
displaying 65535.

Public Sub TestThis()
Dim intVal As Integer

intVal = 32767
MsgBox ("Unsigned: " & intVal + 32768)

End Sub

I was wrong though on the two statements referred to by Chip.

Thank you for the correction, and further "training."

 

[Harald Staff]

Hi guys

Do not use Integer. Stick to Long and Double.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/decontheinte
gerdatatypes.asp

Quote:
"The Integer and Long data types can both hold positive or negative values. The difference
between them is their size: Integer variables can hold values between -32,768 and 32,767,
while Long variables can range from -2,147,483,648 to 2,147,483,647. Traditionally, VBA
programmers have used integers to hold small numbers, because they required less memory.
In recent versions, however, VBA converts all integer values to type Long, even if they
are declared as type Integer. Therefore, there is no longer a performance advantage to
using Integer variables; in fact, Long variables might be slightly faster because VBA does
not have to convert them.

 

[Auric__]

Hi guys

Do not use Integer. Stick to Long and Double.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/decontheinte
gerdatatypes.asp

Quote:
"The Integer and Long data types can both hold positive or negative values. The difference
between them is their size: Integer variables can hold values between -32,768 and 32,767,
while Long variables can range from -2,147,483,648 to 2,147,483,647. Traditionally, VBA
programmers have used integers to hold small numbers, because they required less memory.
In recent versions, however, VBA converts all integer values to type Long, even if they
are declared as type Integer. Therefore, there is no longer a performance advantage to
using Integer variables; in fact, Long variables might be slightly faster because VBA does
not have to convert them.

Now why in the hell does it do that? If I declare a var as integer, I do
it for a reason.

 

[Harald Staff]

Long variables might be slightly faster because VBA does

Now why in the hell does it do that? If I declare a var as integer, I do
it for a reason.

Well, in VB5-6 and VBA that reason is Long gone ;-)

 

[Harlan Grove]

Now why in the hell does it do that? If I declare a var as integer, I do
it for a reason.

The only reason to do so would be to conserve storage, but storage
minimalism and use of spreadsheets is inconsistent to begin with.

On 32-bit hardware, the programming language at some point needs to align
the binary representation on 32-bit boundaries. It's expedient to do so for
all integer variables rather than have to figure out how to align to the
nearest 32-bit boundary.

If you want fine granularity, use C or Assembler. If you want or need to use
VB[A], live with the lack of fine granularity.

 

[Tushar Mehta]

No reason why you shouldn't. I use Integer and Bytes to indicate
intent. In fact, I wish I could declare a variable as
X as Real {-49..+49} or
X as Whole Number {-100..-50}
and have the compiler / OS / firmware / hardware enforce integrity.

The time for worrying about nanosecond performance improvements
resulting from hardware-aligned variables has long since past. The
only time I worried about that kind of stuff was when programming in
Assember on a IBM360 -- and even then just for a lark.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

No reason why you shouldn't. I use Integer and Bytes to indicate
intent. In fact, I wish I could declare a variable as
X as Real {-49..+49} or
X as Whole Number {-100..-50}
and have the compiler / OS / firmware / hardware enforce integrity.

There are languages which provide this. It could be implemented in C++,
creating Real and Whole classes with definable lower and upper value bounds
and the ability to throw exceptions when values exceed these bounds. Granted
it's a lot of work, but the capability exists.

Now, whether 'simple' languages like BASIC should provide this is arguable.

The time for worrying about nanosecond performance improvements
resulting from hardware-aligned variables has long since past. The
only time I worried about that kind of stuff was when programming in
Assember on a IBM360 -- and even then just for a lark.

You the application programmer may not gain much from concerning yourself
with memory alignment issues, but the systems programmers who write language
compilers and interpretters must because it's IMPOSSIBLE (as in the
hardware/CPU can't do it) to do some things unless you start out at a 16- or
32-bit boundaries.

In this age of hardware floating point processing, the only thing provided
by smaller integer or floating point types is potentially more economical
use of system storage. Actual calculations are generaly unaffected whether
operands are 8, 16 or 32 bits long. For individual (scalar) variables, the
benefits from saving 1 or 3 bytes is more than offset by the *cumulative*
performance penalty of dealing with non-paragraph alignment. There are
stronger arguments in favor of actually conserving memory when it comes to
arrays of potentially more economical types.