Printable View
Does anyone know how to make a .xla file for your vba to use.
Because it lists a few of em and I was just wondering how I would make my own. I want to create a macro that saves and then exits on all excel workbooks so I can use a shortcut key to do it.
Get your code the way you want it as an XLS. Then when you're happy, on the File | Save As... command under Save As Type option, scroll all the way down to the bottom - XLA is the last option.
You can then Install with Tools | Addins...
FWIW - you can define a hotkey even if you just put the macro in your Personal macro WB.
Regards,
This one is Solved
Thank You Greg
Hi, ideally you want your addin to be as small as possible so it wont have any adverse affects for your users. Heres some advice extracted from John Walkenbach's Excel 2002 Power Programming with VBA...
FILE SIZE
Excel workbooks (including add-ins) have always suffered from a serious problem:
bloat. You may have noticed that the size of your files tends to increase over time,
even if you don?t add any new content. This is especially true if you delete a lot of
code and then replace it with other code. Making lots of changes to worksheets also
seems to add to file bloat.
If you want to make your add-in?or any workbook, for that matter?as small as
possible, you?ll need to re-create your workbook. Here?s how:
1. Make a backup of your application, and keep it in a safe place.
2. Activate the VBE, and export all the components for your project that contain
VBA code (modules, code modules, UserForms, and possibly ThisWorkbook,
worksheet, and chart modules). Make a note of the filenames and the location.
3. Create a new workbook.
4. Copy the contents of all the worksheets from your original application to
worksheets in the new workbook. Be especially careful if you used named
ranges in your workbook?they must be re-created.
5. Import the components you exported in Step 2.
6. Compile the code.
7. If applicable, reattach any toolbars that were attached to your original workbook.
8. Save the new workbook.
9. Test the new workbook thoroughly to ensure that nothing was lost in the
process.
There?s an excellent chance that the newly created file will be much smaller than
your original. The size reduction depends on many factors, but I?ve been able to
reduce the size of my XLA files by as much as 55 percent with this process.
Parry,
I read that chapter a while back and your post prompted me to check the CD. But in the folder for Chapter 21, there are only two items, neither of which appear to be a utility that would do all of this for you. Are you aware of Walkenbach (or anyone else) already having done up a utility for this? I've got a hum-dinger of an add-in that I suspect is as bloated as the federal budget and I'd like to put it on a diet, so I may end up writing a utility for this for myself (I don't think it'd be all that hard), but thought I'd ask if you'd seen something on the net already.
Regards,
Thank you all for all the good help. Especially you Parry for typing for a while, you didnt have to do that.
Where would books like that be found or I should say, where did ya'll get yours?
http://j-walk.com/ss/books/index.htm
There are, of course, others, but J-Walk's among the best I've come across.
As to where to get it - since our corp. purchasing (finally) figured out how to use Amazon, I got it there, but I'd say buy whereever you can get your finance manager to sign off on using!
Um - should add, Mr. Excel's offering some new titles. But I'm not familiar with their content.
MrExcel Books: http://www.mrexcel.com/sunshop/index...on=&id=&subid=
Bill Jelen & Tracy Syrstad's new book: VBA & Macros for Microsoft Excel
Which is supposed to be an excellent book, and I'll probably get (next time I have $$).
Hi Greg not that Im aware of unless JW put this in his PUP utility. Personally I dont create that many add-ins so I havent really worried about it but yes you could create some code - an Add-In in itself :-) - that cleans it all up. I would probably do it myself though as I tend to do extra things like delete all sheets except one to get it down as much as poss.Quote:
Originally Posted by Greg T
Hi guys,
You may be interested in Rob Bovey's Code Cleaner utility - its designed to help clear-up exactly the sort of 'bloat' that JW refers to.
http://www.appspro.com/Utilities/CodeCleaner.htm
Richie,
Thanks for that link. I downloaded the utility and am looking forward to giving it a try when I have a moment.
Regards,
Thanks also Richie!