Use the variable of another Sub VBA

Question

Use the variable of another Sub VBA

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I have a problem wanting to use the variable "name" of another subroutine. I want it to be able to edit a graph. In this case, the first subroutine generates the graph and gives it the name. In the second subroutine, I want to edit it but I can not call the variable "name" which is where I have what the graphic is called.

Public **nombre** As String

Sub GRAFICA2()

Dim Grafica As String
    nombre = InputBox("Escribe el nombre de la grafica:")       

Dim Rango As Range
    Set Rango = Selection

    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Rango
    ActiveChart.Parent.Name = nombre

    ActiveSheet.ChartObjects(nombre).Activate
    ActiveChart.ChartTitle.Select
    Selection.Delete

Dim cht As Chart
    Set cht = ActiveSheet.ChartObjects(nombre).Chart

    cht.ChartArea.Format.TextFrame2.TextRange.Font.Size = 10
    cht.ChartArea.Format.TextFrame2.TextRange.Font.Name = "Arial"
    cht.ChartArea.Font.Color = RGB(0, 0, 0)

End Sub

Sub grafica2A()

Dim Rango2 As Range
    Set Rango2 = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

    ActiveSheet.ChartObjects(nombre).Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.FullSeriesCollection(1).XValues = Rango2

End Sub

vba

asked by Jose Carlos Castro 30.06.2016 в 19:28

source

3 answers

Define different Initial View Controller Swift how to make an http call when sending form in angular

score 1 · Answer 1

If you need to use a variable outside of a method, why not define it outside of this?

Example:

Dim Numero As Integer

Sub AumentarN()
    Numero = Numero + 1
    MsgBox Numero
End Sub

Sub ProcPrincipal()
    Numero = 5
    MsgBox Numero
    AumentarN ' Llamamos a la subrutina.
End Sub

Result of ProcPrincipal() :

5
6

What applied to your case would be:

Dim NombreGrafica As String
Sub GRAFICA2()
    ' ...
    NombreGrafica = InputBox("Escribe el nombre de la grafica:")
    ' ...
End Sub

Sub grafica2A()
    Dim Rango2 As Range
    Set Rango2 = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

    ActiveSheet.ChartObjects(NombreGrafica).Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.FullSeriesCollection(1).XValues = Rango2
End Sub

Or, you can do what has been recommended in the response from Javier Solis

score 0 · Answer 2

Each subroutine only has access to the variables that were declared inside it, if you want to get a name you should pass it as a parameter to your function

Sub grafica2A(nombre)

   Dim Rango2 As Range Set Rango2 = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
   ActiveSheet.ChartObjects(nombre).Activate
   ActiveChart.Axes(xlValue).MajorGridlines.Select
   ActiveChart.FullSeriesCollection(1).XValues = Rango2
End Sub

score 0 · Answer 3

Add another subroutine, which serves to do 3 things: 1) Ask for the name of the graph. 2) run graph2 with the name as parameter. 3) run graph2A with the name as parameter.

Sub ejecutar()
nombre=inputbox("Escriba un supernombre sorprendente de supergráfica: ")
grafica2(nombre)
grafica2A(nombre)
End Sub

Sub grafica2(Optional ByVal unNombre as String = "Grafiquita2")
.......
End Sub

Sub grafica2A(Optional ByVal unNombre as String = "Grafiquita2A")
.......
End Sub