Moving code from one add-in module to another

[JenniferMurphy]

I want to move several UDFs from one module in one of my add-ins to another module in the same add-in. I created the new module and moved the code, but now all of the calls to those UDFs get a Name error. From what I have read, this is an addressing problem in that the calls have an invisible path to the original add-in module. But I couldn't find a way to change that path or even see it.

I tried deactivating the add-in and reactivating it both with and without closing and reopening the workbook, but I still get a Name error.

How can I change the path?

How can I see the path to confirm that it is correct?

Thanks

 

[bobsan42]

Well you have to compile and debug to see where this comes from - there can be a lot of reasons.
1. you have moved the code from a code module to an object (form or sheet) code module
2. you are calling your functions with reference to the code module name
3. You have moved private functions
or ended up with duplicate declarations ...
or forgot to move private variables ...
many possible reasons
first compile

 

[JenniferMurphy]

Well you have to compile and debug to see where this comes from - there can be a lot of reasons.

I opened the VBA editor, selected the add-in module (MyExcelAddins), clicked Debug and selected Compile MyExcelAddins. It found a few type mismatches in other (old) code modules, but nothing in this one. Correcting those errors did not correct the problem.

1. you have moved the code from a code module to an object (form or sheet) code module

I moved the code from one code module in the add-in to a different (new) code module in that same add-in. I wrote a little test function in that new code module and called it from the same sheet that calls the one that is getting the Name error. It worked.

2. you are calling your functions with reference to the code module name

What does this mean? The UDF is called WtdRtg. The cell that used to work and is now getting a Name error contains this expression:

=wtdrtg(Ratings,RtgsBest,RtgsWrst,RtgTypes,RtgReq,RtgWts,"errmsg=on")

3. You have moved private functions

There are only 3 UDFs. The main one, WtdRtg, is declared public. The other two, which are only called by WtdRtg, were declared as Private. I changed them both to Public. It did not get rid of the Name error. I also tried deactivating the entire add-in and reactivating it. No joy.

or ended up with duplicate declarations ...

Compile didn't detect any, but when I went to check myself, I discovered that the add-in is no longer visible.

What happened? How do I get it back?

or forgot to move private variables ...

As far as I know, all variables were declared as Dim and were inside procedures.

many possible reasons
first compile

I did, but now the add-in is no longer visible. How do I get it back?

 

[JenniferMurphy]

I located the add-in file. It's here:

C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns\MyAdd-Ins.xlam

In Excel, when I go to File | Options | Add-Ins | Manage: Excel Add-ins | Go, the add-in is listed and its checkbox is checked.

But when I open the editor (Alt+F11), it is not listed in the Project Explorer (Ctrl+r).

How do I fix this?

 

[bobsan42]

Have you created a new file? With a new name?
what happens if you call the function from the immediate window of VBE?
Where is the add-in not visible? VBE Project explorer?

Check if add-in is installed:
- Developer tab -> Excel add-ins (alternative: Excel options - Add-ins - Excel addins - Active/Inactive/Manage...)
- VBE -> check if the Add-in is in the Project explorer window after starting excel

Also check trust center settings if all else fails

 

[RoryA]

Does it appear in the disabled items list?

 

[JenniferMurphy]

Have you created a new file? With a new name?

If you mean a new add-in file, no. What I did was right-click on the add-in name in Project Explorer and select Add Module.

what happens if you call the function from the immediate window of VBE?

Sub or Function not defined

Where is the add-in not visible? VBE Project explorer?

Yes, in the Project Explorer.

Check if add-in is installed:
- Developer tab -> Excel add-ins (alternative: Excel options - Add-ins - Excel addins - Active/Inactive/Manage...)

I'm running Office 2007. There nothing is about add-ins under the developer tab.

There is an add-in tab, but I don't see anything like what you suggest.

If I go to File | Options | Add-ins, I see a list of active and inactive add-ins. One of my personal add-ins is in the Active list and that one is also in Project Explorer and those functions work in Excel and in the Immediate window. My primary add-in, the one that I seem to have lost, is not listed in either the Active or Inactive lists nor in the Project Manager.

- VBE -> check if the Add-in is in the Project explorer window after starting excel

I think I have answered this a couple of times. It is NOT listed in Project Manager.

Also check trust center settings if all else fails

Since one add-in is working, I can't see how the Trust Center settings are relevant, but here they are:

• Under Add-ins, none of the boxes are checked.
• Under Macro settings, Enable all macros is selected and Trust access to the VBA project object model is checked.

 

[JenniferMurphy]

Does it appear in the disabled items list?

Nope.

Let me try to summarize the situation all in one place.

I have (or had) two personal add-in modules. The filenames are: My Add-Ins.xlam and Temp Add-Ins.xlam. Both of these files exist in this folder:

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

Both of these add-ins have been around for at least 10 years. I haven't done anything in the second one (Temp Add-ins) for a long time and actually forgot that it existed, but I use the first (My Add-ins) all the time. I add and edit UDFs to do little chores as I need to. My Add-ins has about a dozen code modules. One, MyUtil, contains most of my utility UDFs. The others contins UDFs for specific projects or for testing

I don't recall now how I created these add-ins originally or how activated them.

I have several other add-ins that I downloaded or installed from third parties or that were installed by other software that I had.

About a week ago or so, I started work on a complicated Excel application. I wrote a UDF (WtdRtg) which I put it in the MyUtil module of My Add-ins. WtdRtg processes data in 6 ranges. It compares the data in the first range against the corresponding data in the other 5 ranges. Here's the function declaration.

Public Function WtdRtg(pRatings As Range, _
                       pRtgsBest As Range, _
                       pRtgsWrst As Range, _
                       pRtgTypes As Range, _
                       pRtgReq As Range, _
                       pRtgWts As Range, _
                       ParamArray PArgs() _
                     ) As Variant

WtdRtg got more and more complicated. One If block was two screen long. I started putting code in subfunctions. These also went into My Add-ins right below WtdRtg. I initially coded the subfunctions as Private. The main one was an error handler. It was designed to generate standard MsgBox error messages complete with the address of the cell from which the UDF was called and the address of the cell that caused the error. In the past, I have had problems with UDFs that generate MsgBox error messages. If the UDF is called many times in a sheet, I would have to clear each error message before I could get back to work. So in this error handler, I added a switch that would enable or disable the MsgBox messages and the option to execute a breakpoint after each MsgBox message so I could change the switch and disable the rest of the messages.

Here's that declaration.

Private Sub WtdRtgErr(CallerName As String, CallerAddr As String, ErrMsg As String, _
                      Optional ErrCellAddr As String)

I also did a little experimenting with global variables.

About the same time that I started moving blocks of code to subfunctions, I started getting odd behavior whenever an error occurred. I would set a breakpoint (F9) at the point where the error was occurring. When I resumed execution, the UDF (WtdRtg) would get called several more times. I added code to capture the calling cell address. It showed that the UDF was being called from cells containing data, not calls to the UDF.

As part of the debugging process, I decided to move all of the code for this project to a new code module in My add-ins. I right-clicked on My Add-Ins in Project Explorer and selected Insert | Module. Then I opened the Properties dialog and changed the name of the module to WtdRtg.

Is there a problem with a code module having the same name as a UDF in that module?

I then moved the WtdRtg code and all subfunctions from the MyUtil module to the WtdRtg module, both in My Add-ins.

I did a little testing and everything seemed to be working. It was about this time that I needed to stop work for the day, so I closed the VBE and Excel. The next day. when I started work again, the WtdRtg module was missing and all of the WtgRtd code was back in MyUtil. I then realized that I hadn't remembered to save the Add-in and the morons at Microsoft do not even warn us if we close an add-in without saving changes. Grrrrr.....

So I redid the work to move the code. I am not sure now if this is when the entire add-in went missing or sometime later.

So here's the current situation.

• In the add-in folder shown above, both add-in files are present. I tried opening My Add-ins with Notepad, but it was unreadable. I was afraid to open it with Excel.
• If the Project Explorer in the VBA editor, Temp Add-ins is visible, My Add-ins is not.
• In the Excel Add-In dialog (File | Options | Add-Ins), Temp Add-Ins is listed in the Active Add-Ins, My Add-ins is not listed in either the Active or Inactive Add-Ins.
• If I then click on Manage Excel Add-Ins | Go, Temp Add-Ins is listed and checked, My Add-Ins is not listed.
• If I click on Manage Disabled Items... | Go, no items are listed.

Is there any other information that I need to provide?

Since I have the add-in file, can someone please just tell me how to go about reinstalling it?

Thanks

PS: I really need to get this working again as soon as possible. Most of my workbooks depend on UDFs in my add-in, so I am kinda disabled as it is now. Thanks.

 

[bobsan42]

It seems that for some reason then your add-in is not discovered by Excel and is not installed, so normally your UDFs will not be found.
You can try to add it manually through Excel options - Add-ins - Manage: Excel add-ins - Go..., Browse ... and Locate the file. Make sure its box is checked after you add it to the list.

One more thing just occurred to me (depending on your OS) - have you by any chance transferred the file by e-mail to this computer?
Just in case - locate the add-in file in File browser, Right click , Properties. Check if there is an UNBLOCK button on the General tab. If there is - click on it.

I think that's all I can think of.

 

[RoryA]

Functions/subroutines should not have the same name as a module (or anything else). It just makes your life difficult.