PDA

View Full Version : [SOLVED] NAME? error with predefined function which previously was working



andrew nz
07-19-2017, 03:30 PM
Hi, I am very very rusty with using VBA in excel though I used to use it a lot. I am using Excel 2013 on Windows 8.1.

I added a user defined function to my spreadsheet called GetURL as module 4.

Public Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function

I saved my spreadsheet as a xlsm file.

I call it in the spreadsheet GetURL(N120).

Yesterday it worked fine. Today I copied the worksheet to a second sheet in the same file. Suddenly I got NAME errors for GetURL. I also got Name errors when I recalculated the function in the original sheet (same file). I can still see GetURL in module 4.

Any help. It looks to me like somehow the file has become corrupted as I opened an earlier version and everything is still working fine, including the added sheets!!! But it worries me that suddenly the sheet is not working as perhaps this will happen to the one which is currently working.

Any help appreciated

Andrew

andrew nz
07-19-2017, 04:01 PM
It seems that it is a problem with the settings in Trust Centre. I have ticked Trust Access to the VBA project module and ticked disable all macros with notification. When I open the file it issues a warning and if I then allow macros it seems to work. Are these settings safe?

Thanks for any help

Andrew

SamT
07-19-2017, 04:37 PM
Trust Access to VBA Project is as safe as your VBA code. That is what the VBA Project is.

If you trust your VBA Code, ie Macros, don't use Disable with Notification.

Those two settings are most useful to VBA Programmers who work on other people's code. Just my opinion, YMMV.

andrew nz
07-19-2017, 05:10 PM
Trust Access to VBA Project is as safe as your VBA code. That is what the VBA Project is.

If you trust your VBA Code, ie Macros, don't use Disable with Notification.

Those two settings are most useful to VBA Programmers who work on other people's code. Just my opinion, YMMV.

Thanks SamT. Sorry to ask a very basic question but do changes I make like these in the Trust Center setting affect only that workbook or all workbooks? I guess if it affects all it is more of a security concern.

Thanks again

Andrew

SamT
07-20-2017, 11:18 AM
I thiMk it affects all