Link to home
Start Free TrialLog in
Avatar of Peter Chan
Peter ChanFlag for Hong Kong

asked on

Share codes

Hi,
How to share codes to other Workbook, like to share library of codes, between Visual studio projects?
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Peter Chan

ASKER

Sorry, is there any example for that?
Do we open the same Excel workbook for sharing the codes, within VBA?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do you mean to create TAB and buttons on Excel? Do we create TAB by Visual studio project or not?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many thanks Roy.
I do the setup by the download file from

http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2006/05/26/customuieditor.aspx

then what will I get within Excel?
You need to read the articles that I suggested. Modifying the Ribbon is too comples to be explained in a forum question.
There is one setup file from the provided URL and the relevant URL is not giving much details of the giving functionality after the setup. Can you please advise?
Read the links that I gave you for Ron de Bruin's site.
Where should I  insert the customUI.xml file ?
You don't insert the CustomUI file. You open the CustomUI Editor and the select your workbook from the file option.

Write or copy, from an example, the code in editor. Use the save button to add the xml to the selected workbook. Open the woorkbook and the ribbon should be there.

There's no shortcuts here, it's a learning curve and you need to read the articles and examine the examples.
Very sorry, how to open CustomUI Editor ?
If ypou've installed it then you should have a desktop shortcut as with any program. Click that and then when the program opens browse for your workbook
Many thanks.
I can see the TAB within Excel file like

User generated image
how to further share the common codes through such TAB?
I have no idea what the code is that you are sharing. If you want to run a macro from the addin then you need to add a button to the new Tab. As I keep saying you need to spen some time reading and learning from my suggested links.
I did check the details per your given URL.

If we've added new button to the new TAB, how can we further share the common codes between different workbooks, as this is what I expect to have?
You distribute the addin and install it on each compu
Can I know if the codes for the TAB buttons are still inside the Excel files, correct?
The code remains in the addin. When you save the file as an addin it creates a special type of workbook that is invisible but the code is available to all open workbooks
Sorry, within UI editor, how to create the relevant Workbook which is invisible?
You do not create a workbook with the CustomUI Editor. You crate all your code within a workbook which is saved as an addin -.xlam. I have provided numerous links for this  for you
Due to limited licence, I did remove Excel 2013 on the server and then have set up Excel 2007 on that. Then I could not find out the previous tailor-made button per our UI editor. Any advice?
I mean I could not find out the previous tailor-made button within the same Excel file.
Open the CustomUI Editor and use it to select your file. From the Insert menu of the Editor select Office 2007 CustomUI part and create a button there.
I've done the action within UI editor but then I do not see the relevant TAB, within the Excel as attached.
Asset-Template.xlsm
Within the same machine.
I've corrected the xml but I need to access the code. The project is password protected and I am off to work now
Many thanks.
Can you please take this instead?
Asset-Template---Copy.xlsm
Any update?
I thought I had uploaded it.

The Custom Tab should show in all Ribbon versions of Excel.

I have used a dummy macro to demonstrate because I did not know which one to use with the button.

I also do not see how this can be used as an addin, how do you want to share the code?
I did edit the Excel file by the same, within UI Editor, but then I did not see the TAB button, within Excel 2007. What can be the reason?
I did edit the Excel file by the same steps (per the previous url given), within UI Editor, but then I did not see the TAB button, within Excel 2007. What can be the reason of this?
Many thanks.
Within other Workbooks, do we refer to the Addin Excel, using this?

Sub AutoExec()AddIns.Add fileName:="[Complete Path and File Name]", Install:=TrueEnd Sub

Open in new window

Why do you want to refer to an addin. The whole purpose of an addin is to make the code available to any open workbook which is what you originally asked
Yes, how can we share the codes of the Addin Excel, to all other Workbooks?
This post has been running for a month. I have provided links to read and actually corrected your work. I don't think you have read the articles or even my replies, if you had then you would not keep asking the same questions.

An addin is a special kind of workbook that allows code to be used by other open workbooks.

The code needs to be written so that is compatible with using this way.

When completed and fully tested the workbook should be saved as an addin and can then be installed on other computers.

I actually commented in a reply earlier that I could not see how you expected the workbook that you had posted to be used as an addin.
The sample I attached to this, IS NOT, addin file!

Within UI editor, we declare specific button and how can we involve some other codes, like events, to be shared among all other Workbook?

Per what I know, we can also create addin file, within Excel file, correct?
Event code to be shared is much more complicated than sharing macros.

I have no idea what you mean by creating an addin file within Excel file? You crate the code in a workbook which is then saved and installed as an addin.
Can I have the way more clearly, like to call more than one event, within "1st" workbook, that we would save, within UI editor, to share with other Workbooks, with all events inside?
Really sorry, for long time discussion to this. I think EE is also having the responsibility as she is not notifying us for the thread's update correctly!

Do you mind sharing your own 3 third party email for me to also give you one update, after each change to this thread?
Due to that EE always is keeping "silent" and we cannot immediately know the update to such thread!
EE updates, but I am not here 24/7.

You cannot save any event code in the UI Editor. As I said, running event code from an addin is complicated, you need to grasp using and creating an  addin firdt and I don't believe you have.
Really sorry.
I do expect to share more than one "event" among different workbooks. Can you please guide me to do these?
W£hat events do you want to share - one event is vague.
I said "share more than one event“ to different Excel files.
I asked what events. In my opinion I have now more than answered your original question. Sharing events with an addin was never mentioned originally.
I mean "Button_Click" event to share to other Workbook. It is not reasonable to restrict to have only one event shared to others, correct?
You have not grasped any of this. The code in the button should be a sub in the addin. The code would then be called by a button on the Ribbon not a button in workbooks.
Can I see the case to have several sub, which can be executed, by several button, on the Ribbon? Thanks a lot.
I have pointed you to several sites that contain articles and examples, especially Ron de Bruin. In my opinion I have more than answered the original question.
In the relevant example, actually where do we save this part of code?

Sub Macro1(control As IRibbonControl)
    MsgBox "Hi There"
End Sub

Open in new window


Excel Workbook?
I have no idea what you are asking. That code should be in a Standard Module ans will be called by the relevant Control on the Ribbon
We put
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>

      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">

          <button id="customButton1" label="Click Me" size="large" 
		onAction="Macro1" imageMso="HappyFace" />
        </group>
      </tab>

    </tabs>
  </ribbon>
</customUI>

Open in new window


to UI Editor, is this part

Sub Macro1(control As IRibbonControl)
    MsgBox "Hi There"
End Sub

Open in new window


existing within the relevant Workbook?
Have you tried it?
Yes, this is what I was originally asking, as I want to  see how I can share the codes like

Sub Macro1(control As IRibbonControl)
    MsgBox "Hi There"
End Sub

among different Workbooks.
That was not the original question. That code will run in any workbook if it is in an addin .
I have many codes, within one workbooks like

Sub Macro1()
    ...
End Sub
Sub Macro2()
    ...
End Sub
...

Open in new window

and I expect to automatically share such codes to many other Workbooks. How to achieve this through the current RibbonControl?
You need to add buttons for each macro
Are relevant macro/event codes stored into one Workbook?
I really don't think you have read the articles or understood them  I have repeatedly said that all macros are stored in one workbook and run from buttons in the Custom Tab. If by events you mean worksheet or workbook events then sharing them is beyond your capability based on your answers here.
Can I conclude:

we cannot put the "shared" codes, like Macro events, into one "centralized" place, to share among several Workbooks?
I have said several times if you are referring to workbook or worksheet events then this can be done but it is complicated. I have no idea what you mean by macro events
I have said several times if you are referring to workbook or worksheet events then this can be done but it is complicated.

How?
I've answered the original question and I am not prepared to spend another month trying to show you this.
Can you give me some hints of that, probably I would further check by one other thread, if that is possible?