PDA

View Full Version : Problems Initiating a User Defined Function UDF - Using Application.caller.row



kewiopex
12-14-2016, 03:07 PM
Hello Specialists
I posted a previous question and received an answer that worked based on the attachment but I could not get it to work when it was downloaded. It uses an application.caller row setup to place the function as part of the worksheet. The question has been declared Solved.

Of course, I believe that the issue is with me:
1. am I initiating the UDF macro correctly ( I am going to the vba editor and pressing the run
2. I have a cell in the spreadsheet selected to ensure that it is an active sheet
3. the spreadsheet is the only spreadsheet that is open.

The link to the previous question is below for
"VBA Coding for Averageifs that needs to dynamically adjust over 8 quarters"

http://www.vbaexpress.com/forum/showthread.php?57692-VBA-Coding-for-Averageifs-that-needs-to-dynamically-adjust-over-8-quarters&highlight=

Any suggestions are so very much welcome.

p45cal
12-14-2016, 03:52 PM
You aren't supposed to run the macro, because it's a function in a cell (cells B4 and B5 in Paul's sample file).
All you need to do is allow the sheet to calculate.

kewiopex
12-14-2016, 04:41 PM
Ah..the light comes on! Now I understand...and thank you.
I have a further related question if I may...

1. Can the macro be adapted and used in another workbook/worksheet? If so, how would you initiate the macro to get the formula properly in place? I have another averageifs spreadsheet that will need a similar treatment.

I thank you for being here for we beginners.

p45cal
12-14-2016, 04:59 PM
It will already work in any worksheet within the workbook, but to use it in any workbook you could move it to a standard module in personal.xlsb, but then you'd have to enter the formula as:
=PERSONAL.XLSB!Average8Quarters()

Otherwise, have the function in a standard code module in any workbook that's going to use it.

There is no 'initiating' to do.

Bear in mind that if used on another sheet, that sheet should have similar rows 2 and 3, otherwise it will fail/give the wrong answer.

kewiopex
12-14-2016, 05:30 PM
Terrific, p45cal. I am definitely going to try it in another workbook. And I think I have enough now to attempt to do it.
Thank you for that.