Making Macros available to other users?

  • Thread starter Thread starter John
  • Start date Start date
J

John

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,

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,

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)
Click to expand...

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,

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,

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?
Click to expand...


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,

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,

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.
Click to expand...

Thanks very much for your help

John
 
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
 
Back
Top