Where do I put the code, but why do I put the code there

[Marcellinus]

Hi,

I've read the post "Where do I put the code". However I'm more interested in why I put it there. At the moment I use Classes, Modules, Forms and Thisworkbook. I've used sheetcode once before but I've stopped using sheetcode after I lost precious code because some stupid user deleted the sheet and lost all my code.

So when do I need or should I say when must I use sheetcode? Or is it just for using sheet events?

Marcel

 

[Luke M]

Hi, and welcome to the forum!

Must be put there? Probably only for sheet events, or macros that you want to be transferred with the sheet should you copy it to another workbook or something. Usually, it's just more of a good practice to keep your main code in regular modules so it's easier to find and edit (also prevents accidents like the one you expereinced). E.g., I'll have a sheet event call a macro that's in a regular module.

There's also behaviour differences. Macros that are stored in sheets can't be called just by there name from a different location. For instance, if you have this in a sheet module:

Sub test()
MsgBox "hi"
End Sub

and then in a different location tried to use:

Call test

The code would error out. On the other hand, if you have put "test" in a regular module, then the call would succeed.

The other difference, and this is more a positive, is if you need to use the Me object. In a sheet module, the Me object refers to the sheet the code is stored in, without having to know the name of the sheet (which is sometimes helpful).

 

[Marcellinus]

Hi Luke,

Thanks for your explanation. The me. construction is very handy and used in the same way as in Forms. Calling functions in sheets errors out as you said but if I put the sheet name just as the me in front of the function or sub you can call it from everywhere. I tested it and as long as you does not change the sheet object name but only it sheet propert name everythings keep working.

Sheet1

Function ab() As String
    ab = "Wow"
End Function

Module1

Sub b()
    MsgBox Sheet1.ab
End Sub

My question was generated by my need to keep my projects structured and to put code at the right place to get the best functionallity and speed. I'm trying to find a way to keep my projects structrured and maintanable/scalable but when they grow they tend to get more and more unstructured.

I'll look around here before asking.

 

[Luke M]

You are correct, I should have been more detailed. You can put the full reference in front of any sub/function to help the VB trace it. But, I'm a lazy programmer, and why should I be bothered to have to remember where I stored my subs/functions!

I think the summary is that there's not a hard rule saying that you have to do things a certain way, there's just ways that are easier to maintain. Another example might be that I like to use separate modules for Functions/Subs in my projects. I don't have to, but since I can easily add another module (and change it's name to be "MyFunctions"), it helps me when 6 months later the client calls saying ZZZ doesn't work, and I can easily figure out what I was thinking.