Variable types

[Diddy]

Hi,

I'm a bit of a dabbler with VBA, I use a combination of recorded code,
snippets from the web and user groups and (thankfully) code supplied in
answers here.
At the moment I'm trying to learn a bit about referring to ranges as I'm
hoping to use Offset in some work. The site I'm looking at is
http://spreadsheetpage.com/index.php/tip/referring_to_ranges_in_your_vba_code/

I'm totally confused about variables and DIM statements but understand the
reasons for using Option Explicit (I hope).

So my problem is that I've copied a bit of code from the website and don't
know how to dim the variables. I could spend all day trying various different
things but I was hoping that if I came here I could get some help and maybe
someone could explain the why of it too.

Here's the code

Sub FillRange2()
Num = 1
For Row = 0 To 9
For Col = 0 To 9
Sheets("Sheet1").Range("A1").Offset(Row,Col).Value = Num
Num = Num + 1
Next Col
Next Row
End Sub

Many thanks

 

[Gary''s Student]

Option Explicit
Sub FillRange2()
Dim num As Integer, row As Integer, col As Integer
num = 1
For row = 0 To 9
For col = 0 To 9
Sheets("Sheet1").Range("A1").Offset(row, col).Value = num
num = num + 1
Next col
Next row
End Sub

Here integer is o.k. because the values are small.

 

[Dave Peterson]

If you're using a whole number (positive, negative, or 0), then use "as long".
If you're using a fractional number, then use "As Double".

If you're using an object, it's best to work with that specific object:

Dim wks as worksheet
to deal with worksheets

dim wkbk as workbook
for workbooks

Not
Dim wks as object
or
dim wkbk as variant

And I find that it's less confusing to stay away from keywords that excel's VBA
uses. So I wouldn't use Row as a variable name.

I used iRow in this sample. Even though Col would be an ok name, I like to use
iCol as the column counter. It's a personal choice.

Option Explicit
Sub FillRange2A()

Dim Num As Long
Dim iRow As Long
Dim iCol As Long

Num = 1
For iRow = 0 To 9
For iCol = 0 To 9
Sheets("Sheet1").Range("A1").Offset(iRow, iCol).Value = Num
Num = Num + 1
Next iCol
Next iRow
End Sub

There are variable types that are close to Long's and Double's (Integer's and
Single's). But each of these have smaller limits on what they can hold. By
eschewing those types, your code will take a little more to break.

And from what I've read, your computer will translate Integers to longs to do
the real work anyway. So I'd recommend that you forget that they exist--except
to fix your older code!

=========

And just to add to your idea of why "option explicit" is a good idea:

Saved from an earlier post about why "option explicit" should be used.

I do it for a much more selfish reason.

If I add "Option Explicit" to the top of a module (or have the VBE do it for me
via tools|options|Editor tab|check require variable declaration), I know that
most of my typos will stop my code from compiling.

Then I don't have to spend minutes/hours looking at code like this:
ctr1 = ctrl + 1
(One is ctr-one and one is ctr-ell)
trying to find why my counter isn't incrementing.

And if I declare my variables nicely:

Dim wks as worksheet
not
dim wks as object
and not
dim wks as variant

I get to use the VBE's intellisense.

If I use "dim wks as worksheet", then I can type:
wks.
(including the dot)
and the VBE will pop up a list of all the properties and methods that I can
use. It saves time coding (for me anyway).

And one final selfish reason.

If I use a variable like:

Dim ThisIsACounterOfValidResponses as Long

I can type
Thisis
and hit ctrl-space and the VBE will either complete the variable name or give me
a list of things that start with those characters.

And by using a combination of upper and lower case letters in my variables, the
VBE will match the case found in the declaration statement. And that makes it
easier to see possible typos in my code.

 

[Rick Rothstein]

Two points. First, I would probably Dim the 'num' variable As Double since
it is being assigned back as the value to a cell and we don't know for sure
that it is always going to be a whole number. Second, I would always use As
Long for variables that only take integer values. On 32-bit operating
systems, numbers Dim'med As Integer will be stored in the same memory space
as those Dim'med As Long (4 bytes = 32 bits); so, by making them As Long to
begin with, you save the operating system from having to maintain whatever
it is it maintains to track Integers (within the 4-byte memory storage it
ends up in)... so, in the long run, As Long is more efficient than As
Integer.

 

[Jacob Skaria]

Few more..which helps in the long run especially when you are trouble
shooting or modifying an exisitng code.

Name your variables as to reflect the type and the use. For example lngRow
would reflect that it is Long and hold the Row number. lngCol would reflect
that it is Long and hold the Column number. Similarly if you are using a
temporary variable say lngTemp.

wkbMaster to denot Workbook
wksNames to denot Worksheet
Prefix int to denote Integer
Prefix dbl to denot Double
Prefix bln to denot Boolean and so on..........

 

[Diddy]

Thank you

Option Explicit
Sub FillRange2()
Dim num As Integer, row As Integer, col As Integer
num = 1
For row = 0 To 9
For col = 0 To 9
Sheets("Sheet1").Range("A1").Offset(row, col).Value = num
num = num + 1
Next col
Next row
End Sub

Here integer is o.k. because the values are small.

 

[Diddy]

Thank you Rick,

Do you use integer at all?

Cheers
Diddy

 

[Diddy]

Thank you Dave,

You've also just answered a second question I put to Rick about using
Integers - cheers.

Thanks again Diddy

 

[Diddy]

Thank you Jacob

Few more..which helps in the long run especially when you are trouble
shooting or modifying an exisitng code.

Name your variables as to reflect the type and the use. For example lngRow
would reflect that it is Long and hold the Row number. lngCol would reflect
that it is Long and hold the Column number. Similarly if you are using a
temporary variable say lngTemp.

wkbMaster to denot Workbook
wksNames to denot Worksheet
Prefix int to denote Integer
Prefix dbl to denot Double
Prefix bln to denot Boolean and so on..........

 

[Rick Rothstein]

Off the top of my head, I'd have to say no, never.