CODEWITHSUNDEEP
exceluser-defined-functionsvba
Jarom
Jarom

Reputation: 1077

VBA User Defined Function only Working on one Worksheet in Workbook

I have the below code that creates an array from a table in excel and then searches that array based on multiple criteria from the inputs.

Function output_string(id As Long, dt As Date, descr As String)

'set variables
Dim myarray As Variant
Dim ws As Worksheet
Dim col_count As Integer
Dim row_count As Long
Dim source_range As Range

'set worksheet
Set ws = Worksheets("Data Store")

dt = DateValue(app_dt)

'set up column and row counts for populated range
col_count = ws.Range("A2").End(xlToRight).Column
row_count = ws.Range("A2").End(xlDown).Row

'set range for populated cell space
Set source_range = ws.Range(Cells(2, 1), Cells(row_count, col_count))

'create the dimensions of the array that will store the table
ReDim myarray(1 To source_range.Rows.Count, 1 To source_range.Columns.Count)

'load data from excel table into array
myarray = source_range.Value


'get row with matching criteria
For i = LBound(myarray) To UBound(myarray)
    If myarray(i, 1) = id And dt >= myarray(i, 2) And dt <= myarray(i, 4) _
       And descr = myarray(i, 5) Then
       output_string = myarray(i, 3)
       Exit For
    End If
Next i

End Function

The function gives the correct output. The problem is that the function only works on the Data Store worksheet. All the other worksheets return the #VALUE! error when I put in the exact same input. The arguments to the function can be cell references from other worksheets without erroring out.

Also, every once and a while (seemingly at random) the working function on the Data Source tab will convert from a correct value to #VALUE! as well.

I have the function in a regular module in the same workbook that I'm using. The module also has a sub program in it.

I'm really stumped on this one. Has anyone seen this before/do you know a solution?

Upvotes: 0

Views: 1675

Answers (1)

JNevill
JNevill

Reputation: 50273

This line, as you mentioned in the comment's here, is causing the issue:

'set range for populated cell space
Set source_range = ws.Range(Cells(2, 1), Cells(row_count, col_count))

The problem is that Cells(2,1) and Cells(row_count, col_count) are range objects that have not been properly qualified with their worksheet. I know it seems counterintuitive since you are already saying ws.range here, but these range objects must also be qualified with ws:

'set range for populated cell space
 Set source_range = ws.Range(ws.Cells(2, 1), ws.Cells(row_count, col_count))

Without that qualification it will use the Application.Activesheet as the default, which is probably the sheet that is acting as the Application.Caller.Parent in this UDF context that you are in. So it's trying to make a range in ws made up of beginning and ending cells in your activesheet which is nonsense.

Upvotes: 1

Related Questions