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

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)


"Matt Jensen" wrote:

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

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" wrote in message
...
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

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


"Tushar Mehta" wrote in message
...
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)


"Matt Jensen" wrote:

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

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)
..


"Matt Jensen" wrote:

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}

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


"Tushar Mehta" wrote in message
...
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)
.


"Matt Jensen" wrote:

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}

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. ;-)

"Matt Jensen" wrote:

{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}

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

"Tushar Mehta" wrote in message
...
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. ;-)

"Matt Jensen" wrote:

{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}

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

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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

very nice!
Cheers
Matt

"Tom Ogilvy" wrote in message
...
with Worksheets("myName")
myArray = Intersect(.UsedRange.EntireRow, .Range("A:E")).Value
End with

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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