Creating an addin from an Excel macro

---

Comments

Showing last 8 comments of 63 in total (Show All Comments):

 

---

Comment by: DDuck (25-2-2012 15:59:47) deeplink to this comment

Wish you had put all the tutorial pages in one instead of making 11 separate pages. Hope all the clicks are worth it.

---

Comment by: Ludo (19-4-2012 11:44:18) deeplink to this comment

Hi Jan Karel,

'Of course there are many more things to account for when one designs an add-in.' is more than true!

Some things aren't mentioned in this article, example:
1) it's impossible to add worksheets in the Add-In at run time, unless you set the IsAddin property temporary to FALSE, add the worksheet(s) and back to TRUE after the worksheets are added.
2) You can't use the .Select property.
3) Refering to your add-in project or a worksheet(s) within your add-in need to be approached by ThisWorkbook.Sheets("Sheet 1"), and / or use SET
Example

I struggled more than a week to create a chart in a add-in, and found out that i couln't ADD a chart in the add-in at runtime, but that this needed to be already in place (and the needed series too) to get a chart with variable data.
Sometimes, as long as the IsAddin property = FALSE, the code runs smootly, but once you change this property to TRUE, the code crash. Not always clear to me why this behaves like this, but it happens.

Hope this message is usefull.

Regards,
Ludo

---

Comment by: Jan Karel Pieterse (20-4-2012 02:51:45) deeplink to this comment

Hi Ludo,

Thanks for your thoughts.
One slight comment: there is no need to set an object variable to ThisWorkbook, as you can use ThisWorkbook directly. So this line of code:

Can be replaced with:



and the variable shMyWorksheet can be omitted from the sub entirely.

---

Comment by: KineticFlow (28-11-2014 20:56:46) deeplink to this comment

Thanks, this is a very helpful starter

---

Comment by: Marty Helfer (15-4-2016 15:54:00) deeplink to this comment

Good Morning, I have multiple macros(5)that multiple(15-20)users need access to. I've been going into each PC and copying the macros into each users PERSONAL.xls. When business requirements change and a macro needs updating, I need to revisit each user. Will an Add-in allow me to save it on a network where the users can access it? Ultimately, if a change needs to be made, I'd like to make it in one spot and have it immediately available to the users. Is an Add-in what I'm looking for? Thank you very much. Marty

---

Comment by: Jan Karel Pieterse (15-4-2016 16:09:06) deeplink to this comment

Hi Marty,

Yes an add-in would help you in that situation. If you make sure you place the add-in on a fileshare everyone has access to and if you remember to mark the add-in file read-only (right-click, properties in widnows explorer), then you can simply replace the addin file. People would have to restart Excel to get the new version though.

I show a more sophisticated method here:
https://jkp-ads.com/Articles/UpdateAnAddin.asp

---

Comment by: Marty Helfer (15-4-2016 20:57:03) deeplink to this comment

Good Afternoon, I feel like I'm making headway! Just as "proof of concept" I've created a macro in a workbook all by itself. I saved it on the network as an add-in. I went back into Excel. Selected File, Options, Add-ins, <Manage: Excel Add-ins> Go. Select my two add-ins, click OK, restart Excel and I don't see them in "Data - Data Analysis". Am I missing something? Please let me know your thoughts...Marty

---

Comment by: Jan Karel Pieterse (18-4-2016 06:12:22) deeplink to this comment

Hi Marty,

If this is Excel 2013/2016, you may have to repeat the install process of the add-ins. Make sure ALL instances of Excel are closed prior to doing this.

---

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):
 

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.