How do I call a VBA Function into a Sub Procedure and declare it as a variable?

Question

I'm trying to call a function and declare it as a variable in my current sub.

I tried changing the declaration to Public but I don't know what the problem is.

I want to declare rngResult to the result of my function

Dim i As Integer
For i = 1 To GPN.Count

    lookupValue1 = GPN.Rows(i).Value
    lookupValue2 = Email.Rows(i).Value

Dim rngResult As Range

Set rngResult = DualFindMod(lookupValue1, lookupValue2, lookupRange1, lookupRange2)

rngResult.Offset(0, 2).Value = Union(GPN.Rows(i), Email.Rows(i)).Value

Next i

Code of the function

Public Function DualFindMod(lookupValue1 As String, lookupValue2 As String, lookupRange1 As Range, lookupRange2 As Range) As Range
'Returns selection of lookupRanges based on matching lookupValues.

'A. Set parameters for subroutine arguments

'B. Find & match lookupValues to lookupRanges, then select all matching ranges

'collMatch is the collection of the matching ranges

    Dim i As Integer
    Dim rngResult As Range

    If collMatch.Count > 0 Then
        i = 1
        Set rngResult = collMatch.Item(1)
        Do While i < collMatch.Count
            Set rngResult = Union(rngResult, collMatch.Item(i + 1))
            i = i + 1
        Loop
    End If

End Function

You're not returning anything in your function. If you add a statement `Set DualFindMod = rngResult `: Is this what you want? See https://stackoverflow.com/a/2781710/7599798 — FunThomas, Jul 01 '19 at 06:50
Thank youuuuuuuu. I knew that the problem was that the resulting range in the function doesn't link to the function as its result. Didn't know how to fix it. — Andy Fazulus, Jul 01 '19 at 07:13

score 0 · Answer 1 · answered Jul 01 '19 at 06:54

When calling another macro within a macro if you set the variables to be global you can just re-use them subsequent to them being called and they'll carry their value across macro's too.

I did this a few years ago where I'd have the main "master" macro calling several sub-macro's to do various functions (some would be skipped depending on starting conditions, so made sense to me at the time).

Here's a good example of how to set them up: How do I declare a global variable in VBA?

Please, do not use global variables for such purposes! The idea of a function is to encapsulate logic and return a result. — FunThomas, Jul 01 '19 at 07:00

score 0 · Answer 2 · answered Jul 01 '19 at 07:17

0

"You're not returning anything in your function. If you add a statement Set DualFindMod = rngResult : Is this what you want? See stackoverflow.com/a/2781710/7599798"

Answered by FunThomas

answered Jul 01 '19 at 07:17

Andy Fazulus

35
2
9

Van Ng · Accepted Answer · 2019-07-01T15:03:29.213

I think you should add return of value in function, as mentioned. Here the code:

Public Function DualFindMod(lookupValue1 As String, lookupValue2 As String, lookupRange1 As Range, lookupRange2 As Range) As Range
'Returns selection of lookupRanges based on matching lookupValues.

'A. Set parameters for subroutine arguments

'B. Find & match lookupValues to lookupRanges, then select all matching ranges

'collMatch is the collection of the matching ranges

    Dim i As Integer
    Dim rngResult As Range

    If collMatch.Count > 0 Then
        i = 1
        Set rngResult = collMatch.Item(1)
        Do While i < collMatch.Count
            Set rngResult = Union(rngResult, collMatch.Item(i + 1))
            i = i + 1
        Loop
    End If
    DualFindMod = rngResult
End Function

Other option is to use ref parameters:

Public Sub DualFindMod(lookupValue1 As String, lookupValue2 As String, lookupRange1 As Range, lookupRange2 As Range, ByRef rngOutput As Range) 
'Returns selection of lookupRanges based on matching lookupValues.

'A. Set parameters for subroutine arguments

'B. Find & match lookupValues to lookupRanges, then select all matching ranges

'collMatch is the collection of the matching ranges

    Dim i As Integer
    Dim rngResult As Range

    If collMatch.Count > 0 Then
        i = 1
        Set rngResult = collMatch.Item(1)
        Do While i < collMatch.Count
            Set rngResult = Union(rngResult, collMatch.Item(i + 1))
            i = i + 1
        Loop
    End If
    Set rngOutput = rngResult 
End Sub

And then call it like this:

Dim i As Integer
For i = 1 To GPN.Count

    lookupValue1 = GPN.Rows(i).Value
    lookupValue2 = Email.Rows(i).Value

Dim rngResult As Range

DualFindMod lookupValue1, lookupValue2, lookupRange1, lookupRange2, rngResult 

rngResult.Offset(0, 2).Value = Union(GPN.Rows(i), Email.Rows(i)).Value

Next i

How do I call a VBA Function into a Sub Procedure and declare it as a variable?

3 Answers3