[sims] Excel Report Template - Referencing a UDF in Range.Formula Property

[ilsley_SIMS]

Hi,

 

I'm trying to reference a UDF I've built in the Range.Formula property (within VBA), but it keeps giving me the #NAME? error when I run the report from SIMS. This is how I'm trying to reference it in the VBA module:

 

Function AOB_Cat(Category As Range, ref, joinStr As String) As String

If Not IsNumeric(ref) Then ref = Chr(34) & ref & Chr(34)

With Category

AOB_Cat = Join(Filter(.Parent.Evaluate("if(" & .Offset(, 1).Address & _

"=" & ref & "," & .Address & ",char(2))"), Chr(2), 0), joinStr)

End With

End Function

 

 

Sub CreateOnSubjects()

 

Dim lastRow As Long

 

Sheets("Sheet1").Range("AG1").value = "Subjects On Target"

 

With Sheets("Sheet1")

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("AG2:AG" & lastRow).Formula = "=AOB_Cat($E2:$Z2,""O"","", "")"

End With

 

Sheets("Sheet1").Range("A1").Select

 

End Sub

 

 

If I use the UDF directly from the cell, it works fine, but I need to be able to put the formula directly into the cell via VBA.

 

Any ideas? Thanks!

[ilsley_SIMS]

Any takers?

[bobsmith]

would this get better responses in the excel area rather than MIS?

[ilsley_SIMS]

I can't seem to see an Excel section... or do you mean the Office section?

[bobsmith]

that'll be the place!

[ilsley_SIMS]

Ignore this thread... I've just figured out what was causing the problem!

[vikpaw]

Ignore this thread... I've just figured out what was causing the problem!

Please share so others can fix their own issues next time. I was going to say your code might be running too soon, before the data is properly in the cell.

[ilsley_SIMS]

Please share so others can fix their own issues next time. I was going to say your code might be running too soon, before the data is properly in the cell.

 

 

I fixed it by placing the UDF in a separate spreadsheet on our central staff shared area. In the SIMS Excel Report Template, I then added this spreadsheet as a reference in the reference library (VBA Editor > Tools > References > Browse). I then tweaked the code in the relevant sub routine like so:

 

Sub CreateOnSubjects()

 

Dim lastRow As Long

 

Sheets("Sheet1").Range("AG1").value = "Subjects On Target"

 

With Sheets("Sheet1")

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("AG2:AG" & lastRow).Formula = "='CustomFunctions.xlsm'!AOB_Cat($E2:$Z2,""O"","", "")"

End With

 

Sheets("Sheet1").Range("A1").Select

 

End Sub

 

 

 

So every time I run this report via SIMS now, the report template calls on the CustomFunctions.xlsm spreadsheet which contains the UDF that I wrote and works perfectly!

 

Hope that all makes sense.