Run worksheet module from standard module

YasserKhalil
PlatinumLounger
Posts: 4659
Joined: 31 Aug 2016, 09:02

Run worksheet module from standard module

Post by YasserKhalil »

Hello everyone

I have a worksheet module in Sheet1
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

How can I run this worksheet module from a standard module?
I tried

Code: Select all

Application.Run "Sheet1.Worksheet_BeforeDoubleClick"
But this throws an error that the argument not optional. I tried to add the range and boolean value True as arguments but it displayed as red which refers to an error in syntax

YasserKhalil
PlatinumLounger
Posts: 4659
Joined: 31 Aug 2016, 09:02

Re: Run worksheet module from standard module

Post by YasserKhalil »

I even tried this
CallByName Sheet1, "Worksheet_BeforeDoubleClick", VbMethod, Range("K1"), True

Peter T
NewLounger
Posts: 15
Joined: 27 Dec 2021, 12:17

Re: Run worksheet module from standard module

Post by Peter T »

Change Private to Public and call in the normal way, eg
Dim bCancel As Boolean
Sheet1.Worksheet_BeforeDoubleClick ActiveCell, bCancel
Worksheets("Sheet1").Worksheet_BeforeDoubleClick ActiveCell, bCancel

Consider moving whatever you have in the BeforeDoubleClick stub to its own routine. and call it from the event as well as your normal module. If you move it to a normal module you'll need it qualify any cell references to Sheet1, otherwise they'll refer to the ActiveSheet which might not be Sheet1.

Peter T

YasserKhalil
PlatinumLounger
Posts: 4659
Joined: 31 Aug 2016, 09:02

Re: Run worksheet module from standard module

Post by YasserKhalil »

Thanks a lot for this working solution.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Run worksheet module from standard module

Post by Doc.AElstein »

Hi Yasser,
YasserKhalil wrote:
15 Jan 2022, 09:24
... throws an error that the argument not optional. I tried to add the range and boolean value True as arguments but it displayed as red which refers to an error in syntax
I think the syntax on things like Application.Run can be a bit tricky, but it should work

Example:
This in your worksheet code module

Code: Select all

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Let Target.Value2 = "This cell ya double clicked yaz"
End Sub
Then these sort of things should work in a normal code module to set that macro above off

Code: Select all

' https://eileenslounge.com/viewtopic.php?f=30&t=37685
Sub CallMeBeautifulPrivates()
Dim bCancel As Boolean
 Application.Run Macro:="'Sheet1.Worksheet_BeforeDoubleClick'", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="      Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="Sheet1.Worksheet_BeforeDoubleClick", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run "Sheet1.Worksheet_BeforeDoubleClick   ", ActiveCell, bCancel
 Application.Run Macro:="'Book1.xls'!Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!          Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!'Sheet1.Worksheet_BeforeDoubleClick'", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!          'Sheet1.Worksheet_BeforeDoubleClick'", Arg1:=ActiveCell, Arg2:=bCancel

End Sub


' Ref
' https://excelfox.com/forum/showthread.php/2404-Notes-tests-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11870&viewfull=1#post11870
' https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime
' https://web.archive.org/web/20180101022746/http://excelmatters.com/2017/04/07/passing-arguments-byref-using-run/#comment-205985
Alan
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4659
Joined: 31 Aug 2016, 09:02

Re: Run worksheet module from standard module

Post by YasserKhalil »

Amazing Mr. Alan. This is what I was searching for exactly as this keeps the worksheet module Private not Public.

User avatar
rory
5StarLounger
Posts: 760
Joined: 24 Jan 2010, 15:56

Re: Run worksheet module from standard module

Post by rory »

Why do you want to keep it private when you need to call it from elsewhere? It makes no sense to me. IMO, Peter's approach is the better one.
Regards,
Rory

YasserKhalil
PlatinumLounger
Posts: 4659
Joined: 31 Aug 2016, 09:02

Re: Run worksheet module from standard module

Post by YasserKhalil »

It is just a custom for me to make codes in worksheet modules as Private not as Public.

User avatar
rory
5StarLounger
Posts: 760
Joined: 24 Jan 2010, 15:56

Re: Run worksheet module from standard module

Post by rory »

But it makes no sense if you need to call it from somewhere else. Private scope literally means that it should not be available to external callers. IMO, it should either be in a separate routine in a normal module, called from both places, or, if it really is specific to that worksheet, it could be a public method in that sheet, called from both locations.
Regards,
Rory

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

I have in the past exposed things by making my Privates Public. I am not sure if it was always a good idea.

Post by Doc.AElstein »

Application.Run decouples us to a point in the dependance tree allowing us a synergy consitant enough to avoid conflicts in the underlying principals of the hierachical structure and is, as such, appropriate to use in this instance, we may otherwise be exposing interfaces inappropriately.

I have in the past exposed things by making my Privates Public. I am not sure if it was always a good idea.
I have also sometimes exposed things by making my Privates Public, in coding also, in the way Peter has suggested, and it’s a welcome suggestion and contribution from Peter, I feel..
Doing this has allowed me also, for example, to make variables at the top of a class object module accessable as if they were Public variables available anywhere.
I have a feeling its more in the general order of things to keep these things Private if possible. I am not sure why. I am not so well educated in these Class and Object Orientated Programing concepts, but those that are seem to suggest getting at things in the class object module should be done in a more complicated way, through the Get Let stuff, but once again this is more of a gut feeling on my behalf as i am not so well clued up on the academic side of Class and Object Orientated Programing concepts. On the other hand I am not sure if anoyne is. These concepts seem to be deliberately left a bit vague sometimes.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
rory
5StarLounger
Posts: 760
Joined: 24 Jan 2010, 15:56

Re: Run worksheet module from standard module

Post by rory »

I disagree with pretty much all of that, other than the fact you should keep your privates to yourself. ;)
Regards,
Rory

Peter T
NewLounger
Posts: 15
Joined: 27 Dec 2021, 12:17

Re: Run worksheet module from standard module

Post by Peter T »

@Rory, Is that really the only thing you don’t disagree with, what about that ‘welcome ... contribution..’ bit ;)

@Doc.AElstein, Afraid I don’t follow your first paragraph! Though FWIW I’d only ever use app.Run if it really is the only viable solution.

Generally I don’t declare things as Public unless there’s a reason to, which means in normal modules explicitly declaring as Private because by default declarations are Public. But “If Possible” you say, well it’s often ‘possible’ to keep things Private even if easier and more efficient as Public. As is most likely the case for the OP.

In classes use of properties v. public variables (only to read/write and do nothing else) can be a matter of style, both work similarly. But in some scenarios Property is definitely preferred. FWIW, even if simpler (less code to write) and equally efficient, if distributing or making the class public externally I’d only use Properties.

@YasserKhalil, There’s a lot to be said for ‘if it works it works and if it ain’t broke don’t fix it’ and that may well apply here for you. However, even if it’s customary for you to keep everything in worksheet modules private, in general the advice from Rory and myself earlier (starting with 'Consider..') really is worth following.

Peter T

User avatar
rory
5StarLounger
Posts: 760
Joined: 24 Jan 2010, 15:56

Re: Run worksheet module from standard module

Post by rory »

@Peter,
To be honest, my eyes had glazed over a bit by then, so I overlooked that part... :)
Regards,
Rory