Having an Issue Using WorksheetFunction in a UDF

[cerfani]

Hi I have what I thought was a simple task but I keep getting unexpected results... I googled around but could not find an answer so hopefully this question is not redundant...

I am creating a UDF which is going to wrap some regular excel functions. This UDF will reference ranges in one of the sheets and this UDF is saved in a module that is part of the same workbook.

I am doing this and it only returns 0... note... this is not the actual final code but I wrote this to test... and the test failed

Function TestFunc()
    TestFunc = WorksheetFunction.Index(Sheet1.Range("A2:A10"), 1)
End Function

so on Sheet1, in ranges A2 through A10... there are random values... so i assumed the value in A2 would be returned but I only get 0...

I know my problem must be basic but I am more of a beginner with VBA (not a beginner to programming or excel)

Thanks to whoever is reading

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Here's four possible causes of that....

1. Sheet1 in that statement will refer to the sheet's CodeName. The CodeName doesn't necessarily match the Sheet's Name (that is displayed on the Sheet's tab). If you are trying to reference the Sheet by the Name on the tab use...

...Sheets("Sheet1").Range("A2:A10")...

2. If you don't qualify a Sheet reference with a Workbook reference, then Excel assumes you mean the ActiveWorkbook. A problem can arise when a recalculation occurs and the ActiveWorkbook has a Sheet1 but it isn't the Workbook you want the function to be reading. It's best to fully qualify references. In this example the "ThisWorkbook" keyword refers to the Workbook that holds the UDF code- regardless of whether that Workbook is active.

Function TestFunc()
    TestFunc = WorksheetFunction.Index(ThisWorkbook.Sheet1.Range("A2:A10"), 1)
End Function

3. If you have Circular References, your UDF formulas won't recalculate and may show a 0 value.

4. If Calculation is set to Manual, that UDF won't recalculate.

 

[pjmorris]

Morning Cerfani and welcome to the forum.

I suspect the reason the UDF wasn't returning a value is because EXCEL didn't realise that it needed to recalculate it - even when you press F9 or change a value elsewhere. In part I believe this is because the function takes no parameters. This leaves you two solutions, the first is to structure the UDF to take a range argument such as the range from which you want the data returned. The second is to declare the function as Volatile:

Function TestFunc()
    Application.volatile (True)
    TestFunc = WorksheetFunction.Index(ThisWorkbook.Sheets("Sheet1").Range("A2:A10"), 1)
End Function

Initially I had similar results to yourself and this change then allowed the cell with TestFunc to update whenever I pressed F9 to recalculate. Note you have to make sure the function is called at least once in order for the VBA Code to be read and the function to be recognised as Volatile.

Hope this helps.

 

[pgc01]

Hi
Welcome to the board

Remark:
Bad idea to have a udf that does not recalculate automatically, you have to make it volatile (inefficient) or you must force its recalculation (you may forget to do it)

Maybe if you explain what you really need we can help more?

 

[cerfani]

Had to step away yesterday but will try to resolve this now and let you guys know... thanks!

 

[cerfani]

Hi
Welcome to the board

Remark:
Bad idea to have a udf that does not recalculate automatically, you have to make it volatile (inefficient) or you must force its recalculation (you may forget to do it)

Maybe if you explain what you really need we can help more?

Thanks! I can explain more...

I have a workbook that will be used by myself and a few coworkers to look up rates from. I work in logistics and basically these rates are costs associated with different services in different warehouse locations. Each rate has multiple lookup criteria so it would be tedious to use excel's autofilters and manually lookup the rate or even to write a formula to do it each time. So I would like to simply wrap the lookup formula. I was thinking then the user just has to open the workbook and call my UDF in their workbook... paste values... then close my workbook.

My company's IT department is busy doing god knows what so I literally have to build my own database and functions. Currently referring to contracts manually... as in pieces of paper O_O

I don't know SQL so I am trying to do this in Excel. Perhaps I am pushing the boundaries of Excel?

 

[cerfani]

Hi and Welcome to MrExcel,

Here's four possible causes of that....

1. Sheet1 in that statement will refer to the sheet's CodeName. The CodeName doesn't necessarily match the Sheet's Name (that is displayed on the Sheet's tab). If you are trying to reference the Sheet by the Name on the tab use...

(removed code)

2. If you don't qualify a Sheet reference with a Workbook reference, then Excel assumes you mean the ActiveWorkbook. A problem can arise when a recalculation occurs and the ActiveWorkbook has a Sheet1 but it isn't the Workbook you want the function to be reading. It's best to fully qualify references. In this example the "ThisWorkbook" keyword refers to the Workbook that holds the UDF code- regardless of whether that Workbook is active.

(removed code)

3. If you have Circular References, your UDF formulas won't recalculate and may show a 0 value.

4. If Calculation is set to Manual, that UDF won't recalculate.

Hi and thank you

Right, I was referring to the codename of the sheet and not the name of the sheet (what you see on the sheets tab). In VBA when I look at my VBAProject... the sheet with my rates and where I will be doing my lookup is shown as...

I also tried the 2nd way you said by calling

ThisWorkbook.Sheets("Rates").Range("A2:A10")

Also there shouldn't be a circular reference issue as I am dealing with constants and a simple lookup as a test first. I am also making sure to recalculate (retyping formula and F9). Thanks for the suggestions but still working on it.

 

[cerfani]

Morning Cerfani and welcome to the forum.

I suspect the reason the UDF wasn't returning a value is because EXCEL didn't realise that it needed to recalculate it - even when you press F9 or change a value elsewhere. In part I believe this is because the function takes no parameters. This leaves you two solutions, the first is to structure the UDF to take a range argument such as the range from which you want the data returned. The second is to declare the function as Volatile:

Function TestFunc()
    Application.volatile (True)
    TestFunc = WorksheetFunction.Index(ThisWorkbook.Sheets("Sheet1").Range("A2:A10"), 1)
End Function

Initially I had similar results to yourself and this change then allowed the cell with TestFunc to update whenever I pressed F9 to recalculate. Note you have to make sure the function is called at least once in order for the VBA Code to be read and the function to be recognised as Volatile.

Hope this helps.

The first option I would not want to do because my goal is eliminate the user having to show Excel what to do, I want them to just tell Excel what to do

In regards to your 2nd option... I added the line of code... before the index function just like you have done.

Application.Volatile (True)

The Volatile property on my excel is capitalized though, is this a different property?? I'm not quite sure if VBA is case sensitive... it appears to be but not sure. Anyways, I called this function by entering it into a cell and I tried calling it multiple times (typed into cell... deleted... typed again). What exactly did you mean by...

"Note you have to make sure the function is called at least once in order for the VBA Code to be read and the function to be recognised as Volatile."

I assume the user simply using the formula would be calling it once, I am lost on your meaning here... thanks!

 

[cerfani]

In regards to my last post... I had a typo in my code that the vba compiler did not alert me about. I guess I am too use to fancy intellisense in langs like c#.

@pjmorris ... it appears adding volatile has fixed the issue and the index function is performing as expected. If I have another issue I will post. Thank you to all here for the prompt replies and the welcome. Glad this community as a resource exists :D

 

[cerfani]

I ended up resolving this with Evaluate function instead of WorksheetFunctions...

For anyone curious this is my final code with the multiple criteria lookup...

Function GetRate(Warehouse As String, WHServiceName As String, Tier As String)
    GetRate = ThisWorkbook.Sheets("Rates").Evaluate("=INDEX(D2:D180,MATCH(1,(A2:A180=""" & Warehouse & """)*(B2:B180=""" & WHServiceName & """)*(C2:C180=""" & Tier & """),0))")
End Function

Thanks again to all who read.