Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Connecting to SQL -Looking for best methods | Excel Programming | |||
Std.Dev.methods NOT WORKING | Charts and Charting in Excel | |||
Collection methods | Excel Programming | |||
Search Methods | Excel Programming | |||
Certain methods do not work | Excel Programming |