Error on VBA array function (macro menu or "Argument not optional"

Question

When I try to run the function below the macro menu pops up, so I created a Sub to call the function and now I get the error "Argument not optional".

The function is attempting to do a match which I then hope to use to do an array index match. https://bettersolutions.com/vba/arrays/searching.htm

Sub Repossession_Match()

Dim Inflation_Bucket, Inflation_Bucket_Label As Variant



Call TwoDimensional

End Sub


Public Function TwoDimensional(ByVal Inflation_Bucket_Label As Variant, _
                               ByVal Inflation_Bucket As Variant) _
                               As Boolean

Dim Inflation_Bucket, Inflation_Bucket_Label As Variant
Inflation_Bucket = Range("Costs.Inflation_Bucket")
Inflation_Bucket_Label = Range("Inflation.Inflation_Bucket_Label")

   Dim lrow As Long
   Dim lcolumn As Long

   For lrow = LBound(Inflation_Bucket_Label, 1) To UBound(Inflation_Bucket_Label, 1)
      For lcolumn = LBound(Inflation_Bucket_Label, 2) To UBound(Inflation_Bucket_Label, 2)
         If (Inflation_Bucket_Label(lrow, lcolumn) = Inflation_Bucket) Then
            TwoDimensional = True
            Exit Function
         End If
      Next lcolumn
   Next lrow
End Function

TwoDimensional has two arguments, neither of which are you passing. You probably need to read up on this topic as you are defining both these in your code so you can remove them. — SJR
– SJR, Commented Aug 1, 2019 at 16:27

Tim Williams · Accepted Answer · 2019-08-01 16:49:12Z

You want something more like this:

Sub Repossession_Match()

    Dim Inflation_Bucket, Inflation_Bucket_Label As Variant

    Inflation_Bucket = Range("Costs.Inflation_Bucket")
    Inflation_Bucket_Label = Range("Inflation.Inflation_Bucket_Label")

    MsgBox TwoDimensional(Inflation_Bucket_Label, Inflation_Bucket)

End Sub


Public Function TwoDimensional(ByVal Inflation_Bucket_Label As Variant, _
                               ByVal Inflation_Bucket As Variant) _
                               As Boolean
   Dim lrow As Long
   Dim lcolumn As Long

   For lrow = LBound(Inflation_Bucket_Label, 1) To UBound(Inflation_Bucket_Label, 1)
      For lcolumn = LBound(Inflation_Bucket_Label, 2) To UBound(Inflation_Bucket_Label, 2)
         If (Inflation_Bucket_Label(lrow, lcolumn) = Inflation_Bucket) Then
            TwoDimensional = True
            Exit Function
         End If
      Next lcolumn
   Next lrow
End Function

Collectives™ on Stack Overflow

Error on VBA array function (macro menu or "Argument not optional"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related