CODEWITHSUNDEEP
excelvbarange
nintskari
nintskari

Reputation: 51

How to call VBA subroutine in another module with Range parameter?

I'm making a function on Excel VBA that does many cleaning tasks on a big Excel sheet. It is supposed to contain multiple subroutines. The individual subroutines work, but I can't seem to make the calls work properly.

Public Function CleanTable(rangeA As Range)

    Module1.replaceCellWithZero (rangeA)
    Module2.removeAsterisk (rangeA)
End Function 

Public Function ReplaceCellWithZero(rangeA As Range)

    Dim thisCell As Variant
    For Each thisCell In rangeA
        If InStr(thisCell, "<") > 0 Then
            thisCell.Value2 = 0
        End If
    Next thisCell
End Function

Public Function removeAsterisk(rangeA As Range)

    Dim thisCell As Variant
    For Each thisCell In rangeA
        thisCell.Value2 = replace(thisCell, "*", "")
    Next thisCell
End Function

When I call the CleanTable function, it just doesn't seem to do anything. I couldn't find what the matter is even with debugging. It seems like it just stops at the replaceCellWithZero.

The main function is a function and not a sub because the function will need to be utilized in a big amount of different Excel documents. I believe subs would require either buttons or manual inputting of range areas in the code, which doesn't suit the needs of this thing. The subs are in different modules because the files may need to be shared separately.

Any help would be appreciated!

Upvotes: 1

Views: 2014

Answers (1)

Storax
Storax

Reputation: 12167

As I wrote in my comment the parenthesis around the parameter when calling a sub of function without the keyword Call will evaluate the parameter. In your case the result of the evaluation will either be the content of the cell if the range is just one cell or it will be an array containing the values of the range.

Maybe the following code will help clarify the issue

Option Explicit

Sub printV(v As Variant)

    Dim i As Long, j As Long
    For i = LBound(v) To UBound(v)
        For j = LBound(v, 2) To UBound(v, 2)
            Debug.Print v(i, j)
        Next j
    Next i
End Sub

Sub TestIt()

    Dim rg As Range
    Set rg = Range("A1:B2")

    Debug.Print TypeName((rg)), TypeName(rg)  ' Result is Variant and Range

    ' (rg) - the parewntheses will evaluate the range and the
    ' result is the same as v = rg
    printV (rg)

    ' This call will cause an RTE in the sub
    printV rg

End Sub

Upvotes: 1

Related Questions