Passing name of array to function based on variable

[TheRedCardinal]

Hi all,

My code does this:

1. Populates 4 different arrays with data from locations around the workbook
2. Depending on the values in ranges, picks one of these arrays to work on
3. Uses a function where the name of the array is passed to that function through its parameters.

The problem I'm having is that the function is only being passed a String variant, and not the array itself.

Some example code is here:

Establishing my arrays:

Sales = WBk2.Sheets("Sales").UsedRange
RemSales = WBk2.Sheets("Sales-Removed").UsedRange
Purch = WBk2.Sheets("Purchases").UsedRange
RemPurch = WBk2.Sheets("Purchases-Removed").UsedRange
TabHeads = WS2.ListObjects(1).HeaderRowRange

Allocating the name of the necessary variants to a variable:

If WS3.Range("RepDirection") = "A" Then

    Tab1 = "Purch"
    Tab2 = "RemPurch"
    
Else

    Tab1 = "Sales"
    Tab2 = "RemSales"
    
End If

Calling my function:

IntMatch = WhereInArray(Table(Counter, IntVouch), SIIVouch, Tab1)

And the function itself:

Function WhereInArray(Search As Variant, Vector As Variant, SearchArray As Variant) As Long

For MyCount = LBound(SearchArray, 1) To UBound(SearchArray, 1)

    If SearchArray(MyCount, Vector) = Search Then
        WhereInArray = MyCount
        Exit Function
    End If
    
Next MyCount

MyCount = 0

End Function

In the Locals window when the function is activated, "SearchArray" is just called "Purch" (or "Sales") and has the type Variant/String and so I get a Type Mismatch error.

Whereas if I specify the name of the Array without using the variable, I get the full array transferred into the function.

I could get around this by using IF statements but out of curiosity, is there a way to do this using the variable name instead?

 

[Jeffrey Mahoney]

So several things. You did provide your function a string when you put in quotes.

I don't see how you Dimensioned the Arrays. This would result in a range:
Dim Purch as Range
Purch = WBk2.Sheets("Purchases").UsedRange

This would result in an array

Sub GetArray()
  Dim Purch As Variant
  Dim X As Long
  
  Purch = Application.Transpose(Range("B76:B81").Value)
  For X = 1 To UBound(Purch)
    Debug.Print Purch(X)
  Next X
End Sub

You would then need to pass Purch to the function

 

[Jeffrey Mahoney]

How did you dimension Tab1? Is it a variant also?

 

[TheRedCardinal]

So several things. You did provide your function a string when you put in quotes.

I don't see how you Dimensioned the Arrays. This would result in a range:
Dim Purch as Range
Purch = WBk2.Sheets("Purchases").UsedRange

This would result in an array

Sub GetArray()
  Dim Purch As Variant
  Dim X As Long
 
  Purch = Application.Transpose(Range("B76:B81").Value)
  For X = 1 To UBound(Purch)
    Debug.Print Purch(X)
  Next X
End Sub

You would then need to pass Purch to the function

Hi

I didn't explicitly dimension the arrays, they did that themselves when I allocated the used ranges on each sheet to them.

The arrays are properly set up in Locals both when I am in my main Sub, and also if I replace the variable with the name of the array.

The problem is I think its passing the variable itself into the the function, instead of the value of the variable.

 

[Jeffrey Mahoney]

Tab1 = "Purch"
should be
Tab1 = Purch
if Tab1 is a variant

 

[TheRedCardinal]

Tab1 = "Purch"
should be
Tab1 = Purch
if Tab1 is a variant

Ah ok so you think I have to make tab1 the array itself, rather than just a reference to the name of the array?

I'll give it a go!