Porting VBA to VB.NET: function parameter is possibly multidimensional array

Question

I am porting Excel VBA to VB.NET. I have a function that takes a selection of data, and it may be one dimensional or two. VBA code is quite fluid about using a variable as either a 1-D or 2-D array, but VB.NET marks it as an error.

Here is the reduced code:

Public Function Stat(ByVal Data As Range) As Object
    Dim Y() As Object
    Dim Appp As New Application() ''// Very annoying

    ''//Convert worksheet range into array vector
    Y = Appp.WorksheetFunction.Transpose(Appp.WorksheetFunction.Transpose(Data))

    Dim dimensions As Integer : dimensions = NumberOfArrayDimensions(Y)

    If dimensions > 1 Then
        For i = LBound(Y) To UBound(Y)
            If VarType(Y(i, 1)) <> 0 Then

It fails with "number of indices exceeds the number of dimensions of the indexed array" on the last line.

Edit: So the question is something like, "How can I use a single variable in VB.NET as I can in Excel VBA -- having ambiguous/flexible dimensionality?" Failing that, "How would you recommend changing the code to be most natural in VB.NET?"

Answer 1

Since I don't have sample data to work with this is untested, but something like this might work:

Dim dimensions As Integer : dimensions = Y.Rank

If dimensions > 1 Then
    For i = Y.GetLowerBound(0) To Y.GetUpperBound(0)
        If VarType(Y.GetValue(i, 1)) <> 0 Then

Y.Rank will return the number of dimensions (I assume this results in the same value as the call to NumberOfArrayDimensions(Y)).

Edit: I think you will also need to change the declaration of Y to this:

Dim Y As Array

Answer 2

Public Function Stat(ByVal Data As Range) As Object
Dim Y() As Object
Dim Appp As New Application() ''// Very annoying

dim rows as Integer
dim cols as Integer

dim rowCtr as Integer = 1
dim colCtr as Integer = 1

rows = Data.Rows.Count
cols = Data.Columns.Count

Y = Appp.WorksheetFunction.Transpose(Appp.WorksheetFunction.Transpose(Data))

for rowCtr = 1 to rows
  for colCtr = 1 to cols
     Debug.Print Y(rowCtr, colCtr)
  next
next

EDIT: The idea is to use the Range object to get the bounds instead of the array.
I have not modified rest of the code (other than finding dimensions), which will require some improvements.

EDIT2: Because the Transpose returns an array which has its bounds starting from 1.
EDIT3: The code above is in VB6 style. But the idea remains same in VB.net