Excel: .xls File UDF Does Not Show Value on Newer Computers (Win 10 / Excel 2016)

[kjv1611]

I cannot seem to find any evidence that this should be a version specific issue, but at first glance, it appears that way. Let me first point out I was able to correct the issue for now on an adhoc basis, but I'd like to prevent it from happening in the future.

Here are the different details that I am aware of:
[ul][li]xls file created from a "template" - I use "template" loosely because thus far we have not used actual template files here - something I hope to change one day.[/li]
[li]When the file was opened on a machine running Windows 7 and Excel 2010, the UDF showed values correctly in cells where used.[/li]
[li]When the file was opened on 3 machines each running Windows 10 and Excel 2016, the UDF showed #VALUE.[/li]
[li]I corrected the immediate issue by selecting one cell involved in the calculations for each row of data and saved the file (using Windows 10 and Excel 2016).[/li]
[li]The calculations in the UDF previously were correct in some instances, and I corrected the UDF sometime in the last 6 months.[/li]
[li]The code in the UDF produces no errors, just does not always show values for some reason? (It does at each opening since I last corrected it as mentioned above).[/li]
[li]I found the mention of the Application.Volatile line that can be added to UDFs, but I am not certain that by itself would correct the issue.[/li][/ul]
Any ideas as to what would cause such an issue, and what the best methods to correct it would be?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Hi,

I can't duplicate this on in Windows 10, Office 2013, workbook with UDF saved as .xls.

Can you get more specific?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Sorry. That's about as much as I know so far. It doesn't do it on all workbooks, and they basically all have at least a handful of UDFs that run with various calculations. It makes no sense to me so far. I haven't read or heard of this before. I'll keep digging. For the meantime, the lady who uses this all the time uses Windows 7 anyway. The problem arose when a couple managers running Windows 10 couldn't open the file. So it'll come up again if it's a true issue and not a one-off oddity. I've got another oddity also seemingly related to Excel 2010 vs Excel 2016 and/or Win7 vs Win10. If I have a bright moment and sort it out or think of some other details I can share to help, I'll be sure to share. Thanks for taking a look.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Update that I didn't mention earlier:

For the time being, I wrote a short macro that basically selects each cell with the problematic formula. Once that is done it APPEARS to work correctly in all instances - so far as I know. But I'll hopefully find time from other items to do further testing and find the source problem.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[mintjulep]

Security / Trust settings issue?

Opened from local drive vs network vs email attachment make any difference in behavior?

 

[kjv1611]

Well, this has not happened prior to Windows 10 / Excel 2016. It has run fine at least up through Windows 7 / Excel 2010. I doubt it's an issue in that regard. Also, the issue corrects itself when cells related to a given formula are selected, which also seems to point away from it being security/trust issue. And it's somewhat hit and miss.

Strange thing - I just got a call about another Excel workbook this morning that had the same issue for 2 or 3 out of about 15 cells in one column. I fixed it by unprotecting the worksheet, selecting the cells with formulas showing errors then reprotecting the worksheet.

Definitely something I intend to keep investigating to get to the bottom of it.

As far as local vs network drive: that isn't optional for this process. It requires use over the network, as it goes through a process of data entry / multiple review steps. There are no email attachments involved, as it's a network driven process.

Thanks for thinking about it, though. Any brainstorming could end up proving helpful.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57