Procedure too large/ Option Explicit

[yxz152830]

Gurus,
If my module is option explicit, how do I break the procedure into smaller procedures in the most efficient way?
my guess is that I can claim the variables in global or I can claim them again in each procedure?

 

[yxz152830]

Nothing - it doesn't matter what you call the variable.

Hello just one more question; How do I pass a variable down to the other sub and then send the variable out from the other sub back to main sub?

sub main()
dim x as double
x= 100
abc x
range("a1") = x
end sub

sub abc(x as double)
(some calculations on x)
end sub

 

[RoryA]

Exactly like that. Variables are passed ByRef by default, so any changes you make to the variable passed as an argument are reflected in the original variable too.

 

[yxz152830]

Exactly like that. Variables are passed ByRef by default, so any changes you make to the variable passed as an argument are reflected in the original variable too.

on more question pls:
if the nested sub has a variable that needs to be passed down to the main sub, how do I do it?
like below, a is calculated in the nested sub.

sub main()
dim x as double
x= 100
abc x
range("a1") = a
end sub

sub abc(x as double)
(some calculations on x)
a = x^2
end sub

 

[RoryA]

Simplest way is to change the called sub to a function that returns the value you want:

sub main()
dim x as double
dim a as double
x= 100
a = abc(x)
range("a1") = a
end sub

VBA Code:
function abc(x as double) as double
(some calculations on x)
abc = x^2
end function

 

[yxz152830]

Simplest way is to change the called sub to a function that returns the value you want:

sub main()
dim x as double
dim a as double
x= 100
a = abc(x)
range("a1") = a
end sub

VBA Code:
function abc(x as double) as double
(some calculations on x)
abc = x^2
end function

is there another way? I need to keep the called sub for other procedures. Is it better that I just declare a in the main sub so I can pass it to the called sub so that it can automatically return to the main sub?

 

[RoryA]

You should still make it a function. Other routines can still call it the same way they already do. It would tend to suggest that the called sub may be doing too many things if it should sometimes return a value and sometimes not.