Arrays

[David]

hi Group,

I am have a hard time with the syntax for setting up an Array.
This is what I have so far:
Dim CloseChanges(), ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
It did not like that I was using a variable to start with, so I went to the
ReDim syntax, but it still does not like it. The Array has only 2 data points
it is trying to pull in.

After it runs, I went to the Immediate Window and tried this:
?ThisClose(1)
But I get a message "Expect Array". Not sure wherre I am going wrong?

Thanks,
David

 

[Alan Beban]

hi Group,

I am have a hard time with the syntax for setting up an Array.
This is what I have so far:
Dim CloseChanges(), ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
It did not like that I was using a variable to start with, so I went to the
ReDim syntax, but it still does not like it. The Array has only 2 data points
it is trying to pull in.

After it runs, I went to the Immediate Window and tried this:
?ThisClose(1)
But I get a message "Expect Array". Not sure wherre I am going wrong?

Thanks,
David

What is it that you expect to happen that isn'/t happening?

Alan Beban

 

[TomPl]

What are you trying to do with the array? You defined an array
"CloseChanges()" then set the dimension of the array equal to the value of an
unknown variable "YearsTotal". Then you did nothing else with the array.
What do you want to do with the array?

Tom

 

[David]

Hi,
Thanks for your response. This is what I have so far, but it is not yielding
what I expect still:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
Stop

After the stop I go to the immediate window and type:
?ThisClose(1)
I am expecting the data stored in the Array to now be available to me, but I
get:
"Compile Error:"
"Expected Array"

The Option Base is set. In the Immediate Window, if I type ?CloseChanges(1),
wht I get is 12:00:00 AM. It does not matter which number I try and access,
it is alway 12:00:00 AM.

ThisDate
and
ThisClose
Are the elements I need to access.

Thanks,
David

 

[Charlie]

I agree with you. I always use Option Base 1 because looping from 1 to
nItems is always more intuitive. In fact, I wish Base 1 were the default.
It seems to make more sense.

Likewise regarding error checking: After 23 years of trapping errors,
passing error flags, writing handler code, etc., I finally burned out. When
I think of the hundreds of lines of code I've written that essentially never
get executed it astounds me.

I've spent the last 5 or 6 years simply letting the errors appear and doing
a better job of debugging. Now I mostly use On Error Resume Next. I get
more done in less time.

My functions (mostly string and string arrays) return empty strings or empty
arrays for errors. For arrays I needed to write a UBnd function because
UBound will fail if an array is empty (undimensioned).

Option Base 1
Dim MyArray() As String
nItems = UBnd(MyArray) 'returns zero - it also accepts the Optional
Dimension parameter same as UBound

This has been VERY useful.

 

[Charlie]

You still haven't used CloseChanges anywhere in your code. You need to put
something in those elements of the array somewhere.

Dim CloseChanges() As Date
Dim ThisDate As Date
Dim ThisClose As Double

ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select
CloseChanges(y)= ThisDate
Next y

....or something like that. We don't know what you are using CloseChanges for.

Charlie

 

[Douglas Klimesh]

To start with you you are not declaring CloseChanges() as Date. Use instead:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double

Also, note that by default arrays are dim'ed starting with 0. So if you
ReDim CloseChanges(10) the array is actually CloseChanges(0) ..to..
CloseChanges(9). Use the directive Option Base 1 at the very beginning
of your module code to have an array that goes from 1 to 10 instead of 0
to 9. Or you could: ReDim CloseChanges(1 To YearsTotal)

I always use Option Base 1 because if there is an problem with my index
variable its value will most often be zero, which will give me a VBA
error and more obviously alert me to my programming problem.

 

[David]

Hi Again,

I was under the mistaken belief that I would be able to access these
elements later in the code. I have changed the code to "Do Something" with
the elements.

If I use "Preserve", is there not a way, once out of the loop, to refer to
each of the individual elements? I guess what I would like to do is use the
elements, variables, in other situations, later in the code.

Thanks,
David

 

[Charlie]

Redim Preserve is used to change the size of (usually to expand) the array
without wiping out the data. Redim will erase the data when it resizes the
array.

When you exit your loop all of the data will be in your array, you won't
need to use Redim Preserve unless you are planning on appending more items to
the end of the array. To access the array elements you need to reference
them individually, or in a loop such as:

ThisDate = CloseChanges(1)

or

For i = 1 to YearsTotal
Cells(1,i) = CloseChanges(i)
Next i

These are just some examples.

 

[Charlie]

Another thing: you can only access these array items in the subroutine in
which the array was declared. If you want to "see" this array in another
subroutine you will need to declare the array outside of the sub where it is
delcared now. Move the statement

Dim CloseChanges() as Date

out of your sub and put it at the top of a standard module like this

Public CloseChanges() as Date

 

[Douglas Klimesh]

David,
Only CloseChanges is an array. The immediate window should print the
date with ?ThisClose . You still aren't setting CloseChanges() to
anything in your For loop. Don't you want something like
CloseChanges(y) = ThisClose ? 12:00:00 AM is like 0. It means you
haven't set the date variable to anything.

Charlie,
Can you give us your UBnd code. It sounds like a useful function.

 

[Alan Beban]

. . . note that by default arrays are dim'ed starting with 0. So if you
ReDim CloseChanges(10) the array is actually CloseChanges(0) ..to..
CloseChanges(9).

No. The lower bound is 0, the upper is 10 (not 9).

Alan Beban

 

[David]

Hi,

I think yoou have come closer to any in what I am looking for. The code so
far is:

Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
z = 0
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ReturnAddress = ActiveCell.Address
Range("AA1").Select
ActiveCell.Offset(YearsTotal + 1 - z, 0).Value = ThisDate
ActiveCell.Offset(YearsTotal + 1 - z, 1).Value = ThisClose
z = z + 1
Range(ReturnAddress).Select
ActiveCell.Offset(-52, 0).Select
Next y

But you are right, I have not "Set" anything. I have placed the data in
another part of the worksheet. This is ALL in one module. I was hoping to get
to the end of the "collection" of data, then put it where I wanted it. I was
also hoping I might be able to access "elements" of the data as needed, using
it in several places.

I have not "Set" anything or created any Objects.

Thank you for your help, definitely to closest to what I was trying to
achieve.

Thanks Again,

 

[Charlie]

Sure, there's really not much to it. If there is an error, or Arg1 is not an
array, the function simply "falls through" the If statement thereby returning
zero. Keep in mind this function works only if you use Option Base 1.

Public Function UBnd(Arg1 As Variant, Optional iDim As Long = 1) As Long
'
' returns Ubound of an array or zero if the array is undefined or empty
'
On Error Resume Next
If IsArray(Arg1) Then UBnd = UBound(Arg1, iDim)
'
End Function