Understanding Arrays: A challenging topic
Frank Smith
I'm having a problem placing data into an array then copying that data
in one block onto a worksheet. My knowledge of arrays is limited so
if you can spare the time a little explanation would be very much
appreciated.
I have spent a lot of time searching through the archives and
elsewhere but my books especially do not go into this in the depth I
need to solve the problems below.
This is what I have done so far:
Sub ExtractFromSheetOne() ' this works
'Put focus on SheetOne (Not sure if these 3 lines are best, maybe one
'line of code is faster)
Sheets("SheetOne").Select
Range("A3").Select
Range("A3").Activate
'determine the size of the data range
Dim Test As Range
Set Test = ActiveCell.CurrentRegion
NumberofRows = Test.Rows.Count
NumberofColumns = Test.Columns.Count
'prepare range for copying (whatever its size)
Dim MyRange As Range
Set MyRange = ActiveSheet.Range(Cells(3, 1), _
Cells(NumberofRows, NumberofColumns))
'copy range values into SheetOneArray
'these values will be integers
SheetOneArray = MyRange.value
Set MyRange = Nothing 'frees up memory now that MyRange has been used.
End Sub
Sub DoSomethingToData()
'do something to each row of data in SheetOneArray
For N = 1 To NumberofRows - 2
'do something to each row in SheetOneArray eg sum integers, multiply
integers
'anything really to create input for a 2 dimensional array
'store results in SheetTwoArray
'preserve results in SheetTwoArray
'increase size of SheetTwoArray ready for next iteration
Next N
End Sub
Sub PutArrayOntoSheetTwo()
'ActiveCell at Range("A3")
'Unknown
End Sub
This code does not do anything particularly useful but has been put
together to try and help me understand the following:
copy range from SheetOne into an Array (in one block)
do something to each row of data in the array
create SheetTwoArray
put results into a SheetTwoArray
increase size of SheetTwoArray
when all action is done on all rows in SheetOneArray
store SheetTwoArray onto SheetTwo in one block rather than looping
through values
Happy New Year
Frank
Hank Scorpio
<Frank...@btclosedworld.com> wrote:
>Hi,
>
>I'm having a problem placing data into an array then copying that data
>in one block onto a worksheet. My knowledge of arrays is limited so
>if you can spare the time a little explanation would be very much
>appreciated.
"One clear example is worth more than a warehouse full of inscribed
clay tablets". Says so in the Civilization III manual. Accordingly I
offer you the following. Copy it and paste it into the module of a new
blank workbook. (Don't change the sheet names, unless you do it in the
code below as well.) Put some numbers in the current region around A3
on sheet2, and run it. Then you can go through the code and follow
step by step what it's doing. (Note that I only used sheet 2 and 3
because I had used 1 in answering someone else's question in my test
workbook. You can change the sheets to whichever ones you want.)
I'll also add some comments regarding your existing code below.
Sub ProcessMyArray()
'To pick up a range and put it into a variable,
'you must use a variant. The variable then contains
'an array, which is not QUITE the same thing as
'a variable which is defined AS an arry. They can
'be handled similarly, though.
Dim vnt_Source As Variant
'These we'll use to loop through the array.
Dim l_CounterRow As Long
Dim l_CounterCol As Long
'We'll use these to set where we want to put the
'modified values on the third sheet.
Dim l_TargetRow As Long
Dim l_TargetCol As Long
'There's no need to select the sheet, or the
'current range. You can just pick it up directly.
'Note that I'm using the CODENAME of the
'sheet here; the one that you see in
'the "(Name)" field of the properties window
'in the Visual Basic Editor. This isn't the "Name"
'field (which is what appears on the sheet tab).
'(If you can't see the properties window in the VBE,
'press [F4] to bring it up.)
'Using codenames ensures that your program runs
'even if a user renames the sheet. And it's shorter
'to type.
vnt_Source = Sheet2.Range("A3").CurrentRegion
'Don't assume that the "current region" around A3
'will return an array. What if there are no values
'around it? If that's the case, only the value of
'the single cell A3 will be assigned to the variable.
'Let's check.
If IsArray(vnt_Source) Then
'If it's an array, let's process it. In this
'case, we'll multiply each value by 10.
'We'll do that by stepping through each value.
'(There may in fact be a faster way of doing this,
' but one thing at a time... Besides, I want to
'demonstrate something.)
'Don't check the number of rows and columns.
'Instead use boundary checking. This can be done
'with the UBound and LBound (Upper and Lower
'Boundary) functions.
'If an array has been returned from Current
'region, we know it will be two dimensional.
'Want to know how many rows? Try this (1 is the
'first dimension in the array):
MsgBox UBound(vnt_Source, 1) & " rows"
'And columns? (2 is the second dimension)
MsgBox UBound(vnt_Source, 2) & " columns"
'Now let's do something with that information.
'Loop through each row...
For l_CounterRow = LBound(vnt_Source, 1) To _
UBound(vnt_Source, 1)
'And each column within each row...
For l_CounterCol = LBound(vnt_Source, 2) To _
UBound(vnt_Source, 2)
'Make sure that the cell contains a value.
If IsNumeric(vnt_Source(l_CounterRow, _
l_CounterCol)) Then
'Multiply by 10
vnt_Source(l_CounterRow, l_CounterCol) _
= vnt_Source(l_CounterRow, l_CounterCol) * 10
End If
Next
Next
Else
MsgBox "It's not an array!"
Exit Sub
End If
'OK, let's figure out where the target range is.
'One gotcha here; most arrays in VBA start from a base of 0.
'When you copy a range, it starts from a base of *1*
'even if you have Option Base 0 at the head of the module.
'(Look that up in VB Help if you're interested.)
'We'll start at A1 on the third sheet, so all we need
'are the END coordinates.
l_TargetRow = UBound(vnt_Source, 1)
l_TargetCol = UBound(vnt_Source, 2)
'Now let's plonk that down on sheet3.
'This time we DO need to select the sheet.
Sheet3.Select
'And in one step...
Sheet3.Range(Cells(1, 1), Cells(l_TargetRow, l_TargetCol)) _
= vnt_Source
'et voila.
End Sub
------------------------------------------------
>I have spent a lot of time searching through the archives and
>elsewhere but my books especially do not go into this in the depth I
>need to solve the problems below.
>
>This is what I have done so far:
>
>Sub ExtractFromSheetOne() ' this works
>
>'Put focus on SheetOne (Not sure if these 3 lines are best, maybe one
>'line of code is faster)
>Sheets("SheetOne").Select
>Range("A3").Select
>Range("A3").Activate
No lines of code is faster still. As per my example above, you don't
need to do this.
>'determine the size of the data range
>Dim Test As Range
Your Dims should all go at the head of your procedure, IMHO,
regardless of where they are first used. This is just a style thing,
but you'll find that most programmers do it that way so that it's
easier to see all of the variables in one hit and change them if
necessary.
>Set Test = ActiveCell.CurrentRegion
You haven't declared the following two variables. If you have Option
Explicit set at the head of your module (see VBA help), the procedure
won't compile. If you DON'T have it set, the variables will be
declared implicitly, but will be vulnerable to being mis-typed. IMHO
you should always use Option Explicit. (In the VBE, go to Tools ->
Options -> Editor and check "Require Variable Declaration".
>NumberofRows = Test.Rows.Count
>NumberofColumns = Test.Columns.Count
>
>'prepare range for copying (whatever its size)
>Dim MyRange As Range
>Set MyRange = ActiveSheet.Range(Cells(3, 1), _
>Cells(NumberofRows, NumberofColumns))
As noted above, you don't need to calculate the rows and columns
first.
>'copy range values into SheetOneArray
>'these values will be integers
>SheetOneArray = MyRange.value
>Set MyRange = Nothing 'frees up memory now that MyRange has been used.
>
>End Sub
>
>Sub DoSomethingToData()
>
>'do something to each row of data in SheetOneArray
>For N = 1 To NumberofRows - 2
OOPS! Big "gotcha" there. The variable NumberOfRows was implicitly
created in the ExtractFromSheetOne procedure. It's "local" to that
procedure. DoSomethingToData doesn't know it existed, much less what
its value was. You'd need to either declare the variable at module
level (look up "Scope" in VBA help for an explanation) or better still
pass it to this procedure as an argument. (Unless you put the whole
process in one procedure as I've done.)
>'do something to each row in SheetOneArray eg sum integers, multiply
>integers
>'anything really to create input for a 2 dimensional array
>'store results in SheetTwoArray
>'preserve results in SheetTwoArray
>'increase size of SheetTwoArray ready for next iteration
>
>Next N
>
>End Sub
>
>Sub PutArrayOntoSheetTwo()
>'ActiveCell at Range("A3")
>
>'Unknown
>
>End Sub
>
>This code does not do anything particularly useful but has been put
>together to try and help me understand the following:
>
>copy range from SheetOne into an Array (in one block)
>do something to each row of data in the array
>create SheetTwoArray
>put results into a SheetTwoArray
>increase size of SheetTwoArray
>when all action is done on all rows in SheetOneArray
>store SheetTwoArray onto SheetTwo in one block rather than looping
>through values
>
>Happy New Year
>
>Frank
If you need any clarification, just post again with the details.
---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *
Dave Peterson
When Hank wrote this:
'Now let's plonk that down on sheet3.
'This time we DO need to select the sheet.
Sheet3.Select
'And in one step...
Sheet3.Range(Cells(1, 1), Cells(l_TargetRow, l_TargetCol)) _
= vnt_Source
'et voila.
The reason he had to select sheet3 was that his ranges (cells()) were
unqualified. He could have plonked them down without selecting by using
something like:
Sheet3.Range(sheet3.Cells(1, 1), sheet3.Cells(l_TargetRow, l_TargetCol)) _
= vnt_Source
or
with sheet3
.Range(.Cells(1, 1), .Cells(l_TargetRow, l_TargetCol)) = vnt_Source
end with
And he could have used the dimensions of the array to plonk it down, too:
Sheet3.Range("A1").Resize(UBound(vnt_Source, 1) - LBound(vnt_Source, 1) + 1, _
UBound(vnt_Source, 2) - LBound(vnt_Source, 2) + 1) _
= vnt_Source
--
Dave Peterson
ec3...@msn.com
Hank Scorpio
wrote:
>Just a picky point (not about the array portion):
>
>When Hank wrote this:
>
>'Now let's plonk that down on sheet3.
>'This time we DO need to select the sheet.
>Sheet3.Select
>'And in one step...
>Sheet3.Range(Cells(1, 1), Cells(l_TargetRow, l_TargetCol)) _
> = vnt_Source
>'et voila.
>
>The reason he had to select sheet3 was that his ranges (cells()) were
>unqualified. He could have plonked them down without selecting by using
>something like:
Yeah, you got me. 8^>
The reasons that I did that were:
1- so that the OP could see where the data had gone, to prove that it
had worked; and
2 - I had already given the OP more than enough to absorb and didn't
want to push their syntax absorbtion ability any further.
In the morning light, though, reason 2 doesn't strike me as being an
exceptionally valid one. Accordingly, thanks for adding the
clarification/ expansion of my example.
>Sheet3.Range(sheet3.Cells(1, 1), sheet3.Cells(l_TargetRow, l_TargetCol)) _
> = vnt_Source
>
>or
>with sheet3
> .Range(.Cells(1, 1), .Cells(l_TargetRow, l_TargetCol)) = vnt_Source
>end with
>
>And he could have used the dimensions of the array to plonk it down, too:
>
>Sheet3.Range("A1").Resize(UBound(vnt_Source, 1) - LBound(vnt_Source, 1) + 1, _
> UBound(vnt_Source, 2) - LBound(vnt_Source, 2) + 1) _
> = vnt_Source
Yeah, that was roughly the syntax in the original draft that I did.
This time I DID intentionally chuck it out on the grounds that it's
skull-crushingly convoluted for a learner. But again, no harm in them
seeing that as another possibility.
---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
*Please excuse any missing keystrokes, especially from keys in the
top row of the keyboard. I'm off to buy a new one today!!!*
Frank Smith
Unfortunately, Hanks' reply has not yet appeared on the btopenworld
server. Only realised this when I saw Dave's reply. Went to Google
groups to view the archives for microsoft.public.excel.programming and
had to extract the response from there.
My responses will therefore have to be put into this message which may
look a bit odd.
Comments below:
On Sat, 28 Dec 2002 09:22:15 -0600, Dave Peterson <ec3...@msn.com>
wrote:
>Just a picky point (not about the array portion):
>
>When Hank wrote this:
>
>'Now let's plonk that down on sheet3.
>'This time we DO need to select the sheet.
>Sheet3.Select
>'And in one step...
>Sheet3.Range(Cells(1, 1), Cells(l_TargetRow, l_TargetCol)) _
> = vnt_Source
>'et voila.
>
>The reason he had to select sheet3 was that his ranges (cells()) were
>unqualified. He could have plonked them down without selecting by using
>something like:
The three alternatives have really helped me to understand more. Many
thanks for that Dave.
OK. Done that and very happy with what's happening. Thanks Hank.
>> I'll also add some comments regarding your existing code below.
Very useful and much appreciated.
>> Sub ProcessMyArray()
>>
>> 'To pick up a range and put it into a variable,
>> 'you must use a variant. The variable then contains
>> 'an array, which is not QUITE the same thing as
>> 'a variable which is defined AS an arry. They can
>> 'be handled similarly, though.
>> Dim vnt_Source As Variant
>>
>> 'These we'll use to loop through the array.
>> Dim l_CounterRow As Long
>> Dim l_CounterCol As Long
>>
>> 'We'll use these to set where we want to put the
>> 'modified values on the third sheet.
>> Dim l_TargetRow As Long
>> Dim l_TargetCol As Long
>>
>> 'There's no need to select the sheet, or the
>> 'current range. You can just pick it up directly.
>> 'Note that I'm using the CODENAME of the
>> 'sheet here; the one that you see in
>> 'the "(Name)" field of the properties window
>> 'in the Visual Basic Editor. This isn't the "Name"
>> 'field (which is what appears on the sheet tab).
I didn't know this. I always specify the "Name" as in
Sheets("Sheet1").Range and never used sheet2.range
which certainly saves typing. I do see that if there are lots of
sheets in the workbook then it could make the code a little harder to
understand when I go back to it after a long break. On the other hand
I can see the added benefit below.
With so many ways of doing things in code I guess judgements have to
be made all the time. I can see why you guys are worth the big bucks
:c)
>> '(If you can't see the properties window in the VBE,
>> 'press [F4] to bring it up.)
Odd that. I always keep the properties window in view but never
understood why there were two Name fields. Gonna be much more nosy
from now on.
>> 'Using codenames ensures that your program runs
>> 'even if a user renames the sheet. And it's shorter
>> 'to type.
>> vnt_Source = Sheet2.Range("A3").CurrentRegion
Nice.
>> 'Don't assume that the "current region" around A3
>> 'will return an array. What if there are no values
>> 'around it? If that's the case, only the value of
>> 'the single cell A3 will be assigned to the variable.
>> 'Let's check.
I should have included some check. Oops.
>> If IsArray(vnt_Source) Then
>> 'If it's an array, let's process it. In this
>> 'case, we'll multiply each value by 10.
>> 'We'll do that by stepping through each value.
>> '(There may in fact be a faster way of doing this,
>> ' but one thing at a time... Besides, I want to
>> 'demonstrate something.)
You've got me salivating now. If there's a faster way please please
let me know. It's the procedure that i'm after and why it's faster.
>> 'Don't check the number of rows and columns.
>> 'Instead use boundary checking. This can be done
>> 'with the UBound and LBound (Upper and Lower
>> 'Boundary) functions.
No problem with using LBound and UBound functions so far. Only used
them seriously in the last two weeks though.
Very happy.
>> 'OK, let's figure out where the target range is.
>> 'One gotcha here; most arrays in VBA start from a base of 0.
>> 'When you copy a range, it starts from a base of *1*
>> 'even if you have Option Base 0 at the head of the module.
>> '(Look that up in VB Help if you're interested.)
I always have Option Base 1 at the head of the module and do list the
variables there too. Not obvious from the code I submitted because I
had to basically rewrite some of the code to make it more readable and
more obvious what I was having difficulty with. I should have spent
more time on it and made it clearer. Nice for me to know for the
future.
>> 'We'll start at A1 on the third sheet, so all we need
>> 'are the END coordinates.
>>
>> l_TargetRow = UBound(vnt_Source, 1)
>> l_TargetCol = UBound(vnt_Source, 2)
>>
>> 'Now let's plonk that down on sheet3.
>> 'This time we DO need to select the sheet.
>> Sheet3.Select
>> 'And in one step...
>> Sheet3.Range(Cells(1, 1), Cells(l_TargetRow, l_TargetCol)) _
>> = vnt_Source
>> 'et voila.
>> End Sub
Superb stuff. Four ways of plonking a variant (containing an array).
My toolbox is getting much more useful. Thanks again.
>> ------------------------------------------------
>> >I have spent a lot of time searching through the archives and
>> >elsewhere but my books especially do not go into this in the depth I
>> >need to solve the problems below.
>> >
>> >This is what I have done so far:
>> >
>> >Sub ExtractFromSheetOne()
> ' this works
If the module header is set at Option Base 1 and the variables are
dimmed there.
>> >'Put focus on SheetOne (Not sure if these 3 lines are best, maybe one
>> >'line of code is faster)
>> >Sheets("SheetOne").Select
>> >Range("A3").Select
>> >Range("A3").Activate
>>
>> No lines of code is faster still. As per my example above, you don't
>> need to do this.
>> >'determine the size of the data range
>> >Dim Test As Range
>>
>> Your Dims should all go at the head of your procedure, IMHO,
>> regardless of where they are first used. This is just a style thing,
>> but you'll find that most programmers do it that way so that it's
>> easier to see all of the variables in one hit and change them if
>> necessary.
No problem. I do this but screwed up when I rewrote the code for
sending to microsoft.public.excel.programming. Still too much of a
newbie I guess. In future I will check more thoroughly. I know what
a pain it is to run someone else's code and having to tweak it because
the module header info was missing.
>> >Set Test = ActiveCell.CurrentRegion
>>
>> You haven't declared the following two variables. If you have Option
>> Explicit set at the head of your module (see VBA help), the procedure
>> won't compile. If you DON'T have it set, the variables will be
>> declared implicitly, but will be vulnerable to being mis-typed. IMHO
>> you should always use Option Explicit. (In the VBE, go to Tools ->
>> Options -> Editor and check "Require Variable Declaration".
Sorry about that. I do have my variables declared implicitly. Unless
it's just a counter for a sub or function which is scrapped once the
sub or function has been used. I've so many counters in my module
header i'm gonna have to do some new year cleaning up.
>> >NumberofRows = Test.Rows.Count
>> >NumberofColumns = Test.Columns.Count
>> >
>> >'prepare range for copying (whatever its size)
>> >Dim MyRange As Range
>> >Set MyRange = ActiveSheet.Range(Cells(3, 1), _
>> >Cells(NumberofRows, NumberofColumns))
>>
>> As noted above, you don't need to calculate the rows and columns
>> first.
Thanks :c)
>> >'copy range values into SheetOneArray
>> >'these values will be integers
>> >SheetOneArray = MyRange.value
>> >Set MyRange = Nothing 'frees up memory now that MyRange has been used.
>> >
>> >End Sub
>> >
>> >Sub DoSomethingToData()
>> >
>> >'do something to each row of data in SheetOneArray
>> >For N = 1 To NumberofRows - 2
I used NumberofRows - 2 because the top two rows of the sheets in the
workbook have a description of the purpose of the sheet and column
headings. So my data range starts at Range("A3").
>> OOPS! Big "gotcha" there. The variable NumberOfRows was implicitly
>> created in the ExtractFromSheetOne procedure. It's "local" to that
>> procedure. DoSomethingToData doesn't know it existed, much less what
>> its value was. You'd need to either declare the variable at module
>> level (look up "Scope" in VBA help for an explanation) or better still
>> pass it to this procedure as an argument. (Unless you put the whole
>> process in one procedure as I've done.)
Sorry, my sloppy conversion. Scope isn't a problem for me. I like
the additional comment on passing to a procedure as an argument.
>> >'do something to each row in SheetOneArray eg sum integers, multiply
>> >integers
>> >'anything really to create input for a 2 dimensional array
>> >'store results in SheetTwoArray
>> >'preserve results in SheetTwoArray
>> >'increase size of SheetTwoArray ready for next iteration
>> >
>> >Next N
>> >
>> >End Sub
>> >
>> >Sub PutArrayOntoSheetTwo()
>> >'ActiveCell at Range("A3")
>> >
>> >'Unknown
>> >
>> >End Sub
>> >
>> >This code does not do anything particularly useful but has been put
>> >together to try and help me understand the following:
I didn't put it together very well. My apologies to all the readers
who see this for years to come 8-(
>> >copy range from SheetOne into an Array (in one block)
>> >do something to each row of data in the array
>> >create SheetTwoArray
>> >put results into a SheetTwoArray
>> >increase size of SheetTwoArray
>> >when all action is done on all rows in SheetOneArray
>> >store SheetTwoArray onto SheetTwo in one block rather than looping
>> >through values
>> >
>> >Happy New Year
>> >
>> >Frank
>>
>> If you need any clarification, just post again with the details.
>>
>> ---------------------------------------------------------
>> Hank Scorpio
>> scorpionet who hates spam is at iprimus.com.au (You know what to do.)
>> * Please keep all replies in this Newsgroup. Thanks! *
Thanks again for your help and I will come back soon. Lots of
learning to do now that my toolbox has a few more goodies in it.
Frank
Frank Smith
Just to round off the learning curve a little and you did ask me to
come back if I needed anything explaining. heh heh
>>> Sub ProcessMyArray()
>>>
>>> 'To pick up a range and put it into a variable,
>>> 'you must use a variant. The variable then contains
>>> 'an array, which is not QUITE the same thing as
>>> 'a variable which is defined AS an arry. They can
>>> 'be handled similarly, though.
1. How different is a variable that contains an array compared to an
array that contains variables.
I sort of visualise them as a box that contains a filing cabinet (with
drawers) and a filing cabinet that contains drawers. The drawers are
the variables. I have no idea what is being set up in memory though
for the variable that contains an array. Is the variable containing
the array pointing to the array that's located somewhere else.
2. How similarly can they be handled?
If you could substitute your code below with the appropriate
statements i'd be on cloud nine. I've copied your code below so you
don't need to mess around with cutting and pasting.
Comparing the code really does help.
Many thanks
Frank
------------------------------Hanks Code ----------------------------
Sub ProcessMyArray()
Dim vnt_Source As Variant
'These we'll use to loop through the array.
Dim l_CounterRow As Long
Dim l_CounterCol As Long
'We'll use these to set where we want to put the
'modified values on the third sheet.
Dim l_TargetRow As Long
Dim l_TargetCol As Long
vnt_Source = Sheet2.Range("A3").CurrentRegion
If IsArray(vnt_Source) Then
'If it's an array, let's process it. In this
'case, we'll multiply each value by 10.
'We'll do that by stepping through each value.
'OK, let's figure out where the target range is.
'We'll start at A1 on the third sheet, so all we need
Hank Scorpio
<Frank...@btclosedworld.com> wrote:
>Much appreciation guys for the helpful and very useful feedback.
>
>Unfortunately, Hanks' reply has not yet appeared on the btopenworld
>server. Only realised this when I saw Dave's reply. Went to Google
>groups to view the archives for microsoft.public.excel.programming and
>had to extract the response from there.
>
>My responses will therefore have to be put into this message which may
>look a bit odd.
>
>Comments below:
[Much Snippage] Unfortunately I'm a little light-on for on-line time
tonight, so there are a couple of things that I may have to come back
to later. I'll try to cover as much as I can in the time I've got.
>On Sat, 28 Dec 2002 09:22:15 -0600, Dave Peterson <ec3...@msn.com>
>wrote:
>>
>>Hank Scorpio wrote:
>>>
>>> On Sat, 28 Dec 2002 06:59:53 +0000 (UTC), Frank Smith
>>> <Frank...@btclosedworld.com> wrote:
>>> 'There's no need to select the sheet, or the
>>> 'current range. You can just pick it up directly.
>>> 'Note that I'm using the CODENAME of the
>>> 'sheet here; the one that you see in
>>> 'the "(Name)" field of the properties window
>>> 'in the Visual Basic Editor. This isn't the "Name"
>>> 'field (which is what appears on the sheet tab).
>
>I didn't know this. I always specify the "Name" as in
>Sheets("Sheet1").Range and never used sheet2.range
>which certainly saves typing. I do see that if there are lots of
>sheets in the workbook then it could make the code a little harder to
>understand when I go back to it after a long break.
Not necessarily. Remember that you can change the code name to
whatever you want at design time, and I usually do. Generally I give
each sheet the same Name and Codename, which makes it easy to follow.
>On the other hand
>I can see the added benefit below.
I look at it the other way around; protecting your code from users who
may (accidentally or otherwise) rename the sheet is the primary
benefit IMHO. The shorter syntax is the bonus.
>With so many ways of doing things in code I guess judgements have to
>be made all the time. I can see why you guys are worth the big bucks
>:c)
I wish 8^>
I'll have to come back to this one; I haven't tested it out properly
and unfortunately don't have time to tonight.
>>> 'OK, let's figure out where the target range is.
>>> 'One gotcha here; most arrays in VBA start from a base of 0.
>>> 'When you copy a range, it starts from a base of *1*
>>> 'even if you have Option Base 0 at the head of the module.
>>> '(Look that up in VB Help if you're interested.)
>
>I always have Option Base 1 at the head of the module
OK, a word of caution here; when I was starting out I always found
base 1 to be more intuitive as well and couldn't understand why all
arrays weren't base 1 anyway. HOWEVER you need to be aware that VB/VBA
is rather freakish in allowing such a base in the first place. More
importantly, in the next incarnation of Visual Basic (VB.Net) non-base
0 arrays are NOT allowed and the Option Base statement is not
supported. (http://www.mvps.org/vb/rants/VBTransition2.doc) There's no
way of telling when or if VB.NET (or an incarnation of it) is going to
move across and replace VBA, but I'd be more inclined to bet that it
WILL happen at some point. And if/when it does, you may eventually
find yourself with an awful lot of code to rewrite. Accordingly my tip
would be to get used to writing code for 0 base arrays, and treat base
1 arrays as a freakish exception. Once you've done it for a while
you'll find that it comes naturally. It's advice that you can take or
leave at your option, but it's always wise to keep an eye on the
future.
>and do list the
>variables there too. Not obvious from the code I submitted because I
>had to basically rewrite some of the code to make it more readable and
>more obvious what I was having difficulty with. I should have spent
>more time on it and made it clearer. Nice for me to know for the
>future.
That's cool. I'm not very partial to using module scope or global
scope variables myself; it's too easy to lose track of what any given
procedure is doing to them. (Sometimes they're unavoidable, but not
often.) If I need to share values between procedures I prefer to pass
the values as arguments, thus keeping the scope of any variable as
tight as possible. However that's mainly a style thing as well.
And now we continue on to your next post!
Hank Scorpio
<Frank...@btclosedworld.com> wrote:
And now, after a slight delay caused by a computer crash, I come to
the other post.
>Hi Hank,
>
>Just to round off the learning curve a little and you did ask me to
>come back if I needed anything explaining. heh heh
Well, I asked for it I guess. 8^>
>>>> Sub ProcessMyArray()
>>>>
>>>> 'To pick up a range and put it into a variable,
>>>> 'you must use a variant. The variable then contains
>>>> 'an array, which is not QUITE the same thing as
>>>> 'a variable which is defined AS an arry. They can
>>>> 'be handled similarly, though.
>
>1. How different is a variable that contains an array compared to an
>array that contains variables.
Ah, the key word isn't variable, but VariaNT; that is, the "universal"
data type for VB/VBA. A variable that is of the data type "variant"
can hold anything; long integers, strings, objects... or arrays.
>I sort of visualise them as a box that contains a filing cabinet (with
>drawers) and a filing cabinet that contains drawers.
Hmm, not a bad analogy.
>The drawers are
>the variables.
The "drawers" are more commonly known as the *elements* of the array.
I grant you that each "drawer" (element) is in a sense a variable in
its own right since it's effectively a location in the computer's
memory that can store data, but I think it may be a little confusing
to think of them that way. It's up to you though.
> I have no idea what is being set up in memory though
>for the variable that contains an array. Is the variable containing
>the array pointing to the array that's located somewhere else.
Frankly I'm not sure about the internal mechanics of it, because it's
not germane to the applications that I'm writing. However VBA does a
lot of "behind the scenes" processing when handling variants, and I
think it's LIKELY that a variant type variable which picks up an array
merely contains a pointer to a memory space that is allocated to store
that array. The variant itself, after all, must be in a position to
handle ANY type of data. If you had a variant variable which contains
an array, and it were to be suddenly assigned a long integer, it would
be wasteful for the variable itself to continue holding on to the
necessary memory to contain the array that it previously held. That's
why I'm guessing that it's just a pointer. But don't quote me on that;
and, more importantly, it's not going to matter as long as you're
working in VB/A.
>2. How similarly can they be handled?
>
>If you could substitute your code below with the appropriate
>statements i'd be on cloud nine. I've copied your code below so you
>don't need to mess around with cutting and pasting.
Alas I don't really have time for that tonight, but more to the point
I'm not sure that it would really be all that enlightening. Hopefully
the following will be moreso, however.
Let's say that we're declaring a conventional array. Normally you know
two things; what data type it will hold, and how many elements it will
contain. Therefore you declare it as, say:
Dim sa_MyStringArray(5) as String
That gives us 6 storage spaces (5 in your case since you use Option
Base 1), and each of those storage spaces can contain a string and
ONLY a string. (Though if you pass a numeric value, VB will typically
do an implicit conversion to change it into a string.) VB assigns
storage space for that array based on what you've told it it contains.
Sometimes you don't know HOW many elements you are going to have, so
you can declare a dynamic array.
Dim sa_MyStringArray() as String
When you find out how many elements you need to have, you can use a
Redim statement to create the number you want:
ReDim sa_MyStringArray(4)
Note that you don't need to specify the data type; VB knows that from
the original declaration.
Redimming destroys the data that is already there, so you can use the
Preserve keyword to keep any existing data in the array.
ReDim Preserve sa_MyStringArray(5)
Again, you still end up with an array of string elements.
The key point in all cases is that when you dimensioned the variable
in the first place, your use of the brackets at the end of the
variable name tells VB "this variable WILL contain an array".
On the other hand,
Dim vnt_Source As Variant
does NOT create an array. It merely creates a single variable of the
data type Variant. And, as noted above, that data type can hold
anything.
When it IS assigned an array, (as it was through "vnt_Source =
Sheet2.Range("A3").CurrentRegion"), the array that it is assigned will
itself be AN ARRAY OF VARIANTS. It won't have the fixed data type of
(most) assays. (It IS possible to declare an array variable of variant
type (that is, Dim vnta_Tmp(5) as Variant) , but it's generally
wasteful of resources to do so. I've heard of tests where using
variant data types can be 30% slower than using "hard" data types like
Long or Double.)
So getting back to your question of how similarly they can be handled,
this summarises the two primary differences between an array variable,
and a variant variable that contains an array:
- First, the array variable is always an array. It's always a "filing
cabinet", though if it's dynamic the number of drawers it has can
change. A variant variable can contain anything; it may be an array,
it may not. It's a box which can hold a filing cabinet, but doesn't
have to. That's why you need to do the IsArray function test before
you start doing things which assume that it DOES contain an array,
like using UBound or LBound functions. You don't have to worry about
that with a "true" array. (Though you'll still get an error if you use
UBound on a dynamic array that you haven't set a number of elements
for yet.)
- Second, the elements of the variant variable's array will themselves
be variants. They may be numbers, they may be strings. Unlike a "true"
array variable, you have no control over what data types go into the
elements. That's why I put the "If IsNumeric(vnt_Source(l_CounterRow,
l_CounterCol))" test in there; if any of the cells in the current
region contained text and you tried to multiply them by 10, you'd get
a type mismatch error.
Accordingly if I WERE to rewrite the code below, it would have to be
done as follows:
- First the array variable would have to be declared as a dynamic
type, because we wouldn't know how many rows or columns were in the
current region when we started. And what data type? We'd have to leave
it as a Variant type (as per an example I gave earlier) because we
don't know what the Current Region's cells may contain.
- Then we'd have to size up the Current Region the way you did in your
original example.
- Then we'd need to Redim the array variable using the sizes that we
determined above, then assign the Current Region to it.
- We wouldn't need to do the IsArray test, because we know it's an
array.
Aside from that, I can't think of anything that would be different. It
would, however, be slower.
Frank Smith
Just want to thank you for the extremely useful support you have given
me. I admit I was a little 'greedy' for info. Very very useful.
Again thanks.
Happy New Year to you.
Frank