Problem with a selection in a matrix-function

[Peter Sellmeijer]

Writing a matrix function a noticed that the real selection is one row and
one column
bigger then the selection pointed by the user of that function
So, reading out the selection (and coping it in an other array) it starts
with values
above and left from the selection pointed by the user
I found this confusing, but the problem is that a selection made at the edge
of the sheet
(row 1 or column A) is not possible. It only produces errors
My question is, if this is not the proper way to readout the values of the
selected cells?
Herby you will find a function I wrote to demonstrate this phenomena.

Function SelectionControl(Selection As Range) As Variant
Dim ArrSelect()
'Reproduce all the cells that are selected
'But it seems that one row above and one column left
'are added to the selection really made
'A problem acquire when the selection starts at row "1"
'or at column "A", then only errors "#VALUE!" are the result

Dim Ra, Ka As Integer
Dim i, j As Integer
Dim ReturnColumn As Boolean
Ra = Application.Caller.Rows.Count
Ka = Application.Caller.Columns.Count

Rz = Selection.Rows.Count
Kz = Selection.Columns.Count
ReDim ArrSelect(Ra, Ka)
For j = 0 To Rz
For i = 0 To Kz
ArrSelect(j, i) = Selection(j, i)
Next i
Next j

SelectionControl = ArrSelect
End Function

 

[Tom Ogilvy]

Selecton(i,j) is a shortcut for Selection.Items(i,j). Items is 1 based, so
Selection.Items(1,1) or
Selection(1,1) is the upper left corner of the selection

Selection(0,0) is one row up and one low to the left.

You just don't seem to know that.

To use comparable indexing, use offset

Function SelectionControl(Selection As Range) As Variant
Dim ArrSelect()
'Reproduce all the cells that are selected
'But it seems that one row above and one column left
'are added to the selection really made
'A problem acquire when the selection starts at row "1"
'or at column "A", then only errors "#VALUE!" are the result

Dim Ra, Ka As Integer
Dim i, j As Integer
Dim ReturnColumn As Boolean
Ra = Application.Caller.Rows.Count
Ka = Application.Caller.Columns.Count

Rz = Selection.Rows.Count
Kz = Selection.Columns.Count
ReDim ArrSelect(Ra, Ka)
For j = 0 To Rz
For i = 0 To Kz
ArrSelect(j, i) = Selection.Offset(j,i)
Next i
Next j

SelectionControl = ArrSelect
End Function

 

[Peter Sellmeijer]

Thanks Tom for your reply,
The ofset command works in this way:
ArrSelect(j, i) = Selection(j, i).Offset(1, 1)
But at the edge of the worksheet produces still errors.
But the command "Option Base 1" (and counting from 1) solves all problems

 

[Tom Ogilvy]

My error,
It should have been

ArrSelect(j, i) = Selection(1).Offset(j,i)

Changing to option base1 and changing your loops to start at 1 will also
work, but isn't necessary