Can you run a macro on a worksheet from a module in an XLA?

[Guest]

I have some code on a worksheet in an .XLS which uses a .XLA with some more
VBA code in it. Is it possible for a macro in the .XLA to run another macro
in the .XLS sheet?

What would the syntax be something like? (which does not work of course)

workbooks("Main.XLS").sheet1.mymacro

 

[Norman Jones]

Hi Trefor,

Try:

Application.Run "'Main.xls'!Sheet1.MyMacro"

 

[Guest]

Norman,

Many thanks for the prompt reply. I have used this to run a macro from an
XLS to a macro in an XLA, but not the other way round, but normally in a
Module not a worksheet.

When I run this as you describe I get an error 1004 saying the macro can not
be found.

 

[Norman Jones]

Hi Trefor,

Many thanks for the prompt reply. I have used this to run a macro from
an XLS to a macro in an XLA, but not the other way round, but
normally in a Module not a worksheet.

When I run this as you describe I get an error 1004 saying the macro
can not be found.

The suggested syntax works for me. Check that the workbook name is correct
and is not missing any spaces / does not include any extraneous spaces.

Note also that the suggested syntax wraps the workbook name in single
quotes. This is to allow for possible spaces in the name.

If the problem persists, paste the relevant code line in your response and
confiirm where the code is housed.

 

[Guest]

Norman,

WorkbookMain = ActiveWorkbook.Name

This is in a module of the .XLA. Set_CCRF_Names is the macro name on Sheet13

Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"

 

[Norman Jones]

Hi Trefor,

Perhaps the activeworkbook name includes spaces.

Try:

Dim WorkbookMain As String

WorkbookMain = "'" & ActiveWorkbook.Name & "'"

Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"

 

[Bob Phillips]

Trefor,

Whilst I am sure that Norman's suggested way will work, this does not seem
like good design to me.

If you call a macro in an add-in from another worksheet, that is okay as the
add-in will always be loaded, and you can easily test for it. However, other
way around, the workbook may not be open, or worse, you may have many
workbooks with that macro (I am assuming that they might be template based).

Why do you need to have that macro in the standard workbook?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,

Many thanks for the reply.

Your comments are very valid and understood. In my case the XLS containing
the end macro to be run will also be the same XLS that calls the macro in the
XLA. It is possible in my case to have multiple XLS's open and I keep track
of this by setting the variable WorkbookMain = ActiveWorkbook.Name. So it is
not possible for the XLA to call the Macro in the XLS, without the XLS
starting the whole process in the first place.

So why am I doing this in the first place. The XLS contains a number of
sheets, on request the user can click on various button's and these buttons
will cause in one case a sub set of these sheets to be "exported" or copied
into another workbook. I need some macro's in the new workbook, BUT both the
original XLS and the XLA are protected, so I can't simply copy a Module from
one to another. I had tried exporting the module to a .BAS and then using:

ActiveWorkBook.VBProject.VBComponents.Import (MainPath + "\CCRF.bas") to
copy the macro's into the new XLS.

But this has several disadvantages 1. It mean having an unprotected .BAS
which could then be read and/or modied. 2. It means having an external/extra
file, but worst of all 3. it means I need to check "Trust access to Visual
Basic Project", which has to be done manually by everyone that uses the XLS
for the first time.

To avoid this I thought I would copy the macro into a sheet, that way no
unprotected separate file, no need to make any special changes in Excel and
in theory I can access from the Main XLS AND from the New XLS.

Currently my work around, is to have this macro in both the sheet and the
main module of the XLA. The only catch (other than the extra code) is I have
to maintain two subs not one.

 

[Guest]

Norman,

Spot on!! Many, many thanks.

 

[Norman Jones]

Hi Bob,

Whilst I am sure that Norman's suggested way will work, this does
not seem like good design to me.

I completely endorse your design concerns.

Somewhat lazily, I responded to Trefor's:

 

[Guest]

Norman,

I have another little catch, how do I get the variables values back? The sub
in the sheet sets some variables and I need those values. I tried your
example with the variables I want, but they come back empty:

Dim TempWBMain

TempWBMain = "'" & WorkbookMain & "'"

Application.Run TempWBMain & "!Sheet13.Set_CCRF_Names", CCRFsheet1,
CCRFsheet2, CCRFsheet3, CCRFsheet4, CCRFsheet5, CCRFsheet6, CCRFsheet7a,
CCRFsheet7b, CCRFsheet8, CCRFsheet9, CCRFsheet10, CCRFsheet11, CCRFsheet12,
CCRFsheet13, CCRFsheet14, CCRFsheet15, CCRFsheet16, CCRFsheet17, CCRFsheet18

 

[Guest]

Make the procedure called a Function and have it return an array of values.

For example, in the calling document

Sub test()
Dim a As Variant
a = Application.Run("Book3!PopulateArray", "a1", "b1", "c1", "d1", "e1", "f1")
For i = LBound(a) To UBound(a)
Debug.Print i, a(i)
Next

End Sub

in the called workbook. (Book3 in the example)


Function PopulateArray(a, b, c, d, e, f)
ReDim v(1 To 6)
v(1) = a
v(2) = b
v(3) = c
v(4) = d
v(5) = e
v(6) = f
PopulateArray = v
End Function

 

[Guest]

Tom,

Many thanks for your reply. I don't need to pass any variable to the
function, I just need them back so I presume this are not required?

This is my calling sub in a module in the XLA:

Sub TestGetArray()
WorkbookMain = ActiveWorkBook.Name
Dim TempWBMain, CCRFsheet As Variant
TempWBMain = "'" & WorkbookMain & "'"

CCRFsheet = Application.Run(TempWBMain & "!Sheet13.Set_CCRF_Names")

' Set_CCRF_Names
DCSheet(DCSheet1%) = CCRFsheet(1) <<<==== Type Mismatch
End Sub


This is on my sheet in the XLS:

Function Set_CCRF_Names()
ReDim CCRFsheetName(1 To 19)
CCRFsheetName(1) = "1. Introduction & Help"

 

[Guest]

I don't have any problems getting results with

Sub TestGetArray()
WorkbookMain = ActiveWorkbook.Name
Dim TempWBMain, CCRFsheet As Variant

TempWBMain = "'" & WorkbookMain & "'"

CCRFsheet = Application.Run(TempWBMain & "!Module2.Set_CCRF_Names")

' Set_CCRF_Names
ThisWorkbook.Worksheets(1) _
.Range("A1").Resize( _
UBound(CCRFsheet) - _
LBound(CCRFsheet) + 1) _
.Value = Application.Transpose(CCRFsheet)
End Sub

and the Set_CCRF_Names in a general module in the activeworkbook.

I get a macro not found if I put it in a worksheet module.

However, you indicate you don't have any problem with that for a simple sub
located in sheet13.

 

[Guest]

Tom,

I must be doing something really basic wrong. In the Sheet of the XLS I have:

Public CCRFsheet As Variant
..
..
Function Set_CCRF_Names()
ReDim CCRFsheetName(1 To 19)
CCRFsheetName(1) = "1. Introduction & Help"

 

[Tom Ogilvy]

Like I said, it doesn't appear to work for me if the function is in a Sheet
module.

 

[Guest]

Tom,

Sorry I misunderstood.

So you have the same problem as me and there is no workaround?

My original issue was trying to get variables loaded with values from a
Sheet macro and using those values in a modulae macro in another file. Is
there a better or another way arounf this?