repeating code under each worksheet (1 Viewer)

[Guus2005]

Hi there,

I have a workbook with 12 worksheets.
The code for every worksheet is the same. A subset:

Private Sub Worksheet_Activate()
      gstrPrevSheetName = ActiveSheet.Name
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
      TabChange Target
End Sub

In Access you can use WithEvents to group recurring pieces of code.
Is this also possible in Excel and if so, how?

Thanks!

 

[Isaac]

I believe WithEvents in a class module works with all VBA

 

[neuroman9999]

In Access you can use WithEvents to group recurring pieces of code.
Is this also possible in Excel and if so, how?

the question I would ask is, why are you interested in WithEvents? According to the following, it doesn't sound like it would serve the purpose you want to use it for:

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/modifiers/withevents

so why exactly do you want to "'group"" this code you have behind each worksheet? I would assume, to get the same thing done, you could simply write a universal function and call it whenever, with args.

 

[Isaac]

Because OP doesn't want to have to write code in each worksheet's class module. They want the code to fire on the same event for any worksheet created--present or future. That's a purpose of WithEvents. The link you posted has nothing to do with VBA, it's for vb.Net

 

[neuroman9999]

The link you posted has nothing to do with VBA, it's for vb.Net

i understand that, but that is completely irrelevant. Literally most things that are posted my MS regarding VB.NET can be emulated verbatim in VBA and VB 2.0/3/4/5/6. They only post VB.NET because VB6 and VBA are so outdated they don't care anymore and/or have moved on years ago, like they should have. Everything posted by MS is in the form of .NET ""anything"". Because that will be their baby for a long time to come.

 

[CJ_London]

wonder when they will get around to using VB.NET instead of VBA in all their office products

 

[Isaac]

wonder when they will get around to using VB.NET instead of VBA in all their office products

That's an interesting question.
I don't think so, though, because I think Microsoft wants to keep plenty of products in the realm of things that appeal to and can be grasped by developers at the "casual" level. I would reference the now popular "no-code" product mantra, but of course, it's not to that extent...but similar market appeal. I think they will remain satisfied with the separation of more casual dev's using just VBA and if a COM class library is desired, create using .net

 

[neuroman9999]

I would reference the now popular "no-code" product mantra, but of course, it's not to that extent...but similar market appeal.

more than likely, the ""no code"" concept will never apply to office products. the people selling these types of products are selling enterprise software, if I'm not mistaken. not to mention the fact that VBA and every object in it was developed specifically for office apps.

wonder when they will get around to using VB.NET instead of VBA in all their office products

more than likely, never. .NET is meant for big business. office products have other purposes. I spose it's always a possibility though.

 

[The_Doc_Man]

Not physically impossible to switch to VB.NET, but VBA is semi-compiled (to Pcode, a pseudo-machine) and emulated whereas VB.NET is interpreted. There is a reasonably big difference between those two methods. Having said that, the code interpreter for VB.NET exists and the "raw text" code exists in the modules. So I wouldn't say "impossible" to get them together. I WOULD say "expensive undertaking" because the semi-complied version can link more easily to events in Access and to API entry points. DID say "More easily." DID NOT SAY "cannot." Anyway, the expense of the project o establish that linkage might kill it before it starts. Because as we know, business of any kind needs an expected return on their investment and this one would be questionable.

 

[arnelgp]

most of the Event you mentioned can be also found in the parent (ThisWorkbook).

 

[neuroman9999]

Because as we know, business of any kind needs an expected return on their investment and this one would be questionable.

no argument here! always gotta consider that money!