Basic question on modules

[mark hansen]

I have a basic question regarding modules. At a concept level, what are they? Are they inherently different code from the code I put under a button?

I cut my teeth with computers programming dBase II and we had programs call other programs when we needed the code to be used more than once. It saved lots of repetitive programming.

Is this what modules are for?

I have done lots of research on code that can e-mail and all of it, is in modules. I do most of my stuff in buttons to kick off the code. In one of my spreadsheets, I have the same long code put in two spots, under two buttons - for ease of use. I didn't want my users to scroll over to get to the single reset button. Could I have put the same code in a module and reference that code by the name of the module on two buttons?

I guess the basic question is code in a module inherently different from code used by a button?

Sorry for such a basic question concept question.

Mark

 

[hiker95]

mark hansen,

What is a Visual Basic Module?
http://www.emagenit.com/VBA Folder/what_is_a_vba_module.htm

 

[xenou]

Could I have put the same code in a module and reference that code by the name of the module on two buttons?

Yes.
Most simply:

In Module1:

Sub Foo()
'blah blah blah
End Sub

In Button1:

Sub Button1_OnClick()
    Call Foo
End Sub

In Button2:

Sub Button2_OnClick()
    Call Foo
End Sub

I personally don't put a lot of code in buttons on worksheets as there's odd issues with scope that arise from time to time (not a problem but something to be aware of). Generally, I just call module code from button code.

 

[xenou]

Could I have put the same code in a module and reference that code by the name of the module on two buttons?

Yes.
Most simply:

In Module1:

Sub Foo()
'blah blah blah
End Sub

In Button1:

Sub Button1_OnClick()
    Call Foo
End Sub

In Button2:

Sub Button2_OnClick()
    Call Foo
End Sub

I personally don't put a lot of code in buttons on worksheets as there's odd issues with scope that arise from time to time (not a problem but something to be aware of). Code is code. But the container of the code is what differs. Generally, I just call module code from button code, unless its very simple button code or its quite specific to that button and that button alone, rather than code that works with worksheets or other Excel objects. I try not to put code in specific containers (Worksheets, ThisWorkbook, Buttons, Userforms) unless it really relates directly to that container and that container only.

 

[xenou]

But I am repeating myself ...

 

[mark hansen]

Thanks xenou,
That clears it up quite nicely. With the help of this board, I have been able to help out my users with some pretty impressive "magic". I always thought there may be a better way and I'm reminded what one of my first mentors told me "You never know what you don't know ---so, eventhough you got it, keep looking for a better way".

I've beed reading the website that hiker95 mentioned, and others like it. I can see I have much more foundation work to do on my VBA skills.

Thanks again,
Mark