Linking subs

[gregv]

Hi,
I am teaching myself excel VBA and at present, everything I write, is done in one sub. There are certain sections of my code that I need to repeat several times in the sub. Is there a way I can put those lines in another sub (or area) which I could link into (and out of) each time I need that calculation carried out?
Sorry if it's an obvious or daft question! - Thanks

 

[VoG]

You can call them like

Sub MyBigSub()
Call Macro1
Call Macro2
End Sub

 

[Yard]

If you want the code to DO something, write a Sub (called e.g. MySub) and from your main sub, call it using

Call MySub

If you want to perform a calculation, consider writing a Function Sub.

Remember that if you want to share variables between the different subs you'll have to declare them publicly outside of the subs themselves.

Otherwise, I'd recommend the Mr Excel VBA Macros book!

 

[gregv]

Thanks for your suggestions. Having "gone" into the second sub and carried out the code there, how do I link back to the first sub at the place where I left it?

 

[VoG]

You don't have to. When the called sub hits its End Sub it returns to the calling sub.

 

[gregv]

Thanks very much. That's great and very helpful.
I note Yard's comments but (sorry I am stupid) what is the difference between "doing something" and performing a calculation? Is it simply, if I am doing "arithmetic" I use function, otherwise always use a sub?

 

[VoG]

If you are doing arithmetic you could use either but a function would be better practice and probably easier to code e.g.

Function Cube(a As Double)
Cube = a ^ 3
End Function

Then in your sub

y = Cube(x)

If you want to do things like changing formatting, adding sheets and so on then you have to use a sub.

 

[gregv]

Thanks - dont really understand what a function is as only ever written subs but it sounds that if I stick with subs I should be OK.

 

[phantom1975]

Functions aren't that much different than subs. Here's an example of how to use either to perform the same thing:

Sub Area_Using_Sub()

Dim Width As Double, Length As Double


    Width = 4
    
    Length = 5
    
    MsgBox Width * Length


End Sub

Sub Area_Using_Function()


    MsgBox Area(4, 5)


End Sub

Public Function Area(Width As Double, Length As Double) As Double


    Area = Width * Length


End Function

The cool thing about a function (also called a UDF standing for User-Defined Function) is that you can use it like a formula within your cells. You have to declare the function as Public in order to do so.

Try this in one of your cells:

=AREA(6,7)

 

[gregv]

Phantom,
I think I understand what you are saying, but I need to walk before I run and it's getting a bit complex for a newbie!