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

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
--
Trefor

Hi Trefor,

Try:

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


---
Regards,
Norman


"Trefor" <[email protected]> wrote in message
news:[email protected]...
>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
> --
> Trefor

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.

--
Trefor


"Norman Jones" wrote:

> Hi Trefor,
>
> Try:
>
> Application.Run "'Main.xls'!Sheet1.MyMacro"
>
>
> ---
> Regards,
> Norman
>
>
> "Trefor" <[email protected]> wrote in message
> news:[email protected]...
> >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
> > --
> > Trefor
>
>
>

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.


---
Regards,
Norman

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"


--
Trefor


"Norman Jones" wrote:

> 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.
>
>
> ---
> Regards,
> Norman
>
>
>

Hi Trefor,

Perhaps the activeworkbook name includes spaces.

Try:

Dim WorkbookMain As String

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

Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"


---
Regards,
Norman


"Trefor" <[email protected]> wrote in message
news:[email protected]...
> 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"
>
>
> --
> Trefor

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)

"Trefor" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Trefor

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.

--
Trefor


"Bob Phillips" wrote:

> 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)
>
> "Trefor" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > --
> > Trefor
>
>
>

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:

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

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


---
Regards,
Norman

Norman,

Spot on!! Many, many thanks.

--
Trefor


"Norman Jones" wrote:

> Hi Trefor,
>
> Perhaps the activeworkbook name includes spaces.
>
> Try:
>
> Dim WorkbookMain As String
>
> WorkbookMain = "'" & ActiveWorkbook.Name & "'"
>
> Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"
>
>
> ---
> Regards,
> Norman
>
>
> "Trefor" <[email protected]> wrote in message
> news:[email protected]...
> > 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"
> >
> >
> > --
> > Trefor
>
>
>

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


--
Trefor


"Norman Jones" wrote:

> Hi Trefor,
>
> Perhaps the activeworkbook name includes spaces.
>
> Try:
>
> Dim WorkbookMain As String
>
> WorkbookMain = "'" & ActiveWorkbook.Name & "'"
>
> Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"
>
>
> ---
> Regards,
> Norman
>
>
> "Trefor" <[email protected]> wrote in message
> news:[email protected]...
> > 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"
> >
> >
> > --
> > Trefor
>
>
>

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


--
Regards,
Tom Ogilvy



"Trefor" wrote:

> 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
>
>
> --
> Trefor
>
>
> "Norman Jones" wrote:
>
> > Hi Trefor,
> >
> > Perhaps the activeworkbook name includes spaces.
> >
> > Try:
> >
> > Dim WorkbookMain As String
> >
> > WorkbookMain = "'" & ActiveWorkbook.Name & "'"
> >
> > Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> > "Trefor" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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"
> > >
> > >
> > > --
> > > Trefor
> >
> >
> >

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"

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.

--
Regards,
Tom Ogilvy


"Trefor" wrote:

> 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"
> .
> .
> CCRFsheetName(19) = "18. Connectivity Diagram 1.0"
> Set_CCRF_Names = CCRFsheetName
> End Function
>
>
> Can you tell me what have I done wrong please?
>
> --
> Trefor
>
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Trefor" wrote:
> >
> > > 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
> > >
> > >
> > > --
> > > Trefor
> > >
> > >
> > > "Norman Jones" wrote:
> > >
> > > > Hi Trefor,
> > > >
> > > > Perhaps the activeworkbook name includes spaces.
> > > >
> > > > Try:
> > > >
> > > > Dim WorkbookMain As String
> > > >
> > > > WorkbookMain = "'" & ActiveWorkbook.Name & "'"
> > > >
> > > > Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"
> > > >
> > > >
> > > > ---
> > > > Regards,
> > > > Norman
> > > >
> > > >
> > > > "Trefor" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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"
> > > > >
> > > > >
> > > > > --
> > > > > Trefor
> > > >
> > > >
> > > >

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"

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

--
Regards,
Tom Ogilvy

"Trefor" <[email protected]> wrote in message
news:[email protected]...
> 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"
> .
> .
> CCRFsheetName(19) = "18. Connectivity Diagram 1.0"
> Set_CCRF_Names = CCRFsheetName
> End Function
>
> Sub Open_Intro()
> CCRFsheet = Set_CCRF_Names
> Sheets(CCRFsheet(1)).Visible = True
> End Sub
>
> The above works fine.
>
> In the module of the XLA I have:
>
> Public CCRFsheet As Variant
> .
> .
> Sub TestGetArray()
> WorkbookMain = ActiveWorkBook.Name
> Dim TempWBMain
> TempWBMain = "'" & WorkbookMain & "'"
>
> CCRFsheet = Application.Run(TempWBMain & "!Sheet13.Set_CCRF_Names")
> Sheets(CCRFsheet(1)).Visible = True <<<-- This errors, because
> CCRFsheet(1) = Type Mismatch
> End Sub
>
>
>
>
> --
> Trefor
>
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Trefor" wrote:
> >
> > > 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"
> > > .
> > > .
> > > CCRFsheetName(19) = "18. Connectivity Diagram 1.0"
> > > Set_CCRF_Names = CCRFsheetName
> > > End Function
> > >
> > >
> > > Can you tell me what have I done wrong please?
> > >
> > > --
> > > Trefor
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "Trefor" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > --
> > > > > Trefor
> > > > >
> > > > >
> > > > > "Norman Jones" wrote:
> > > > >
> > > > > > Hi Trefor,
> > > > > >
> > > > > > Perhaps the activeworkbook name includes spaces.
> > > > > >
> > > > > > Try:
> > > > > >
> > > > > > Dim WorkbookMain As String
> > > > > >
> > > > > > WorkbookMain = "'" & ActiveWorkbook.Name & "'"
> > > > > >
> > > > > > Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"
> > > > > >
> > > > > >
> > > > > > ---
> > > > > > Regards,
> > > > > > Norman
> > > > > >
> > > > > >
> > > > > > "Trefor" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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"
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Trefor
> > > > > >
> > > > > >
> > > > > >

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?

--
Trefor


"Tom Ogilvy" wrote:

> Like I said, it doesn't appear to work for me if the function is in a Sheet
> module.
>
> --
> Regards,
> Tom Ogilvy
>
> "Trefor" <[email protected]> wrote in message
> news:[email protected]...
> > 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"
> > .
> > .
> > CCRFsheetName(19) = "18. Connectivity Diagram 1.0"
> > Set_CCRF_Names = CCRFsheetName
> > End Function
> >
> > Sub Open_Intro()
> > CCRFsheet = Set_CCRF_Names
> > Sheets(CCRFsheet(1)).Visible = True
> > End Sub
> >
> > The above works fine.
> >
> > In the module of the XLA I have:
> >
> > Public CCRFsheet As Variant
> > .
> > .
> > Sub TestGetArray()
> > WorkbookMain = ActiveWorkBook.Name
> > Dim TempWBMain
> > TempWBMain = "'" & WorkbookMain & "'"
> >
> > CCRFsheet = Application.Run(TempWBMain & "!Sheet13.Set_CCRF_Names")
> > Sheets(CCRFsheet(1)).Visible = True <<<-- This errors, because
> > CCRFsheet(1) = Type Mismatch
> > End Sub
> >
> >
> >
> >
> > --
> > Trefor
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Trefor" wrote:
> > >
> > > > 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"
> > > > .
> > > > .
> > > > CCRFsheetName(19) = "18. Connectivity Diagram 1.0"
> > > > Set_CCRF_Names = CCRFsheetName
> > > > End Function
> > > >
> > > >
> > > > Can you tell me what have I done wrong please?
> > > >
> > > > --
> > > > Trefor
> > > >
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > >
> > > > > "Trefor" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Trefor
> > > > > >
> > > > > >
> > > > > > "Norman Jones" wrote:
> > > > > >
> > > > > > > Hi Trefor,
> > > > > > >
> > > > > > > Perhaps the activeworkbook name includes spaces.
> > > > > > >
> > > > > > > Try:
> > > > > > >
> > > > > > > Dim WorkbookMain As String
> > > > > > >
> > > > > > > WorkbookMain = "'" & ActiveWorkbook.Name & "'"
> > > > > > >
> > > > > > > Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"
> > > > > > >
> > > > > > >
> > > > > > > ---
> > > > > > > Regards,
> > > > > > > Norman
> > > > > > >
> > > > > > >
> > > > > > > "Trefor" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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"
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Trefor
> > > > > > >
> > > > > > >
> > > > > > >
>
>
>