Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
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...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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
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. -- Gary''s Student - gsnu200842 "Diddy" wrote: 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...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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
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. Diddy wrote: 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...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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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. -- Gary''s Student - gsnu200842 "Diddy" wrote: 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...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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
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.......... -- If this post helps click Yes --------------- Jacob Skaria "Diddy" wrote: 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...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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
Thank you
"Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200842 "Diddy" wrote: 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...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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
Thank you Rick,
Do you use integer at all? Cheers Diddy "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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. -- Gary''s Student - gsnu200842 "Diddy" wrote: 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...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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
Thank you Dave,
You've also just answered a second question I put to Rick about using Integers - cheers. Thanks again Diddy "Dave Peterson" wrote: 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. Diddy wrote: 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...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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
Thank you Jacob
"Jacob Skaria" wrote: 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.......... -- If this post helps click Yes --------------- Jacob Skaria "Diddy" wrote: 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...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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable types
Off the top of my head, I'd have to say no, never.
-- Rick (MVP - Excel) "Diddy" wrote in message ... Thank you Rick, Do you use integer at all? Cheers Diddy "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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. -- Gary''s Student - gsnu200842 "Diddy" wrote: 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...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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I hide unused file types from file types list in save dial | Excel Discussion (Misc queries) | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Assign a single variable to an Array filled with same data types | Excel Programming | |||
Variable Types | Excel Programming | |||
VB6 to VBA conversion. Form vs. UserForm differences. Object variable types | Excel Programming |