Making Macros available to other users?

Discussion in 'Computer Information' started by John, Mar 6, 2004.

  1. John

    John Guest

    Hello.

    I just have a few questions about sharing my macros with other users.

    I have two macros in Excel that are saved in the personal macro
    workbook. I have also put logos on my tool bar that I drew myself
    that run the macros.

    I just wondered, if I want other people to be able to use these macros
    can I put the personal macro workbook on a drive accessible to all
    users? I think maybe if it is possible to save it as an external
    Microsoft Excel Add-in .xla it would be a good idea? Do you know of a
    way to export macros so they become external add-ins for Excel?

    It would be good if I could just instal an external add-in on all
    these machines which would bring in the logo's I designed for the
    macros as well. It would save a lot of messing around.

    Anyway if you know how to do any of this I'd love to hear from you.

    Thanks very much for any help you can offer.

    John
    John, Mar 6, 2004
    #1
    1. Advertising

  2. John

    Bob Phillips Guest

    John,

    An addin is just an Excel workbook, but saved as type xla not xls. So put
    your macros in a new workbook, and just save it as an xls (bottom option on
    file types in the save dialog).

    Then go and install it in Tools>Addins by clicking Browse, selecting the
    file, and then checking the new entry.

    The main thing to take care of is to ensure that you always specifically
    refer to a workbook/worksheet, as normally non-referenced code works on the
    active workbook/activesheet, but they will not be visible to addin code (an
    addin has an activeworkbook and an activesheet, you just cannot see them).

    --

    HTH

    Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
    (remove nothere from the email address if mailing direct)

    "John" <> wrote in message
    news:...
    > Hello.
    >
    > I just have a few questions about sharing my macros with other users.
    >
    > I have two macros in Excel that are saved in the personal macro
    > workbook. I have also put logos on my tool bar that I drew myself
    > that run the macros.
    >
    > I just wondered, if I want other people to be able to use these macros
    > can I put the personal macro workbook on a drive accessible to all
    > users? I think maybe if it is possible to save it as an external
    > Microsoft Excel Add-in .xla it would be a good idea? Do you know of a
    > way to export macros so they become external add-ins for Excel?
    >
    > It would be good if I could just instal an external add-in on all
    > these machines which would bring in the logo's I designed for the
    > macros as well. It would save a lot of messing around.
    >
    > Anyway if you know how to do any of this I'd love to hear from you.
    >
    > Thanks very much for any help you can offer.
    >
    > John
    >
    >
    Bob Phillips, Mar 6, 2004
    #2
    1. Advertising

  3. John

    John Guest

    On Sat, 6 Mar 2004 22:52:55 -0000, "Bob Phillips"
    <> wrote:

    >John,
    >
    >An addin is just an Excel workbook, but saved as type xla not xls. So put
    >your macros in a new workbook, and just save it as an xls (bottom option on
    >file types in the save dialog).
    >
    >Then go and install it in Tools>Addins by clicking Browse, selecting the
    >file, and then checking the new entry.
    >
    >The main thing to take care of is to ensure that you always specifically
    >refer to a workbook/worksheet, as normally non-referenced code works on the
    >active workbook/activesheet, but they will not be visible to addin code (an
    >addin has an activeworkbook and an activesheet, you just cannot see them).
    >
    >--
    >
    >HTH
    >
    >Bob Phillips
    > ... looking out across Poole Harbour to the Purbecks
    >(remove nothere from the email address if mailing direct)


    Cheers Bob, this helps a lot.

    As far as the referencing of the code goes...

    If I have a macro to replace certain data within a spreadsheet with
    alternate data, if it is possible for it to cross-reference within
    another spreadsheet instead of being within the macro itself, how to I
    go about it?

    For example I might have the following macro:

    Sub MacroA()
    '
    ' Macro A
    ' Macro recorded 10/12/03
    '
    ' Keyborad Shortcut Ctrl+k
    '
    Columns(1).Replace What:="1111", Replacement:="AAAA",
    LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
    Columns(1).Replace What:="2222", Replacement:="BBBB",
    LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
    Columns(1).Replace What:="3333", Replacement:="CCCC",
    LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False

    End Sub

    This macro replaces 1111 with aaaa etc etc.

    If I put the replacements within a spreadsheet instead of the macro,
    and for example I enter the 1111 in cell 1 of column a, and the aaaa
    in cell 1 of column b, and so on.

    It would be more organised this way, and it would enable me to just
    update the list within the spreadsheet itself, instead of having to go
    to the macro each time to edit. And I would also be able to put the
    spreadsheet on a drive accessible to all users, so it would be the
    same location.

    If it's possible to do this, I just wondered what the formula would
    then become, say for example the spreadsheet with the replacement list
    was located on drive F and called replacements.xls, and it was within
    Sheet1?

    Columns(1).Replace What:="1111", Replacement:="AAAA",
    LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False

    What would the 1111 and the aaaa above change to? Would it be
    something along the lines of:

    Columns(1).Replace What:="F:/replacements.xls'Sheet1'!A1",
    Replacement:="F:/replacements.xls'Sheet1'!B1", LookAt;=xlWhole,_
    SearchOrder:=xlByRows, MatchCase:=False

    Thanks very much for any info on this. I always get confused with the
    exact way to enter the file location plus the sheet and cell
    reference.

    John
    John, Mar 6, 2004
    #3
  4. John

    Bob Phillips Guest

    John,

    You would still need to open the file with the replacement criteria, you
    cannot access a closed workbook (well actually you can, but let's not
    confuse this with that complexity).

    So you would have some code along the lines of

    Dim oWbTarget As Workbook
    Dim oWbCriteria As Workbook
    Dim sReplace As String
    Dim sTo As String

    Set oWbTarget = Workbooks.Open
    Filename:="C:\SomeDir\SomeSUbDir\SomeFile.xls"
    'this sets a pointer to the workbook to be worked upon
    Set oWbCriteria = Workbooks.Open Filename:="F:\myDrive\replacements.xls"
    'you now have a pointer to this workbook that can be used throughout the
    code
    sReplace = oWBCriteria.Worksheets("Sheet1").Range("A1")
    sTo = oWBCriteria.Worksheets("Sheet1").Range("B1")

    'now replace it
    With oWbTarget
    .Columns(1).Replace What:=sReplace, _
    Replacement:=sTo, _
    LookAt;=xlWhole, _
    SearchOrder:=xlByRows, _
    MatchCase:=False
    End With

    Note that my point about the activesheet on an addin is covered here. In
    your code, Columns assumed the activesheet, whereas this code always points
    explicitly at a sheet and workbook.

    Get the idea?

    --

    HTH

    Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
    (remove nothere from the email address if mailing direct)

    "John" <> wrote in message
    news:...
    > On Sat, 6 Mar 2004 22:52:55 -0000, "Bob Phillips"
    > <> wrote:
    >
    > Cheers Bob, this helps a lot.
    >
    > As far as the referencing of the code goes...
    >
    > If I have a macro to replace certain data within a spreadsheet with
    > alternate data, if it is possible for it to cross-reference within
    > another spreadsheet instead of being within the macro itself, how to I
    > go about it?
    >
    > For example I might have the following macro:
    >
    > Sub MacroA()
    > '
    > ' Macro A
    > ' Macro recorded 10/12/03
    > '
    > ' Keyborad Shortcut Ctrl+k
    > '
    > Columns(1).Replace What:="1111", Replacement:="AAAA",
    > LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
    > Columns(1).Replace What:="2222", Replacement:="BBBB",
    > LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
    > Columns(1).Replace What:="3333", Replacement:="CCCC",
    > LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
    >
    > End Sub
    >
    > This macro replaces 1111 with aaaa etc etc.
    >
    > If I put the replacements within a spreadsheet instead of the macro,
    > and for example I enter the 1111 in cell 1 of column a, and the aaaa
    > in cell 1 of column b, and so on.
    >
    > It would be more organised this way, and it would enable me to just
    > update the list within the spreadsheet itself, instead of having to go
    > to the macro each time to edit. And I would also be able to put the
    > spreadsheet on a drive accessible to all users, so it would be the
    > same location.
    >
    > If it's possible to do this, I just wondered what the formula would
    > then become, say for example the spreadsheet with the replacement list
    > was located on drive F and called replacements.xls, and it was within
    > Sheet1?
    >
    > Columns(1).Replace What:="1111", Replacement:="AAAA",
    > LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
    >
    > What would the 1111 and the aaaa above change to? Would it be
    > something along the lines of:
    >
    > Columns(1).Replace What:="F:/replacements.xls'Sheet1'!A1",
    > Replacement:="F:/replacements.xls'Sheet1'!B1", LookAt;=xlWhole,_
    > SearchOrder:=xlByRows, MatchCase:=False
    >
    > Thanks very much for any info on this. I always get confused with the
    > exact way to enter the file location plus the sheet and cell
    > reference.
    >
    > John
    >
    >
    Bob Phillips, Mar 7, 2004
    #4
  5. John

    John Guest

    On Sun, 7 Mar 2004 00:01:23 -0000, "Bob Phillips"
    <> wrote:

    >John,
    >
    >You would still need to open the file with the replacement criteria, you
    >cannot access a closed workbook (well actually you can, but let's not
    >confuse this with that complexity).
    >
    >So you would have some code along the lines of
    >
    >Dim oWbTarget As Workbook
    >Dim oWbCriteria As Workbook
    >Dim sReplace As String
    >Dim sTo As String
    >
    > Set oWbTarget = Workbooks.Open
    >Filename:="C:\SomeDir\SomeSUbDir\SomeFile.xls"
    > 'this sets a pointer to the workbook to be worked upon
    > Set oWbCriteria = Workbooks.Open Filename:="F:\myDrive\replacements.xls"
    > 'you now have a pointer to this workbook that can be used throughout the
    >code
    > sReplace = oWBCriteria.Worksheets("Sheet1").Range("A1")
    > sTo = oWBCriteria.Worksheets("Sheet1").Range("B1")
    >
    > 'now replace it
    > With oWbTarget
    > .Columns(1).Replace What:=sReplace, _
    > Replacement:=sTo, _
    > LookAt;=xlWhole, _
    > SearchOrder:=xlByRows, _
    > MatchCase:=False
    > End With
    >
    >Note that my point about the activesheet on an addin is covered here. In
    >your code, Columns assumed the activesheet, whereas this code always points
    >explicitly at a sheet and workbook.
    >
    >Get the idea?



    Yep. I think it's a bit complex but I just about get it.

    I just have one final question as far as the opening of the workbook
    goes. Would it cause any problems if lots of different users are
    trying to open the same workbook, or would it just open as read-only
    anyway and still be able to work?

    Thanks for your help

    John
    John, Mar 7, 2004
    #5
  6. John

    Bob Phillips Guest

    John,

    I assume that you are referring to the workbook with replacement criteria? I
    don't think that this would be a problem, it would just open as read only,
    which would be fine as I assume you would only want certain user to update
    that file. In that respect it might be best to restrict the update
    permissions on the file, and/or make it read-only.
    --

    HTH

    Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
    (remove nothere from the email address if mailing direct)

    "John" <> wrote in message
    news:...
    > On Sun, 7 Mar 2004 00:01:23 -0000, "Bob Phillips"
    > <> wrote:
    >
    > >John,
    > >
    > >You would still need to open the file with the replacement criteria, you
    > >cannot access a closed workbook (well actually you can, but let's not
    > >confuse this with that complexity).
    > >
    > >So you would have some code along the lines of
    > >
    > >Dim oWbTarget As Workbook
    > >Dim oWbCriteria As Workbook
    > >Dim sReplace As String
    > >Dim sTo As String
    > >
    > > Set oWbTarget = Workbooks.Open
    > >Filename:="C:\SomeDir\SomeSUbDir\SomeFile.xls"
    > > 'this sets a pointer to the workbook to be worked upon
    > > Set oWbCriteria = Workbooks.Open

    Filename:="F:\myDrive\replacements.xls"
    > > 'you now have a pointer to this workbook that can be used throughout

    the
    > >code
    > > sReplace = oWBCriteria.Worksheets("Sheet1").Range("A1")
    > > sTo = oWBCriteria.Worksheets("Sheet1").Range("B1")
    > >
    > > 'now replace it
    > > With oWbTarget
    > > .Columns(1).Replace What:=sReplace, _
    > > Replacement:=sTo, _
    > > LookAt;=xlWhole, _
    > > SearchOrder:=xlByRows, _
    > > MatchCase:=False
    > > End With
    > >
    > >Note that my point about the activesheet on an addin is covered here. In
    > >your code, Columns assumed the activesheet, whereas this code always

    points
    > >explicitly at a sheet and workbook.
    > >
    > >Get the idea?

    >
    >
    > Yep. I think it's a bit complex but I just about get it.
    >
    > I just have one final question as far as the opening of the workbook
    > goes. Would it cause any problems if lots of different users are
    > trying to open the same workbook, or would it just open as read-only
    > anyway and still be able to work?
    >
    > Thanks for your help
    >
    > John
    >
    >
    Bob Phillips, Mar 7, 2004
    #6
  7. John

    John Guest

    On Sun, 7 Mar 2004 14:33:29 -0000, "Bob Phillips"
    <> wrote:

    >John,
    >
    >I assume that you are referring to the workbook with replacement criteria? I
    >don't think that this would be a problem, it would just open as read only,
    >which would be fine as I assume you would only want certain user to update
    >that file. In that respect it might be best to restrict the update
    >permissions on the file, and/or make it read-only.


    Thanks very much for your help

    John
    John, Mar 7, 2004
    #7
  8. John

    Jordan Guest

    I guess John I dont understand your question. When you save the document
    with macro's in Vba dosent it automatically save the macros along with it?

    -Jordan

    "John" <> wrote in message
    news:...
    > Hello.
    >
    > I just have a few questions about sharing my macros with other users.
    >
    > I have two macros in Excel that are saved in the personal macro
    > workbook. I have also put logos on my tool bar that I drew myself
    > that run the macros.
    >
    > I just wondered, if I want other people to be able to use these macros
    > can I put the personal macro workbook on a drive accessible to all
    > users? I think maybe if it is possible to save it as an external
    > Microsoft Excel Add-in .xla it would be a good idea? Do you know of a
    > way to export macros so they become external add-ins for Excel?
    >
    > It would be good if I could just instal an external add-in on all
    > these machines which would bring in the logo's I designed for the
    > macros as well. It would save a lot of messing around.
    >
    > Anyway if you know how to do any of this I'd love to hear from you.
    >
    > Thanks very much for any help you can offer.
    >
    > John
    >
    >
    >
    Jordan, Apr 13, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bernie McAlister

    Macros disabled

    Bernie McAlister, Aug 23, 2003, in forum: Computer Support
    Replies:
    1
    Views:
    512
    ┬░Mike┬░
    Aug 23, 2003
  2. ]v[etaphoid

    Site that teachs how to write Excel macros?

    ]v[etaphoid, Nov 28, 2003, in forum: Computer Support
    Replies:
    3
    Views:
    615
  3. Replies:
    8
    Views:
    1,047
  4. Macros in IE

    , Apr 2, 2005, in forum: Computer Support
    Replies:
    9
    Views:
    10,501
    Liebschen
    Jun 28, 2009
  5. Saqib Ali

    Macros of Hungarian Wax Peppers - Take 2

    Saqib Ali, Jul 29, 2003, in forum: Digital Photography
    Replies:
    4
    Views:
    361
    Saqib Ali
    Jul 30, 2003
Loading...

Share This Page