Dynamic array problem

[Matt Jensen]

Howdy
I've defined a dynamic array

dim myarray() as variant
dim myvar as int
myvar =Worksheet("myname").UsedRange.Rows.Count
redim myarray(1 to myvar, 1 to 5)
myarray = Worksheet("myname").UsedRange.Value

Why then, when the usedrange is actually 4 columns wide (& 1 row deep) and
the 5th column is empty, does
UBound(myarray, 2) = 4
???
Doesn't that defy the whole point of dimensioning the array?

What am I missing?
Thanks
matt

 

[Guest]

A couple of points.

The code you've shared doesn't compile. You might want to check what that
is all about.

As far as changing the dimension of your array goes, it appears XL/VBA
redimensions the array as needed. After all, it is possible that the
specified array size is insufficient to transfer the specified range.
Effectively, your code could be simplified to:

Dim myarray() As Variant
myarray = Worksheets("sheet1").UsedRange.Value
MsgBox UBound(myarray, 2) & "," & UBound(myarray, 1)

 

[Bob Phillips]

Matt,

When I corrected the 3 syntax errors in the code, it ran find, and I got
UBound(myarray, 2) = 5 as expected.

It seems you have something else going on.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Matt Jensen]

Thanks Tushar
The problem with that is that when I then refer to myarray(i, 5) in a For
loop with i as the variable and there was originally no value for the
usedrange's first row's fifth column I get a subscript out of range error.
Therefore, I tried to define the array as 5 columns wide which I thought
would avoid this error

The code was just example code, here is code that compiles:

Sub test()
Dim myarray() As Variant
Dim myvar As Integer
myvar = Worksheets("Sheet1").UsedRange.Rows.Count
ReDim myarray(1 To myvar, 1 To 5)
myarray = Worksheets("Sheet1").UsedRange.Value
Debug.Print UBound(myarray, 2)
End Sub


Put any value in cells a1,b1,c1,d1 on Sheet1 and the immediate window will
show UBound(myarray, 2) as 4 even though you defined the array as 5 columns
wide...?? What's with this? How can I avoid an error when referring the 5
column of the array then? Preferably not with an On Error statement
Thanks
Matt

 

[Guest]

Hi Matt,

I guess you didn't get a chance to read my entire post. As I mentioned in
it, XL/VBA resizes the array as needed. So, if you have four columns of
data, the resulting array will have a 2nd dimension of 4.

As far as referring to column 5 goes, I am not sure of the intent. There is
no column 5 in the worksheet; hence there isn't one in the array. Consider
using ubound(array,2) rather than a hardcoded value such as 5. If you need
to create an extra column, consder changing the array size *after* VBA gives
you the results. Something like:

Dim x()
x = Worksheets(1).UsedRange.Value
ReDim Preserve x(LBound(x, 1) To UBound(x, 1), LBound(x, 2) To UBound(x,
2) + 1)
..

Thanks Tushar {snip}
Put any value in cells a1,b1,c1,d1 on Sheet1 and the immediate window will
show UBound(myarray, 2) as 4 even though you defined the array as 5 columns
wide...?? What's with this? How can I avoid an error when referring the 5
column of the array then? Preferably not with an On Error statement
Thanks
Matt

{snip}

 

[Matt Jensen]

Thanks Tushar
I realise the intent may seem questionable but the explanation is long
winded and complicated.
I'll give it a try so you can briefly understand.

A user of the workbook can hit a custom button to email it to another
predefined user.
There is a separate macro (named "Add") that when run, will copy 5 column
values from a row in the "source" worksheet to a blank worksheet named
"Updates" (this worksheet is made "blank" each time the app opens in the
workbook open event). So essentially this updates sheet has "updates" on it,
1 row for each "Add" in the current "session" that the user has had the
workbook open.
*If* this "add" macro has been run, then when the user hits the button to
send an email, in the email body I create an HTML table with 5 headings and
loop thru each and any "added" row on the "updates" sheet.
The fifth column holds 'comments' field values, and sometimes there may be
no comment. So if there is no entries in the comments, no matter how many
"Adds", then referring to the array's 5th column obviously gives an error.

The problem with your latest suggestion which I had already tried is that if
the "updates" worksheet is blank, it throws an error too, in your example
when you define x.
I did read your post, I just didn't see any "need" for XL/VBA to resize my
array that I explicity dimensioned nor did I understand why - I guess I'll
just have to take this fact for granted though.

Thought there'd be a simple answer. Guess I'll have to IF and On Error this
problem away!

Just stressed at the mo - sorry!
Cheers
Matt

 

[Guest]

Hi Matt,

Thanks for the explanation. I was not questioning the intent just pointing
out that it might lead to an alternative approach.

Yes, you will have to use some form of checking to see if the comments
column exists or not. Or, you could force it to exist. Create the 5 headers
in row 1 of your updates worksheet.

Also, I don't know how you are creating the body of the email. If you treat
the updates worksheet as a ADO data source, you could use the GetString
method of the ADO recordset object and get the HTML for the table in a single
step.

And, as for being stressed goes, hopefully, you'll get this problem
straightened out and lower your stress level. That is...
....
....
until the next problem comes along. ;-)

:

{snip}

I did read your post, I just didn't see any "need" for XL/VBA to resize my
array that I explicity dimensioned nor did I understand why - I guess I'll
just have to take this fact for granted though.
{snip}
Just stressed at the mo - sorry!

{snip}

 

[Matt Jensen]

Cool - thanks
Didn't realise you could use a worksheet as an ADO recordset! Interesting!
Time prevents currently though!
Thanks
Matt

 

[Tom Ogilvy]

with Worksheets("myName")
myArray = Intersect(.UsedRange.EntireRow, .Range("A:E")).Value
End with

 

[Matt Jensen]

very nice!
Cheers
Matt

with Worksheets("myName")
myArray = Intersect(.UsedRange.EntireRow, .Range("A:E")).Value
End with