Excel VBA Introduction Part 33 - Creating Add Ins
ฝัง
- เผยแพร่เมื่อ 3 ธ.ค. 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/s...
By Andrew Gould
www.wiseowl.co.uk - Creating an Excel VBA Add-In is a great way to make your custom functions available to other Excel workbooks and VBA projects. This video teaches you how to create an add-in from scratch, including how to write VBA functions, where to save your add-in, how to enable an add-in to make it available to other workbooks and how to reference an add-in from a VBA project.
You can learn about writing Excel VBA functions in this video • Excel VBA Introduction...
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
![สาวเมืองเพชร - ธีเดช ทองอภิชาติ [Official MV]](http://i.ytimg.com/vi/kEt7F9PvO3I/mqdefault.jpg)
Best coding teacher, EVER!
This message is for Andrew Gould, I don’t have any special word for you, that how much I thank you for your Excel VBA tutorials on TH-cam, I learned a lot of watching those videos, and now I am doing job in Excel VBA with full of confidence, thank you so much for that, I have one kind request to you that, it would be great help if you would post video tutorial, for how can we create Command Bars, Menus, Sub Menus, right click menus or how can we play with menus in Excel through VBA.
Thanks Kashif
Very clear and useful instructions. It's of great help to me as I am learning Excel VBA. Thank you, Andrew.
Thanks Andrew. You are a great instructor.
thank you so much Andrew for this video.
I find this really helpfull to bring up my own functions i used to save in txt files and paste whenever new workbook created. Thnk u very very much!
excellent tutorial
amazing it solved one of my recent issue. thanks
Happy to hear that it helped, thanks for watching!
Thank you for sharing this video and this is what I looking for.
This is excellent. Thank you so much for your work.
Thanks for such a good video on VBA.....
Very nicely done!
This is great, thanks! Currently I'm using the Personal.xlsb file but I might switch to this method since you can store your code file anywhere you want in the filesystem/on network and share with others. Also I get tired of the read only prompt for Personal.xlsb when I'm working with multiple spreadsheets. Don't understand why Microsoft didn't disable it from showing for that file.
Awesome Video!
Thank you very much!
Wonderful
Thank you!
Thank you andrew for this
So is this better than using personal work book?
So then i could create macros and save them this way
I think it depends on what you're trying to do! Add-Ins probably make it easier to share code with other people. You can also create multiple Add-Ins for different projects or groups of code while you only ever have one Personal Macro Workbook.
Thank you for the training video! So if I understand correctly, saving the LongDateFormat UDF in this example as an Excel add-in and as a module (.bas file) is very similar, the difference is that add-in is easier for the function to be used in different workbooks, while for module it must be imported each time? Thank you!
Hi! Yes, that's a good summary, thanks for the comment!
@@WiseOwlTutorials Thanks again for your excellent training video :)
Hi Andrew, your channel is one of my favourites. Thanks for all the videos. I hope one day I can go through all of them! could you confirm that : to make sure the project still works on other computers, I will need to send the add in file along with the project, right? it seems to be a pain and "scary" for a user who is not programmer but that's the only way?
Book which I can read & learn more & more codes
quite useful!
Hi Andrew, can you show me how to display userforms defined add-ins
Great tutorial!
With Add Ins we can make libraries, can't we?
can we make add-ins from workboook module???
Hey Andrew...Request you to create a video on creating Ribbons without using custom UI editor..please
Hi Amit! You'll need some way to modify the XML of the ribbon. I guess you could just use Notepad to do this in principle although that would be tricky! Have you tried this tool andypope.info/vba/ribboneditor_2010.htm
2 questions. 1. Can the add-in need to contain Sub procedures? 2. Does if require the Public (ie Public Function or Public Sub)? Thank you.
Hi! The add-in does not need to contain Sub procedures (but you can include them if you want to). If you want to use the functions in the worksheet they need to be public. If you don't declare a function as Private, it will be Public by default.
I hope it helps!
Hi Andrew. Can I ask please - does it matter if the add-in is made on a 32 bit machine/version of office as to whether it will run on a 64 bit version of excel and if so would it just be a case of recreating it again on a 64 bit machine/version? Thank you
Thanks Andrew that's really helpful. I'll have a look. Much appreciated.
Thanks just a question do you know how to crack a add-ins password?
What is the logic of the suffix
Hi Andrew, thanks! :-) Is there way how to create macro which will install and create custom ribbon with newly added macros?
+WiseOwlTutorials Thanks :-) I did it :-) I have automated ribbon + addin in one :-) pitty that for Ribbons you need XML editor.
can we disable Vba Project Properties as well as Module..i want to make them unselect is it possible?
i Need Good Protection for my File
Hi Andrew!
This is a great video again. Thanks a lot for it!
I have a question though: What did I wrong? I created an add-in, and installed it as You showed, and there are more buttons already on the ribbon of the functions from it. Everything okay. But! If I open the XLAM from the VB Editor, and write some new Sub routine (even if it is only simply just a range.select), it seems not being able to save it. I click on the small floppy icon, it shows, that my xlam will be saved, but if I close and re-open excel, my new routine is disappeared. :( My original 2 functions are still there, and the 3 old Sub routines too, but my new sub has vanished. How? Why?
So in general:
What are the right steps if I want to change/update an already existing XLAM with new buttons, etc?
Thanks a lot!
Zsolt
Hi Zsolt! I haven't encountered this problem myself. In fact, I've just tested that I can successfully perform the steps you've described and it's working normally for me.
It seems that many other people have experienced the same issue that you describe. Here are a few links from a quick Google
answers.microsoft.com/en-us/msoffice/forum/all/unable-to-save-edits-to-xlam/fe7e663f-2d22-4b3a-9aae-adf4a939ca04
www.mrexcel.com/board/threads/cant-save-my-xlam-add-in.461321/
stackoverflow.com/questions/889840/how-do-i-save-code-changes-to-an-excel-vba-add-in
Andrew, is there anyway i can ease the installation of Ad-ins for the users. Such as creating an installation package where it will install the add-ins and tab and a group with the ad-ins? Thanks again for your help
Thanks a lot Andrew. I appreciate your help.
Andrew, Is there a way to query excel table in a sheet?
Andy, I used ADO Excel driver and I was able to filter data using sql syntax that way. Thanks for all your videos and help. Andy, don't see anywhere on your website for donating for your help.
Hello wiseowl sir.. I want to mastry in vba... How do I do it.. Please suggest any good 📖
Hi Andrew, many thanks for all of your tutorials! I'm watching them all the time!
I have created a very simple add in.. And I uploaded it like you did in the video but every time I try to open or work in excel it makes excel freeze until I kill Excel from task manager.. Can you tell me if there is something Wrong with the code?
thank you!
Sub ISL_Format()
Selection.NumberFormat = "$ #,##0"
With Selection
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.ReadingOrder = xlContext
End With
End Sub
Many thanks for your replay Andrew. it seems to be a local problem in some computers..
Can CreateObject be used for referencing Add-In?
Hello Andrew,
Thank you for your reply.
I mustn't have used the word "referencing" in my question :)
Heyy.. Its a very nice video. I have one doubt. So, I have a excel add-in file and a different excel file. When i drag and drop the excel file in the add-in file, I get the results that I want. But, I am trying to automate this. That is, everytime, I open the excel file, the add-in should do its work. (I should not have to drag and drop or press anything for that matter!) How to do this? Please help!! TIA
I understand what you are saying. But the problem is that the add-in file is not made by me. And it opens using Run command. When I give this command, the excel add-in will open. Now, the next file (which has all formulas) that i will open, will automatically open in this instance. (given no other excel files are opened.. otherwise we have to drag and drop the file)
What i need is to automate this.
So, is there is way/any code, so that i don't have to open 2 files or say one file automatically opens in a particular instance..
hi,
this is regarding an issue related to add-in,i have a template created in Excel and the coding is done in VBA(Macros),in some users where add-in option appears, the users are unable to submit the template(there is no specific error). Can add-in/smart view impact the functioning of macros.What is the ideal setting to run Macros. Any help would be appreciated.
+WiseOwlTutorials Thanks for the reply.To make a clarity, the template is the part of a whole process, we have a template which has macros written at the backend, there is an option to submit the template and as the template is submitted, a processing id is generated,which is used for the further operation.
Now my users complain that as they see the option of add-in the ribbon,this is causing a hinderance in running of the macros (i. e. they are unable to submit the template& there is no error message).Is this possible? from this video of yours my understanding is(please correct if i am wrong) add-ins are used to make the availability of a formula to various other sessions of excel.
Please share your views if add-ins can cause problem in running of macros.
Thanks in advance :) :)
+WiseOwlTutorials Thank you, for the details.Your efforts are appreciated. I'll try finding other possibilities.
.