Module name added to calls to personal add-in module

[JenniferMurphy]

I have a personal add-in module that I have used for years. It is called zzz add-ins.xlam and is stored, along with a couple of other add-in modules, here:

C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns

It contains dozens of UDFs that I have written over the years and that are used in hundreds of workbooks.

I have a workbook, Magazine log.xlam, that I use to keep track of magazines that I give to the grandkids on their birthdays. It has a code module (Module1) that contains a couple of UDFs that are specific to that workbook. It also has calls to a couple of UDFs in the add-in module. I have used that workbook for years.

Today, I was doing some work in the magazine workbook to add a couple of magazines. That sheet has calls to two UDFs in my add-in module, NextBirthday and MyFmtAge. I happened to notice that all of the calls to NextBirthday in my add-in module have been changed to include the add-in module name. For example, instead of

=nextbirthday(C7)

it is now

='zzz Add-Ins.xlam'!nextbirthday(C7)

Interestingly, none of the calls to MyFmtAge, which is in the same add-in module, have the module name added.

If I remove the module name from the calls to NextBirthday, they get a #REF ! error and the UDF is never called.

I don't know how long it has been like that. I just noticed it today, but I am sure that it was not that way when I created the workbook originally.

When I checked the two UDFs, I noticed that MyFmtAge was declared Public and NextBirthday was not.

Function NextBirthday(DoB As Date, Optional vToday As Date) As Date
Public Function MyFmtAge(P_AgeFrom As Variant _
              , Optional P_Units As String = "*" _
              , Optional P_LabelSw As String = "Long" _
              , Optional P_DP As Integer = 1 _
              , Optional P_AgeTo As Variant _
              , Optional P_NegText As Variant _
                ) As Variant

I changed NextBirthday to add "Public".

Public Function NextBirthday(DoB As Date, Optional vToday As Date) As Date

It still fails.

Can someone give me a clue as to what the problem is and how to fix it?

 

[JenniferMurphy]

PS: If I put the exact same expression

=nextbirthday(C7)

in a new workbook, it works perfectly.

 

[RoryA]

I suspect you have something else open that has (or had) a nextBirthday function in it - most likely the workbook in question. Do you have a particular reason for trying to remove the add-in name from the function?

Strictly speaking, 'zzz Add-Ins.xlam' is not a module name, it's the add-in name.

 

[JenniferMurphy]

I suspect you have something else open that has (or had) a nextBirthday function in it - most likely the workbook in question.

You've done it again, Rory. I named the column containing the next birthdays of the kids "NextBirthday". Excel promptly cleaned up the calls to the UDF by adding the add-in name.

Do you have a particular reason for trying to remove the add-in name from the function?

Mainly I wanted to know why it was there in case it was caused by some error, which it was -- an error on my part. I don't like things that I don't understand. Sadly, that includes a lot of things. (sigh)

Strictly speaking, 'zzz Add-Ins.xlam' is not a module name, it's the add-in name.

Yep, I was just being lazy/careless.

Thanks for helping me find the error. It would have bugged me all day like a pebble in my shoe.