PDA

View Full Version : Solved: check Ctrl + Shift + Enter array size



clazy
10-15-2008, 08:08 PM
How can I check the array size when user expects by Ctrl + Shift + Enter? e.g.

Function test()
Dim r As Long
Dim c As Long
Dim count As Long

'Here, assign (r,c) as user input dimension
'r = 5
'c = 3

count = 1

ReDim v(1 To r, 1 To c)

For i = 1 To r
For j = 1 To c
v(i, j) = count
count = count + 1
Next j
Next i

test = v

End Function


Thanks!

Bob Phillips
10-16-2008, 12:53 AM
You can get the array size by using the UBound and LBound functions, and the dimension id.



size1 = UBound(myArray, 1) - LBound(myArray, 1) + 1


etc.

clazy
10-16-2008, 09:29 PM
Thanks for your reply!
Actually I would like to know if there's any implicit variable like 'myArray'. For example, if I select the cells A1:C5, then enter the forumla and press Ctrl + Shift + Enter, I can get the reference to the array [A1:C5] which provides row=5, column=3!
That is, to fill the comment part, with the reference to the array [A1:C5]!

Bob Phillips
10-17-2008, 12:32 AM
Application.Caler

clazy
10-19-2008, 07:33 PM
Thanks! I can finish my code finally by:

r = Application.Caller.Rows.count
c = Application.Caller.Columns.count