VBA module to Macro short cut and sharing to multiple users

[Yohanis]

Hi all

I am not sure if it is the proper way, but I changed an excel VBA module to a Macro by just changing the module name to Sub macro() and also able to assign a short cut (Ctrl+A) for it. So if what i did is the proper way, then how do I share this to multiple computers / users where they can access the Macro whenever they open any Excel file (existing or new). My choice to convert it to Macro is for its short cut feature.
Thank you for your time.

[June7]

Would need code in each workbook to call procedure. Maybe https://www.mrexcel.com/board/threads/calling-procedures-in-another-workbook-and-passing-variables.465901/

Possibly with a customized ribbon button.

What does this procedure do?

In Excel, a macro is still a VBA procedure.

[Logit]

It would be best to NOT use the term 'Macro' for the name of your macro. You could abbreviate the term to 'Mcro' or maybe 'Maco' . Using the actual term 'macro' might confuse Excel and cause issues later.
This would be the same thing for terms such as : COPY, PASTE, WRITE, INPUT BOX, etc. Those are all reserved terms Excel uses for objects or processes.

[Aflatoon]

You cannot change a module into a macro. A module is a container for code.

Your best bet is probably to turn the workbook into an add-in and distribute that.

[Yohanis]

Thanks Aflatoon

One reason I want to convert the VBA module to a Macro is the short - cut so user does not have to deal with running the module. I can not also create a command button to run as I wanted these module to be available on any Excel file for all users. So as you said if i turn the workbook into add- ins, can I also assign a short cut to run the module from any computer / user.
thanks again

[Paul_Hossler]

A possibly simpler option would be to create an XLSM with the macro, assign a shortcut key to that macro, save it in XLSTART folder, then hide the workbook

[Yohanis]

Hi all thank you for your reply. At this point i am now more inclined to using an Excel Add - ins. The add - ins also allows me to create a quick access toolbar. Now Unfortunately when i convert it to an add ins and tired to use it on a new worksheet, in some of the result, the worksheet i used to create the add ins keeps coming, rather than the updated version which i want to see.

[Aflatoon]

Firstly, the QAT is for user customisations really - you should be using a custom ribbon. Second, I suspect that is your problem anyway as the QAT will include links with the file path, which may well be why you keep seeing the old version appear.

[Yohanis]

Thank you for your time Aflatoon. But do you have any tip on how to create the Custom ribbon for an Excel Add ins.

Thank you again

[Paul_Hossler]

VBAexpress has forum

http://www.vbaexpress.com/forum/forumdisplay.php?96-Office-2007-Ribbon-UI


These are the MS references

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/aa338202(v=office.12)?redirectedfrom=MSDN

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/aa338199(v=office.12)?redirectedfrom=MSDN

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/aa722523(v=office.12)?redirectedfrom=MSDN


This is the editor

https://github.com/fernandreu/office-ribbonx-editor


BUT ... IMHO I still think that you'd find that a hidden workbook in XLSTART would be easier

[Aflatoon]

I always recommend this as the place to start: https://www.rondebruin.nl/win/section2.htm

I would recommend the add-in approach versus a hidden workbook, especially if you want a customised ribbon setup.

[Paul_Hossler]

I always recommend this as the place to start: https://www.rondebruin.nl/win/section2.htm

I would recommend the add-in approach versus a hidden workbook, especially if you want a customised ribbon setup.


1. Also a good place

2. I was thinking that a hidden workbook would more easily allow a QAT icon and a shortcut key

[Aflatoon]

I don't see how that would be any easier?

Also, in my opinion, the QAT is for the user to customise, not for distributed workbooks. They should use the ribbon - and that won't work if the workbook is hidden, as best I recall. It's also a lot easier to distribute an add-in in my experience, especially if users want the ability to enable/disable it easily.

Anyway, at least the OP now has a myriad of options. :)

[Yohanis]

Thanks both. I will explore both suggestions

Thanks again

[Paul_Hossler]

I don't see how that would be any easier?

Also, in my opinion, the QAT is for the user to customise, not for distributed workbooks. They should use the ribbon - and that won't work if the workbook is hidden, as best I recall. It's also a lot easier to distribute an add-in in my experience, especially if users want the ability to enable/disable it easily.

Anyway, at least the OP now has a myriad of options. :)


1. OP doesn't have to muddle their way through learning CustomUI and Fluent

2. Yes, I (mostly) agree, but IMHO the QAT is not too difficult for a user since all the options, functions, etc. are right there

3. Yes, if I had a workbook with a lot of functions, options, etc. (and I do :) ) I'd make it an addin. This seems like it's only one Sub in the WB

4. Again IMHO, telling users how to install an add-in and telling them to copy the hidden workbook to XLSTART seems a wash.

But...

As you and OP said, there are options to investigate

[Yohanis]

I think it is better i share my Excel file and what it does.

The worksheet "Pdata" is holding the data
All output will be stored on the PowerDetail worksheet.
At first it SUMS the row in Pdata and puts it in PowerDetail Column B (you see it in green)
Now using the green data the GPI table is calculated as High, moderate and low.
The central tendency table is calculated from the Pdata worksheet again and it calculate each columns Mean, Median and Mode (see the column title here it matches with the source worksheet "pdata")
The Occurrences of Responses table is also calculated from Pdata worksheet and counts and also get the percentage of the counts of 4,3,2,1 and 0's (Also note the Row names in here exactly matches with the source worksheet "pdata")
The issue:
If you run the code now all works fine you can also go to the "pData" worksheet and change the column name both the Central Tendency and occurrences table will also update the names accordingly, but if you create this as an Excel Add - ins. It runs fine, but when you change the column names on "pData" worksheet it does not update it on pwerDetail worksheet. Hope this help to see the issue i have.

[Paul_Hossler]

I put the macro code into the addin (XLAM), but the data is now in a stand-alone XLSX workbook

I made the add-in explicitly refer to Activework in multiple places, and changed Thisworkbook to Activeworkbook in one

I added the macro to the QAT but it could easily be added to the ribbon

I think that the way it was, it was updating the worksheets in the addin, but I'm GUESSING that you wanted the distributed macro to update the users' visible workbooks ???