VBA methods

[Guest]

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

 

[Jan Karel Pieterse]

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

 

[Guest]

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

 

[Alan Beban]

Hi LesHurley,




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

 

[Guest]

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?

 

[Alan Beban]

Yeah, it applies.

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

Alan Beban

 

[Guest]

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

 

[Guest]

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

 

[Alan Beban]

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

 

[Guest]

Yep; that works fine.. Thank you very much.

 

[Jan Karel Pieterse]

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