Application.Run error

[harm.charles]

Hi,

I'm using the following code to do a call for a function in a workbook
called "Code"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Run ("Code.xls!ModuleWorksheet_SelectionChange(Target)")
End Sub

This code is in my primary project. However I receive an error that
say's the Macro cannot be found.
The "Code.xls" is in a workbook that holds of the codes I wish to use
in my project.(different workbook)
I've used various formats for the above code found on this forum.
Using Excel 2003 windows XP
Any help would be great.

Thanks

 

[Susan]

there are certain words that you shouldn't use as the name of a module
or code or function ........ "code" is one of them.
try renaming your function to something like MyCode and try again.

susan

 

[Susan]

in theory i'm right, although i did misread your post. try renaming
your workbook - anything but code.xls.

susan

 

[harm.charles]

there are certain words that you shouldn't use as the name of a module
or code or function ........ "code" is one of them.
try renaming your function to something like MyCode and try again.

susan

Thanks for the reply.
I renamed the "Code" to "MyCode" and still received the error Macro
cannot be found.
I also re-named the Function ModuleWorksheet_SelectionChange(Target)
to
Worksheet_SelectionChange(Target)
Still received the error.
The project workbook holds the all of the call procedures.
The "MyCode" workbooks holds all of the Functions I wish to use.
All of the "Function" are declared as "Public" in a Module.

 

[Dave Peterson]

You know the sheet codename/module name that should be called, right.

I used Sheet2 (just to be different).

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wkbk As Workbook
Set wkbk = Workbooks("Code.xls")
Application.Run _
"'" & wkbk.Name & "'!sheet2.Worksheet_SelectionChange", Target
End Sub

 

[Peter T]

With App.Run you can only call a procedures in a normal module. Also, you
can only pass values as arguments. So, even if you were calling a normal
routine you want not be able to pass the expected range object (your way
would pass its default value property).

In a normal module in Code.xls include a macro like this -

Sub myMacro(sAddr As String)
Dim rng As Range

On Error GoTo errH
Set rng = Range(sAddr) ' recreate the range object
' do stuff here
MsgBox rng(1).Value

Exit Sub
errH:
MsgBox Err.Description

End Sub

In your other workbook call the macro and pass the full address of the range
Target

Application.Run "Code.xls!myMacro", Target.Address(external:=True)

Regards,
Peter T

 

[Susan]

ok. sorry, i don't know, then. maybe this thread will help

http://groups.google.com/group/micr...en&lnk=gst&q=application.run#7243b70e2c6cc712

sorry!
susan

 

[Dave Peterson]

This won't work.

Target is still a range that points to the cells that changed in the original
worksheet. (I don't think that's what you want.)

Maybe...

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wks As Worksheet

Set wks = Workbooks("code.xls").Worksheets("sheet2")

With wks
Application.Run "'" & .Parent.Name & "'!" _
& .CodeName & ".Worksheet_SelectionChange", .Range(Target.Address)
End With

End Sub

 

[Peter T]

Having read Dave's, sheepishly I must request please ignore mine !

(must have had OnTime in mind)

Regards,
Peter T

 

[harm.charles]

With App.Run you can only call a procedures in a normal module. Also, you
can only pass values as arguments. So, even if you were calling a normal
routine you want not be able to pass the expected range object (your way
would pass its default value property).

In a normal module in Code.xls include a macro like this -

Sub myMacro(sAddr As String)
Dim rng As Range

    On Error GoTo errH
    Set rng = Range(sAddr) ' recreate the range object
    ' do stuff here
    MsgBox rng(1).Value

    Exit Sub
errH:
    MsgBox Err.Description

End Sub

In your other workbook call the macro and pass the full address of the range
Target

Application.Run "Code.xls!myMacro", Target.Address(external:=True)

Regards,
Peter T

Ok,

Thanks to all.
I tried "Daves" approach and it gets me to the "Function" I'm looking
for.
However I did remove the "sheet1" in his code.
Once again the forum came through.