Using a variable's value from one sub into another in VBA

Question

Please, I am trying to use a variable's value from one sub into another, but unfortunately I did not get it. Follow the code below:

Sub get_value()
    Dim area2 As Range

    Set area2 = Range(Cells(8, 2), Cells(linha, 2))
    f = area2.Count
    s = x - f
End Sub

Sub get_value_s()
    Worksheets(2).Activate

    Range("A3").Select
    Selection.End(xlDown).Select
    linha = Selection.Row
    linha2 = linha + s

    Rows(linha2).Select
    Selection.Insert Shift:=xlDown
End Sub

P.S. I have set s as integer globally. (Public s As Integer).

Many thanks!

score 1 · Accepted Answer · answered Oct 13 '14 at 04:31

It's hard to know what the error is without more code since you have several other global variables and we can't see how they are set. My guess is that your not setting your variables before your calling them. For example, are you sure get_value() is called before get_value_s() and that x & linha are the correct values at the time you call it?

I'd suggest not using Globals unless you really have too. I can get your code down to only one global (x) and this is because I have no info on where this comes from.

Try this instead:

Public x As Integer

Function get_value(linha As Integer, wks As Worksheet)
    Dim area2 As Range
    Set area2 = wks.Range(Cells(8, 2), Cells(linha, 2))

    Dim f As Long
    f = area2.Count
    get_value = x - f
End Function

Sub get_value_s()
    Dim wks As Worksheet
    Set wks = Worksheets(2)

    Dim linha As Integer, linha2 As Integer

    linha = wks.Range("A3").End(xlDown).Row
    linha2 = linha + get_value(linha, wks)

    wks.Rows(linha2).Insert Shift:=xlDown
End Sub

And make sure X is set before you call get_value_s!

Note that I refactored your code to avoid Active & Select see [this interesting read](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to learn more about why. — Automate This, Oct 13 '14 at 04:34

score 0 · Answer 2 · answered Oct 13 '14 at 04:31

Like what Portland Runner suggested, you can do it like this.

Function get_value()
    Dim area2 As Range

    Set area2 = Range(Cells(8, 2), Cells(linha, 2))
    f = area2.Count
    get_value = x - f
End Function

Sub get_value_s()
    Worksheets(2).Activate

    Range("A3").Select
    Selection.End(xlDown).Select
    linha = Selection.Row
    linha2 = linha + get_value()

    Rows(linha2).Select
    Selection.Insert Shift:=xlDown
End Sub

Using a variable's value from one sub into another in VBA

2 Answers2