VBA methods
 

Can anyone suggest a vba built in method that is similiar to
ArrayObject.Rows.Count and ArrayObject.Columns.Count that will count the rows
and columns of the area in the worksheet where the returned array will be
placed? I want to select the dimensions of the returned array to suit my
personal whim when I execute the VBA Function.
Les
--
Thanks for your help

VBA methods
 

Hi LesHurley,

Can anyone suggest a vba built in method that is similiar to
ArrayObject.Rows.Count and ArrayObject.Columns.Count that will count the rows
and columns of the area in the worksheet where the returned array will be
placed? I want to select the dimensions of the returned array to suit my
personal whim when I execute the VBA Function.


ubound(ArrayObject,1) and ubound(ArrayObject,2)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

VBA methods
 

Jan; I dont know how to use that. I cant find "unbound" in VBE help.
Here is what I want to do:

Function Test(SomeArray)
Dim r As Integer, c As Integer
r=Test.Rows.Count 'Or some such function
c=Test.Columns.Count 'Ditto
'Some code to fill the Test Array
End Function
--
Thanks for your help


"Jan Karel Pieterse" wrote:

Hi LesHurley,

Can anyone suggest a vba built in method that is similiar to
ArrayObject.Rows.Count and ArrayObject.Columns.Count that will count the rows
and columns of the area in the worksheet where the returned array will be
placed? I want to select the dimensions of the returned array to suit my
personal whim when I execute the VBA Function.


ubound(ArrayObject,1) and ubound(ArrayObject,2)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

VBA methods
 

Jan Karel Pieterse wrote:
Hi LesHurley,


Can anyone suggest a vba built in method that is similiar to
ArrayObject.Rows.Count and ArrayObject.Columns.Count that will count the rows
and columns of the area in the worksheet where the returned array will be
placed? I want to select the dimensions of the returned array to suit my
personal whim when I execute the VBA Function.



ubound(ArrayObject,1) and ubound(ArrayObject,2)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Or more generally, to accommodate 0-based arrays

UBound(ArrayObject,1) - LBound(ArrayObject,1) + 1

Alan Beban

VBA methods
 

oops; I misspelled that. I did find Ubound and Lbound i help, but that
doesn't seem to apply. Can you give an example that will do what I suggest
in my last post?
--
Thanks for your help


"Jan Karel Pieterse" wrote:

Hi LesHurley,

Can anyone suggest a vba built in method that is similiar to
ArrayObject.Rows.Count and ArrayObject.Columns.Count that will count the rows
and columns of the area in the worksheet where the returned array will be
placed? I want to select the dimensions of the returned array to suit my
personal whim when I execute the VBA Function.


ubound(ArrayObject,1) and ubound(ArrayObject,2)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

VBA methods
 

Yeah, it applies.

r = UBound(SomeArray,1) - LBound(SomeArray,1) + 1
c = UBound(SomeArray,2) - LBound(SomeArray,2) + 1

Alan Beban

LesHurley wrote:
oops; I misspelled that. I did find Ubound and Lbound i help, but that
doesn't seem to apply. Can you give an example that will do what I suggest
in my last post?

VBA methods
 

OK; here is what I wrote. the program compiles Ok but when run doesnt get as
far as the MsgBox before it crashes. What I did to execute it was to
highlight a blank area on the worksheet and write {=test()} and press
Ctrl+Shift+Enter as usual.

Function Test()
Dim A() As Integer
Dim i As Integer, j As Integer, r As Integer, c As Integer

r = UBound(Test, 1) - LBound(Test, 1) + 1
c = UBound(Test, 2) - LBound(Test, 2) + 1

MsgBox "Got 'em ."
ReDim A(r, c)
'For i = 1 To r
'For j = 1 To c
'A(i, j) = i + j - 1
'Next j
'Next i
Test = A



End Function
--
Thanks for your help


"Alan Beban" wrote:

Yeah, it applies.

r = UBound(SomeArray,1) - LBound(SomeArray,1) + 1
c = UBound(SomeArray,2) - LBound(SomeArray,2) + 1

Alan Beban

LesHurley wrote:
oops; I misspelled that. I did find Ubound and Lbound i help, but that
doesn't seem to apply. Can you give an example that will do what I suggest
in my last post?

VBA methods
 

ps: I forgot to mention, I'm using Option Base 1
Thanks for your help


"Alan Beban" wrote:

Yeah, it applies.

r = UBound(SomeArray,1) - LBound(SomeArray,1) + 1
c = UBound(SomeArray,2) - LBound(SomeArray,2) + 1

Alan Beban

LesHurley wrote:
oops; I misspelled that. I did find Ubound and Lbound i help, but that
doesn't seem to apply. Can you give an example that will do what I suggest
in my last post?

VBA methods
 

Well, you said you were using Function Test(SomeArray); now it turns out
you're not using some array at all, but simply selecting a range on the
worksheet. The following will work:

Function Test()
Dim A() As Integer
Dim i As Integer, j As Integer, r As Integer, c As Integer
r = Selection.Rows.Count
c = Selection.Columns.Count
'r = UBound(Test, 1) - LBound(Test, 1) + 1
'c = UBound(Test, 2) - LBound(Test, 2) + 1

MsgBox "Got 'em ."
ReDim A(r, c)
For i = 1 To r
For j = 1 To c
A(i, j) = i + j - 1
Next j
Next i
Test = A
End Function

Alan Beban

LesHurley wrote:
OK; here is what I wrote. the program compiles Ok but when run doesnt get as
far as the MsgBox before it crashes. What I did to execute it was to
highlight a blank area on the worksheet and write {=test()} and press
Ctrl+Shift+Enter as usual.

Function Test()
Dim A() As Integer
Dim i As Integer, j As Integer, r As Integer, c As Integer

r = UBound(Test, 1) - LBound(Test, 1) + 1
c = UBound(Test, 2) - LBound(Test, 2) + 1

MsgBox "Got 'em ."
ReDim A(r, c)
'For i = 1 To r
'For j = 1 To c
'A(i, j) = i + j - 1
'Next j
'Next i
Test = A



End Function

VBA methods
 

Yep; that works fine.. Thank you very much.
--
Thanks for your help


"Alan Beban" wrote:

Well, you said you were using Function Test(SomeArray); now it turns out
you're not using some array at all, but simply selecting a range on the
worksheet. The following will work:

Function Test()
Dim A() As Integer
Dim i As Integer, j As Integer, r As Integer, c As Integer
r = Selection.Rows.Count
c = Selection.Columns.Count
'r = UBound(Test, 1) - LBound(Test, 1) + 1
'c = UBound(Test, 2) - LBound(Test, 2) + 1

MsgBox "Got 'em ."
ReDim A(r, c)
For i = 1 To r
For j = 1 To c
A(i, j) = i + j - 1
Next j
Next i
Test = A
End Function

Alan Beban

LesHurley wrote:
OK; here is what I wrote. the program compiles Ok but when run doesnt get as
far as the MsgBox before it crashes. What I did to execute it was to
highlight a blank area on the worksheet and write {=test()} and press
Ctrl+Shift+Enter as usual.

Function Test()
Dim A() As Integer
Dim i As Integer, j As Integer, r As Integer, c As Integer

r = UBound(Test, 1) - LBound(Test, 1) + 1
c = UBound(Test, 2) - LBound(Test, 2) + 1

MsgBox "Got 'em ."
ReDim A(r, c)
'For i = 1 To r
'For j = 1 To c
'A(i, j) = i + j - 1
'Next j
'Next i
Test = A



End Function

VBA methods
 

Hi Alan,

The following will work:

Function Test()
Dim A() As Integer
Dim i As Integer, j As Integer, r As Integer, c As Integer
r = Selection.Rows.Count
c = Selection.Columns.Count

I guess using "Selection" in a user defined function would give interesting
results <g. Especially if I select something other than a range of cells and
Excel decides to recalc...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com