Calling Function with Range as Parameters-CodePudding

I am trying to call a function with parameters as follows.

Private Sub Load_Data(File_Path As String)
    Dim data() As String
    'data = data_range_str(Range("rng_tbl_cell_ref_ttl_rows"), Range("rng_tbl_cell_ref_st_row"), Range("rng_CV_Col_No_A1"), Range("rng_cell_ref_sheet"))
    data = data_range_str(67, 6, 3, "Cell_Ref")
End Sub

Function data_range_str(row_count As Integer, offset_row As Integer, col As Integer, w_sheet_name As String) As String()
    Dim i As Integer
    Dim data_range_string() As String
    ReDim data_range_string(row_count - 1) As String
    
    If row_count > 1 Then
        For i = 0 To row_count - 1
            data_range_string(i) = ThisWorkbook.Worksheets(w_sheet_name).Cells(offset_row   i, col)
        Next i
    ElseIf row_count = 1 Then
        data_range_string(0) = ThisWorkbook.Worksheets(w_sheet_name).Cells(offset_row, col)
    End If
    data_range_str = data_range_string
End Function

When I call the data_range_str function with excel range as commented in the codes, it shows [Error 1004:Method 'Range' of object '_Global' failed]. But when I call the function with hardcoded parameters as in above code, it is OK.

Please help me to be able to call with variables.

CodePudding user response：

Not all details are given, so I had to guess a bit. This is my guess:

data = data_range_str(Range("rng_tbl_cell_ref_ttl_rows").Rows.Count, Range("rng_tbl_cell_ref_st_row").Row, Range("rng_CV_Col_No_A1").Column, Range("rng_cell_ref_sheet").Text)

It might be wrong, but I think you get the picture. A range is not a value, but an object with a lot of properties, such as row, column and a lot more.

CodePudding user response：

This should work. When working with arrays defined from ranges, they need to be of Variant type, even though the contents are still of String type (or Double). Also the arrays need to be 1 based and have a rank of 2. These are just conventions that Microsoft has introduced. in their design of Excel/VBA

ALso no need to loop through the data, but rather just use the .Resize() function to reference multiple cells at once.

Private Sub Load_Data(File_Path As String)
    Dim data() As Variant, first as String
    data = DataRangeVals(67, 6, 3, "Cell_Ref")
    first = data(1,1)
End Sub

Public Function DataRangeVals(ByVal row_count as Long, byVal offset_row as Long, ByVal col as Long, ByVal w_sheet_name as String) as Variant()
    Dim data() as Variant
    If row_count>1 then
        data = Sheels(w_sheet_name).Cells(offset_row,col).Resize(row_count,1).Value
    ElseIf row_count=1 Then
       ReDim data(1 to 1, 1 to 1)
       data(1) = Sheels(w_sheet_name).Cells(offset_row,col).Value
    Else
       data = Array()  'Empty array
    End if
    DataRangeVals = data
End Function

There is a caveat in the op code, when it uses .Cells() as opposed to .Offset(), depending on the numbers passed to the function. With offset_row=6 and col=3 the corresponding cell address is

Cells(6,3) => "C6"
Offset(6,3) => "D7"

I just want to point out if the numbers are offsets from the first cell then you need to use the .Offset() function, or if the numbers are index values in the worksheets, then .Cells() is what is needed.

I want to offer a better alternative, that uses a source range, and the row count. So the referencing of the data is done outside the function where you have more control of how it is done.

Private Sub Load_Data(File_Path As String)
    Dim data() As Variant, first as String
    data = DataRangeVals(Worksheets("Cell_Ref").Cells(6,3), 67)
    first = data(1,1)
End Sub

Public Function DataRangeVals(ByVal r_src as Range, ByVal row_count as Long, Optional ByVal col_count As Long = 1) as Variant()
    Dim data() as Variant
    If row_count>1 Or col_count>1 then
        data = r_src.Resize(row_count,col_count).Value
    ElseIf row_count=1 And col_count=1 Then
       ReDim data(1 to 1, 1 to 1)
       data(1) = r_src.Value
    Else
       data = Array()  'Empty array
    End if
    DataRangeVals = data
End Function