CODEWITHSUNDEEP
excelvba
UnfortunatelyEvil
UnfortunatelyEvil

Reputation: 23

How to pass a result of a function into a sub call?

I am having trouble with the following code. When stepping through it, both using the Sub name with no parameter parenthesis and using Call with parenthesis have the same behavior. Likewise, removing the ByRef makes no difference.

Vba enters the SetRange, sets the return to a Range, and ends, but then the next step is not entering ColumnToUpper. That is, when it hits the block of calls to ColumnToUpper, it just enters SetRange a bunch of times, but never enters any of the ColumnToUpper Subs.

'Beginning of main Sub
    ColumnToUpper SetRange(data, "Last Name")
    Call ColumnToUpper(SetRange(data, "First Name"))
    'more calls
End Sub

Sub ColumnToUpper(ByRef rng As Range)
    For i = 1 To rng.Count
        rng(i) = UCase(rng(i))
    Next i
End Sub

Function SetRange(tbl As ListObject, hdr As String) As Range
    SetRange = Range(tbl.Name + "[" + hdr + "]")
End Function

Of course, I could set a Range variable, and set it between each call to the other function, but I would prefer a more elegant method. Note, I am not just doing a huge block of known columns just being set to upper (as I could use one large range), but I narrowed it down to this easy to digest and still failing example!

Upvotes: 2

Views: 72

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

  1. You want to use & for concatenation not +
  2. You need to Set Objects:

Set SetRange ...

So:

Function SetRange(tbl As ListObject, hdr As String) As Range
    Set SetRange = tbl.Parent.Range(tbl.Name & "[" & hdr & "]")
End Function

The fact that you did not get an error pointing at the Set issue means you have poor error handling. Most likely you have On Error Resume Next somewhere in the code and it is bypassing the error and returning nothing.

Upvotes: 3

Related Questions