Declare variables

[Dan15]

Here is the basic issue:

first subroutine defines value of variable.
second subroutine puts that value in a specific location.

example:

sub first
x = 2
second
end sub

sub second
ActiveCell.Select
ActiveCell.Next.Next.Next.Next.Next.Next.Next.Next.Next.Next.Next.Next = x
end sub

How do I write this correctly, so sub second knows to use the value from sub first. Or is having a sub inside a sub a problem?

Thanks-

-Dan

 

[RobBroekhuis]

You can't have a sub inside a sub in VBA (I'm pretty sure). Declare them independently, with a module-level variable declared at the top of the module, before any subs:

dim x as integer
sub first
end sub

sub second
end sub
Rob

 

[Dan15]

Rob-
you can have a sub within a sub: in this example, each sub colors a specific cell, then the final sub runs the three subs.



Sub red()
Range("B7&quot.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Sub
Sub green()
Range("B8&quot.Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End Sub
Sub blue()
Range("B9&quot.Select
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Range("C11&quot.Select
End Sub

Sub total()
red
green
blue
End Sub


The question is can I have functions inside a sub, use a variable defined outside the sub.

 

[SkipVought]

here's another take on your question.

You write a function that returns a value

Function MyNumber() as Integer
   MyNumber = 5
End Function

Sub Test()
   Cells(5, 6).Value = MyNumber
End Sub

puts the value, 5 into row 5 column 6

Typically, the function evaluates based on some passed parameter or external condition.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[Dan15]

Skip, that works, but how do I now run the function from a drop down menu?

 

[SkipVought]

The Function represents a value. You can use it from the Combobox_Click event

Function MyNumber() as Integer
   MyNumber = 5
End Function

Sub Combobox1_Click()
   Cells(5, 6).Value = MyNumber
End Sub

Of course, Cells(5, 6).Value = 5 does the same thing, unless your function is a bit more complex. Skip,
SkipAndMary1017@mindspring.com

 

[Dan15]

The situation is that I have many custom menu items, each time the user selects a menu item, it sets the value of the varible. The second sub is the same for every menu item, using the value from the first sub. I am looking to avoid writing the same second sub hunders of times for every menu item. Instead, the first sub calls the second sub, and the second sub is written only once. This way I only have to change the second sub once when needed

 

[rmikesmith]

Dan,

You can do as Rob suggested, declaring x as a global (module level) variable, in which case your example construct should work fine; i.e. Sub First sets the value of x then calls Sub Second. Sounds like you have many "First" procedures and want each to call the same "Second" sub. That should work. Alternatively, you can declare your second sub to accept an argument. Example:

Sub Second( byVal X as Integer )
  ActiveCell.Offset(0,12).Value = X
End Sub

Call it this way:

Private Sub MenuItem_Click()
  Second 2
End Sub

Note: Declare X other than Integer, if appropriate; may want to use Variant as this would be most flexible.


Rob can certainly speak for himself, but I'm guessing he meant you cannot declare one SUB inside another, as you can in some languages.


Regards,
Mike

 

[Dan15]

Thank you for your help fellas-

Mike, that solution works well-

-Dan