How to Create a Custom Excel Add-in (Step-by-Step Guide)
Вставка
- Опубліковано 12 лип 2024
- 👉 Explore All My Excel Solutions: pythonandvba.com/solutions
𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Supercharge your Excel skills and productivity by learning how to create a custom Excel add-in using VBA (Visual Basic for Applications) in this easy-to-follow, step-by-step tutorial. Perfect for both beginners and advanced users, this guide will walk you through the entire process of creating your very own Excel add-in.
VBA allows you to quickly and efficiently build custom add-ins that can expand Excel's capabilities, automate repetitive tasks, and integrate with external tools and services. It's an excellent way to get started with creating and distributing add-ins due to its user-friendliness and ease of implementation. While there are other methods, such as COM, VSTO, and Office.JS, our focus will be on VBA in this tutorial.
By the end of this video, you'll have a custom ribbon that allows you to run any macro with ease, taking your Excel abilities to new heights!
🌍 𝗟𝗜𝗡𝗞𝗦:
▶ GitHub Repo: github.com/Sven-Bo/excel-add-...
▶ Office RibbonX Editor: github.com/fernandreu/office-...
▶ MyToolBelt Add-in: pythonandvba.com/mytoolbelt
▶ Office imageMSO icon library: bert-toolkit.com/imagemso-lis...
▶ Blog Post (XML/VBA Starter Code): bettersolutions.com/vba/ribbo...
▶ Microsoft Custom UI Docs: learn.microsoft.com/en-us/ope...
⭐ 𝗧𝗜𝗠𝗘𝗦𝗧𝗔𝗠𝗣𝗦:
0:00 - Introduction
1:06 - Creating an XLSM File
1:30 - Customizing the Excel Ribbon
5:12 - Integrating ChatGPT
7:45 - Adding More Buttons to the Add-in
10:39 - Exploring Other Custom UI Elements
11:30 - Distributing Your Add-in
13:00 - Outro
𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.com/mytoolbelt
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.com/grafly
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.com/cuteplots
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.com/emojify
📑 Excel Templates: pythonandvba.com/go/excel-tem...
🎓 My Courses: pythonandvba.com/go/courses
📚 Books, Tools, and More: pythonandvba.com/resources
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: / sven-bosau
📸 Instagram: / codingisfun_official
💻 GitHub: github.com/Sven-Bo
💬 Discord: pythonandvba.com/discord
📬 Contact: pythonandvba.com/contact
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here:
▶ pythonandvba.com/coffee-donation
*Hope this video helps you to create your own Excel add-in!* 🚀
Here are all the relevant links:
👉GitHub Repo: github.com/Sven-Bo/excel-add-in-tutorial-template
👉Office RibbonX Editor: github.com/fernandreu/office-ribbonx-editor/releases/tag/v1.9.0
👉MyToolBelt Add-in: pythonandvba.com/mytoolbelt
👉Office imageMSO icon library: bert-toolkit.com/imagemso-list.html
👉Blog Post (XML/VBA Starter Code): bettersolutions.com/vba/ribbon/document-level-customui-editor.htm
👉Microsoft Custom UI Docs: learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/d842006e-3187-4f66-a17d-0819a3cc94b5
Great that you are back 🚀
I should think of creating my own Excel Add-In 🤓
Thanks, Christian!
Go for it and happy coding! 🚀🤓
Great walk through video! I've spent the afternoon installing Visual Studio and trying to figure out their IDE and how to build an Add In through the project template but this is 1000x simpler and works for sharing a few work tools with coworkers... Thanks!
Glad to hear that you liked the video. Good luck with your add-in. I am sure your coworkers will love it! :) Cheers, Sven ✌️
Welcome back, very nice video!
Thank you, Ricardo! I appreciate all your support! 👍
I've been needing to create my own ribbon add in for a while and this was super easy to follow and straight forward. Thanks!
Happy to hear that you liked the video. Good luck with your add-in and happy excelling! Cheers, Sven ✌️
On a side note. I don’t suppose you’re planning another video with how to make the custom tick marks on your add in?
@@ajayhuestis4981 I am afraid you are right. I will most likely focus more on Python-related content in the future. I might only sprinkle in some VBA videos here and there.
Very very good video!
Thank you! :)
Thank you, that was good
Glad you liked it. Thanks for watching and taking the time to leave a comment! 👍
THANK YOU!
My pleasure! Appreciate you taking the time to watch and leave a comment. Cheers, Sven ✌️
Hey Sven, great tutorial. I've been thinking for a while now that I'd like to learn how to create an Excel add-in with a custom ribbon tab and icons, etc, to give myself easy access to macros I've developed which could be useful across different workbooks, etc. Today I followed your step-by-step guide and it all worked perfectly: I now have a personalised ribbon tab with my first custom icon to launch a little routine I wrote, plus I've taken the liberty of adding your two IFERROR tools, which no doubt will also come in handy.
If you ever have the time and the inclination to do a follow-up video to explain more advanced techniques using some of the different elements you mention briefly starting at 10:43, that would be great!... But in the meantime, thanks for this excellent introductory lesson which was enough to get me up and running.
Hi Patrick,
Thank you so much for the positive feedback! I'm thrilled to hear you were able to create your own add-in. I've noted your suggestion regarding a follow-up video, but I can't make any promises at this moment.
In the meantime, you might want to explore this wonderful playlist from a fellow UA-camr. It includes more in-depth tutorials on how to add advanced ribbon elements: ua-cam.com/video/ypI2VXYaXQI/v-deo.html&ab_channel=VBAA2Z
I hope it proves helpful! Happy Coding!
Its been a while hope you can make even more exciting videos!
Yes, I took a small break from UA-cam in March as I was on vacation. But rest assured, there will be more videos coming in the future! :)
Great Helpfull
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!
Thanks for giving link these will help to to study more advance way
An absolute pleasure, very happy to hear that you found it useful! Cheers, Sven ✌️
That is awesome 🎉❤
First comment! :) Thank you! Happy Developing! 🤓
@@CodingIsFun I always wait for your great videos 📷
@@haninawaya9967 Thanks for your support! 🙏
Wooow, it's so cool, can you another video for word Add-in?
Glad you liked it and thanks for your video suggestion :)
As always, 10/10 video 👏
Though, I need to ask: would you consider making an advanced version like, hiding all other tabs and locking for the user the possibility to show them again or to see the vba codes from behind?
Thank you for watching and for your feedback. I appreciate your suggestions for an advanced version of the tutorial. However, at this point, I'm not planning to create one. That being said, I never say never, but I can't make any promises 😅. Thanks again for your support.
Good morning. Great videos. Very knowledgeable person and professional. I was wondering if you could post a video on pivot tables using python. Thank you!😊w
Thanks for the kind words and your video suggestion! :)
Would absolutely love a tutorial on how you made the Table of Contents WITH clickable screenshots!! Genius!! I am really enjoying your Add-in "My Tool Belt" Pro version is perfect.
Happy to hear you're enjoying the MyToolBelt PRO Version! Your video request is definitely noted, but creating a detailed walkthrough of the TOC feature might be a bit out of reach at the moment, since I’m currently diving deeper into Python topics. Seems to resonate better with the audience for some reason. But hey, never say never, right? Anyways, super happy to hear you liked the video and are enjoying the MyToolBelt add-in. Cheers, Sven ✌
@@CodingIsFun maybe a comment pointing towards some resources on the topic of adding linked screenshot macros??? Worth asking 😊
New sub: Thank you for your video this is exactly what I been looking for. I followed your instructions several times and I keep bringing back a error code stating my api key is invalid but I generate 3 different ones and still same error
Thanks for watching. Please have a look at the following possible solution: github.com/Sven-Bo/Integrate-ChatGPT-in-Excel-using-VBA#common-issues-and-solutions
Very nice
Thanks!
Welcome back
Thanks! ♥
Hi Sven :) did you have nice time in Sri Lanka ?
And this video is really cool !
Hey Sujung! Thanks for watching. So happy you liked the video! Oh, and Sri Lanka was a blast, had an awesome time there! 🎉
This is really good. Thanks! Can I use this to add into Powerpooint and Outlook?
You can also crete PowerPoint and Outlook add-ins. The process is similar, but not the same. For you reference: stackoverflow.com/a/37548779
I hope it helps! Happy Coding! Cheers, Sven ✌️
very nice
Thanks! 👍
Nice! Where are you creating these graphics/animations, like in 0:14? You download it, or making urself?
Thanks for watching. That is a lottie animation: lottiefiles.com/
Top Video! Thank you! I wished, somebody would do similar videos, e.g. in that quality, for OpenSource Software like Libreoffice Writer or Calc. Would you mind, stepping into these ? ;)
Thank you for taking the time to watch the video and for your kind words! I'm glad to hear that you found it helpful. I appreciate your suggestion, but unfortunately, I do not have much experience using those programs. Throughout my career as a data analyst, I have primarily used Excel. 😅
Very detailed video
a small doubt: I'm trying to connect to the azure open ai service(instead of open ai) what changes should have to make in the code
Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!
Nice Video, very helpful.
Do you know how to make a VBA addin that opens a side pane with UI elements? Like the wikipedia addin say?
Thanks! You could do that with VSTO or Office.js Excel add-ins :)
you can use visual studio and create VSTO addin (in VB or C#) add Pane object in your project. or create an office addin using visual studio or visual studio code.
Great video. Watching from India. I m from forest department. Really very helpful video for save jungle and wildlife. But I can't find "My add in" in your website. Please guide
Thanks for watching. The links, including the Excel workbook from the video, are in the description box.
Thank you sir for lighting speed reply ❤
Hi Sven, Great Video thanks for sharing. it would be great if Excel Lambdas could be turned to Add-ins any idea if this is possible? many thanks
Hi there! Thanks for watching. Accessing lambda functions directly in VBA isn't possible, but there's a workaround you might find useful. You can actually create your own functions within VBA. These functions become available to anyone with your add-in installed. For example, I recently developed an "AI Formula" that connects to the OpenAI API. Check out this video for a demo: ua-cam.com/video/m69UeA27jFo/v-deo.html
Also, if you're interested in creating your own user-defined functions, here's a guide that could help: trumpexcel.com/user-defined-function-vba/
Hope this helps! Happy Coding! Cheers, Sven ✌️
Sri Lanka 🇱🇰 ❤
😍♥
I have a couple VBA functions I put together in an XLSM file and want to use it as an add-in for other sheets I create. How do I keep the XLSM from opening up when I open a file that has the add-in attached to it?
Thanks for watching. Sorry, but I am not sure what you mean
I understand that your macros is in the excel file (XLSM) embedded. You have to create an add-in.
How you call your initial method?
Hi Sven, how to write python code in vba macro?. Not to use runpython and call py file using xlwings, want to write python code in vba just like we write in spyder, jupyter notebook or other IDEs. Is thr any dll or settings available to do so.
VBA is its own scripting language associated with Microsoft Office products and does not support writing Python code within it. You can call external Python scripts via certain methods (like RunPython in xlwings), but there's no way to write Python code directly in VBA as if it were a Python IDE like Spyder or Jupyter Notebook.
@@CodingIsFunsure got it and thank u for ur reply
Can we make a Installer software like a executable file which after installing it should automatically add my custom Add-in file in their system in excel. Please guide me how to do this task.
Thanks for watching. Have a look at the following article:
jkp-ads.com/articles/excel-addin-installer.asp
I hope it helps! Happy Coding! Cheers, Sven ✌️
How can I fix the error "End of Central Directory record could not be found" when I open the xlsm in the office ribbonX editor?
It was due to security program. Thanks!
Glad you solved it! Cheers, Sven ✌️
How do you install RibbonX on a Mac?
Thanks for watching. I think, it only works on Windows - sorry!
Sory, but when I add my add-in into Excel (like in last video part), my add-in doesn't show on top panel. How I can fix this problem?
Thanks for watching. Hard to tell from a distance
How do we sell the add-in we create?
Thanks for watching. That is a broad question and hard to answer. You could list it on Gumroad and sell it there, just as one example.
Can I do this on a Mac? I figured not but wondering if anybody has tried?
Thanks for watching. This solution does only work on Windows. Cheers, Sven ✌️
Made an add in thanks but one major drawback of this is u don't have undo feature for the task u perform through this VBA add in and didn't find anything useful on internet too
Thanks for watching. Perhaps the following video/approach might help you: ua-cam.com/video/KqWLfCtiTKc/v-deo.htmlsi=3MEA1KoreyfcnYnz
Happy Coding! Cheers, Sven ✌️
How about updating the ChatGPT for version 4, as the API have been updated. And you code will be depreaceated by 2024, a very nice bit of code I must add. Even Chatgpt itself was unable to offer this function! :-)
Thanks for watching. The code is already updated and I even created a video about it. Here you are ;)
ua-cam.com/video/3Z96yLlDim0/v-deo.htmlsi=l9k-1ftoQLedjpH4
@@CodingIsFun Deserves a new subscriber, great job.
Welcome aboard! 🎉
Will I be able to create an add in for this ?
ua-cam.com/video/VWq5d7j6ppI/v-deo.html
Try it out! :)
@@CodingIsFun One thing I need to mention I got an error "Wrong number of arguments or invalid property assignment", after alot of searching I found that I needed to put "control As IRibbonControl" into the macro
Sub MacroName(control As IRibbonControl)
@@danesvarneelamagam8941 Yes, that is exactly what I have shown at the 05:00 min mark 😉
@@CodingIsFun Ohh, apologies
@@CodingIsFun Ohh, apologies
that strong german accent is quite awkward to listen to.
Well, every office superhero needs their unique trait! Mine just happens to be a strong German accent. 🇩🇪💪
Your English is perfect! Better than my German which is NON existent. Keep up the amazing work and the passing of knowledge. The pace of your instruction is spot on and the level of information you provide is great. Thank you!!! @@CodingIsFun
@@JoeMcMullin Thanks for the kind words! I really appreciate it! 🙏👍
i followed you step by step but when i get excut the file excel macro after changing on office !ribbonX Editor it sais the next '' Sorry, we couldn't find C....................xlsm. it is possible it was moved, renamed or deleted? ''
what's the probleme
PM: in the video, it's 07:24 exactly
Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.
@@CodingIsFun can you help me? maybe by using the app AnyDesk ?? please