Multiple UDF #VALUE! Error

[szymagic]

Hello,

I have two UDF's defined in my workbook. One in Module 1 and the other in Module 2.

The first UDF is used solely on Sheet1, and the second UDF is used solely on Sheet2.

When I calculate the Sheet1 and navigate to Sheet2, every UDF on the Sheet2 has a #VALUE ! error. And when I calculate Sheet2 and then navigate back to Sheet1, every UDF on the Sheet1 has a #VALUE ! error. For some reason I can't have both of my UDFs calculated at the same time across two sheets.

I added the ActiveSheet.Calculate command under the Worksheet Activate window for each worksheet, so I wouldn't have to manually calculate the each sheet.

The problem comes when I print my workbook, whatever sheet was last active has the UDFs correctly calculated, and the other sheet has #VALUE ! errors.

My print macro selects the print area on each sheet that corresponds to how many pages are active in each sheet. It then uses Array() to print all of those print areas in one job.

Sheets(Array("Sheet1", "Sheet2")).Select
ActiveWindow.SelectedSheets.Printout

Does anyone know why this happens and if there is a fix??

Thank you for your assistance!

 

[RoryA]

We'd need to see the UDF code.

 

[szymagic]

Actually, I think I figured it out.

My Code is super long and ugly, so I didn't really want to post it (sorry)

But I ran a little test, I think it is because I use Application.WorksheetFunction.Vlookup within the UDF.

I will probably just rewrite my code to not use vlookup. But if there is a way around it that'd be cool too!

Thanks!

 

[szymagic]

I lied.

I believe it is because I use Application.Volatile for each UDF. Anybody know of a way around this!? Sorry for jumping the gun

 

[szymagic]

Update. I think I finally understand my problem, but have yet to come across a solution that works for me.

The problem is that the UDF thinks that the it should pull its' values from the active sheet rather than the sheet it is placed in. I tried using Application.Caller.Parent in front of any Range I call, and that works when I step through it, but it still returns a #VALUE error within the worksheet.

I've consulted these pages, but I haven't found anything that actually works.

https://www.ozgrid.com/forum/forum/...ral/71361-udf-value-error-on-non-active-sheet


https://www.mrexcel.com/forum/excel-questions/1050071-excel-udf-referencing-variable-worksheet.html

 

[szymagic]

Wow, so sorry to the mods, but Application.Caller.Parent infront of all of my Ranges works for what I am trying to accomplish.

For some reason it doesn't work if you try to call a UDF calculated value to a different worksheet. Sorry for all of this mess...

 

[DanteAmor]

Try

Sheets(Array("Sheet1", "Sheet2")).Select
ActiveWindow.SelectedSheets.Printout
sheets("Sheet1").select

I'm sorry, I think it does not solve the problem