UDF errors when workbook is opened from a network server

Hi,
I have a client who has been using an old .xls workbook that contains several UDF functions. They've used this successfully on a remote server without problems since I believe 2003 (no, don't ask why they haven't moved to a later Excel version since then!)

They've recently changed to a new "Net Docs" server (I'm not familiar with that name and unfortunately the user is not a teccy and at the moment can't give me any more information, nor indeed whether as part of the server change they've also moved to 365 or some other environment)

I'm told that when they now open the workbook from the Net Docs server they are getting #NAME? errors in the cells that contain UDFs.
I too see those errors when I open the file from my local hard drive, but as soon as I click the "Enable All Macros", warning during the opening process they disappear.

Can anyone offer any thoughts on what might be causing the UDFs failing to run when opening the workbook from a new server.

Usual TIA.

Well, when you move to a new server the network engineer tends to review user policies, let him/her check if the macro restriction is set to avoid executing macros.
That probably explains the issue and I have encountered that quite often

Thanks,

I'll get the client's teccies to check that out.

Let me know how it goes, am curious to hear if my theory could be correct

Originally Posted by Keebellah

Let me know how it goes, am curious to hear if my theory could be correct

Thanks for the continued interest.

On further discussion it turns out that the client was essentially expecting the workbook to open without the need to accept the 'Enable macros warning'query. The question of the #NAME errors was of seondary importance.

Unfortunately after I'd talked the client through the whole subect of trusted workbooks and tusted locations and explained why such checks are important they decided on balance not to get IT involved and have simply accepted they will have to choose to enable macros when a workbook is first opened. As it happens the UDFs were unnecessary since there are perfectly straightforward standard excel functions that achieve the same aim - which I changed so that they don't see a worrying #NAME on opening.

So I'm no nearer understanding why the client claims the workbook opened without prompts on the old server but not on the new. Unless of course the old server did have the files in a trusted location of which the client was unaware.

Thanks Lewis,

Interesting suggestion. Maybe changing straight from 2003 to Office 365 is encountering the same problem you found in as far as the UDF #NAME results are concerned. When the client has had time to catch his breath and absorb the explanations I gave and if I think I can talk him through the process you suggest (or get his permission for me to do it remotely) then I may well attempt that.

I'll let you know if anything transpires and thanks again for the interest.

I ran into issues too and when I compared the Trust settings the default was to not allow any macros and not show notifications, the way it was built was the other way around than what was 'normal' in the older versions.

What also happened with Excel's transition from 2003 to 2007 and newer versions is that the default settings for macro's was modified.
Also the way Excel was built changed, it was no longer one file but a compressed zip file which opens on-the-fly. You can check this by either opening it with WinZip or similar aor just add the .zip extension after xlsm or xlst and you'll see that it's all xml code in the background.

The security level was 'stepped-up' and does not (always) allow the user to change these unless the rights policy is set (on network shares)

I understand that 'older' version users don't want the hassle but if you want to drive a car with extended (new) options, you'll have to go along with it.

Originally Posted by Keebellah

I ran into issues too and when I compared the Trust settings the default was to not allow any macros and not show notifications, the way it was built was the other way around than what was 'normal' in the older versions.
.......

Thanks Hans,

I'll discuss this with the client and see if they're willing to try this out. If they do I'll keep you posted.