0

I have searched the internet and Stack Overflow, but couldn't find an answer to my problem.

I defined a UDF in Excel, it's saved in a module in an .xlam Add-In. The UDF works perfectly fine on my computer. Now I'm trying to use the workbook which uses the UDF on another computer (in a different language) with the same .xlam Add-In active. However Excel suddenly references the entire path to the add-in file as an "absolute path" instead of only showing the function name itself. The result is a #NAME? error because Excel doesn't find the UDF.

How I have written the VBA Code

How it should look

How it looks on another computer

Has anyone had this issue and found a solution for it? I could try to find a workaround without the UDF function but I'd prefer that it works this way. Another time the situation might be more complex ...

JohanDC
  • 13
  • 2
  • 2
    Please add the code to the post as text so that we can test it and edit it. – Toddleson Jan 13 '22 at 18:07
  • 1
    Sounds like a duplicate of [This question](https://stackoverflow.com/questions/24925424/how-to-keep-reference-to-add-in-udf-when-workbook-moved-to-different-folder-than). The answer is quite convoluted unfortunately. – Christofer Weber Jan 13 '22 at 18:28
  • Thank you @ChristoferWeber, that is useful information! – JohanDC Jan 14 '22 at 14:33

1 Answers1

0

The problem with your UDF is the location of the .xlam file. You can find information on dealing with that by following the link in the comments above provided by Christofer Weber.

Having said that, I think it may be more beneficial for you to use the built-in formula that excel has for this which is EOMONTH()

EOMONTH() can be used to return both the last day of a month and the first day of a month (by finding the last day of the previous month and adding 1)

You can find more information on EOMONTH() here https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628

Glenn G
  • 667
  • 10
  • 24
  • Thank you very much for the suggestion! I didn't know about EOMONTH which does indeed yield the result I'm looking for. It doesn't solve the Add-in issue though, for which the answer of Christofer Weber did point me in the right direction for other UDF functions that don't have a built-in Excel version (that I know of :)) – JohanDC Jan 14 '22 at 14:31