Make Array available to a function

[pitaszek]

Hi Guys,

Do you know any way?

The function is to Vlookup the array and return a name of the boss based on employees name.

I tried to declare the Array as a public but this does not work.

Maybe there is a way to pass a constant parameter to a function in a form of variant array?

I tried something like, maybe you can use that as a basis:

'People() is an 2d array
Function FindTheBoss(SalesEN As Variant, People() As Variant) As String


    For i = 1 To UBound(People)
    
                If People(i, 2) = Person Then
                   FindTheBoss = People(i, 1)
                End If
            
    Next i


End Function

Regards,
Witek

 

[Jeffrey Mahoney]

This worked for me

Public People As Variant
Const Boss = "Mary"


Sub FindPerson()


  Dim p As String
  Dim i As Long
  People = Range("B4:C8")
  
  p = FindTheBoss()
  Debug.Print p
  
End Sub


Function FindTheBoss()
  Dim i As Long
  
  For i = 1 To UBound(People)
    If People(i, 1) = Boss Then
      FindTheBoss = People(i, 2)
      Exit Function
    End If
  Next i
    
End Function