Solved: VBA to Excel Add-in

[tesko]

Hello,

I've just written my first VBA code, and I want to share this code with my collegues in the form of Excel add-in. xla format. (and they will use the same code in different files) However when I save the file as .xla, and open this .xla file in another .xls file (drag and drop save x0la to another xls file), it doesn't work. I believe that this is due to the fact that my xla added as another vba project, instead of a module within vba project of my xls file.

How can I convert my code to an add-in that will add itself into the open file's vba project hiearachy as a new module and work there within that hieararch, rather than making a new filename.xla VBAProject?

Sorry if I couldn't explain myself, this is my first code and I'm quite unfamiliar with the jargon.

Best,

[Bob Phillips]

It should be an xla, and you should load it via Tools>Addins.

[tesko]

Sorry but this is not what I mean, I don't have a problem in adding the add-in.
I wrote a code and saved it in Add-in.xla.
I opened my file workfile and then added the add-in.xla.
When I look at Visual Basic Windows, on the left upper pane I see two projects. One for xla file and one other for my workfile.
In this way my code doesn't work.
If I move the module1 from xla project to workfile's project section it works.

How can I make my add-in add a module within the project of opened file, rather than within it's own project folder.

[CatDaddy]

could you modify your code so it doesnt have to be in the same workbook as it works in? removing ThisWorkbook and making it ActiveWorkbook might help if you have this?

[Paul_Hossler]

How can I make my add-in add a module within the project of opened file, rather than within it's own project folder.

I don't think add-ins work the the I think you think they work. The add-inb's code is not added to the user's opened workbook, but it is used by that workbook.

It also depends on how the user causes your add-in's code to run: CommandBar, Ribbon, or directly from VBA.

And of course that depends on the version of Excel. Since you're saying XLA, I assume it 2003?

Simple example: an add-in with a sub to make every cell in the worksheet Selection Upper Case.

How would the user make that happen (button, etc.) with A1:Z26 selected in a workbook?

The code is in the add-in, but the User 'talks' to the add-in and the add-in 'talks' to the active workbook. OK, it's not a great example, but post a representivie example and we'll see

Paul

[tesko]

could you modify your code so it doesnt have to be in the same workbook as it works in? removing ThisWorkbook and making it ActiveWorkbook might help if you have this?

Thanks a lot ! This one solved the problem. I'm sincerely grateful.

[CatDaddy]

No problem! mark solved at the top >thread tools

[tesko]

I don't think add-ins work the the I think you think they work. The add-inb's code is not added to the user's opened workbook, but it is used by that workbook.

It also depends on how the user causes your add-in's code to run: CommandBar, Ribbon, or directly from VBA.

And of course that depends on the version of Excel. Since you're saying XLA, I assume it 2003?

Simple example: an add-in with a sub to make every cell in the worksheet Selection Upper Case.

How would the user make that happen (button, etc.) with A1:Z26 selected in a workbook?

The code is in the add-in, but the User 'talks' to the add-in and the add-in 'talks' to the active workbook. OK, it's not a great example, but post a representivie example and we'll see

Paul

Paul thanks a lot for the reply. Let me answer:

I said 2003 because I created the add in at work which uses 2003 but currently on my office 2010 home laptop. So at the moment 2010 and I added the add in to (I guess the English of that is) Quick Ribbon next to undo and redo buttons.

I totally understood (I hope "totally") what you mean by your example.

When I say "thisworkbook" it's actually referring to the xla file per se, which doesn't have any data other than the code itself. Hence the error I faced.

And when I say "Active.Workbook" user triggers the add-in and the add-in refers to the active workbook and implements the functions on the workbook the user sees (active).

Anyways, thanks a lot for your reply as well.

Regards,