VBA debug compile error

[ouvay]

Hello, I am working on this code that matches multiple criteria and then does a bunch of copy pasting (simply put)

I am looping through my array, sending through the criteria to the UDF, which loops through the second data region to find the match and paste the values I want pasted

simple enough in theory... but when I try and send the criteria through for referencing ... I get an error...

Here is my basic code (haven't finished obviously)

Sub connect()

    Dim hdfcwb As Workbook: Set hdfcwb = Workbooks.Item("Payment Control - HDFC Leg.xlsm")
    Dim hdfc As Worksheet: Set hdfc = hdfcwb.Worksheets(1)
    Dim a(2 To hdfc.Rows.Count, 1 To 6) As Integer
    
    For r = 2 To UBound(a, 1)
        match(a(r, 3), a(r, 4), a(r, 6))
        
    Next r
    
End Sub

Function code

Function match(buyer As Long, sponsor As Long, pay As Integer)

End Function

Error

Not sure why the "=" is required?

TIA

 

[StephenCrump]

A VBA function returns some value(s).

Any code calling that function needs to assign or use that value somehow, e.g.

SomeVariableName = match(a(r, 3), a(r, 4), a(r, 6))

BTW, you'll need to declare a in two steps:

Dim a() As Long
    
ReDim a(2 To hdfc.Rows.Count, 1 To 6) As Long

But you should really make a only as big as you need, rather than including 1m+ rows.

I'd probably also change the name of the function to MyMatch, say. Generally it's better to avoid names that already mean something in Excel or VBA. It shouldn't be an issue here with match, but sometimes it will cause problems with your code.

 

[ouvay]

A VBA function returns some value(s).

Any code calling that function needs to assign or use that value somehow, e.g.

SomeVariableName = match(a(r, 3), a(r, 4), a(r, 6))

BTW, you'll need to declare a in two steps:

Dim a() As Long
   
ReDim a(2 To hdfc.Rows.Count, 1 To 6) As Long

But you should really make a only as big as you need, rather than including 1m+ rows.

I'd probably also change the name of the function to MyMatch, say. Generally it's better to avoid names that already mean something in Excel or VBA. It shouldn't be an issue here with match, but sometimes it will cause problems with your code.

Thank you! That is insightful!

sets me off in the general direction I need to be headed in!

 

[StephenCrump]

You're welcome. Thanks for posting back.

 

[RoryA]

FWIW, you don't have to use the value returned from a function but if you don't, there shouldn't be parentheses around the function call (unless you use the Call statement) so it would be either:

match a(r, 3), a(r, 4), a(r, 6)

or:

Call match(a(r, 3), a(r, 4), a(r, 6))

 

[StephenCrump]

FWIW, you don't have to use the value returned from a function .....

Interesting, thanks. Never knew that.

 

[ouvay]

FWIW, you don't have to use the value returned from a function but if you don't, there shouldn't be parentheses around the function call (unless you use the Call statement) so it would be either:

match a(r, 3), a(r, 4), a(r, 6)

or:

Call match(a(r, 3), a(r, 4), a(r, 6))

Thats very useful! Thanks!