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

Question

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!

"I believe subs would require either buttons or manual inputting of range areas in the code." This is incorrect. A Sub can have arguments just like a Function. — BigBen
– BigBen, Commented Sep 27, 2019 at 13:35
I would change these to Subs personally - but remove the parentheses around rangeA. — BigBen
– BigBen, Commented Sep 27, 2019 at 13:40
Re: my first comment, how would using these as Functions do away with "manual inputting of range areas in the code"? How are you calling the first function? — BigBen
– BigBen, Commented Sep 27, 2019 at 13:48
IMHO you should get a run time error 424 in the line Module1.replaceCellWithZero (rangeA) as the parenthesis will first evaluate rangeA and then you pass a Variant instead of a range to the function. So, as BigBen wrote, remove the parenthesis. — Storax
– Storax, Commented Sep 27, 2019 at 13:53

Storax · Accepted Answer · 2019-09-27 15:34:36Z

1

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

answered Sep 27, 2019 at 15:34

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nintskari Over a year ago

Thanks. I wasn't familiar with the evaluation thing. This helped me understand it.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related