How to use the name of a cell of the Excel spreadsheet in VBA, not to indicate row and column?

Asked

Viewed 5,064 times

3

How to use the name of a cell of the Excel spreadsheet in VBA, not to indicate row and column?

For example, I named "Client Name" to cell B20, when I use this name in VBA to put in label ( label1.caption = Client Name ), it accepts and does not give error. However, it does not take the cell value.

As I am inserting columns and rows, the cell changes, but the VBA evidently does not hit the rows and columns of the range command.

How do I use the cell name directly?

excel vba variable-declaration

  • I don’t quite understand what you want, you want to take the column you just included so you can change some property of it?

  • I didn’t really explain. I named a cell in Excel and I want to use the name defined in Excel as a variable in VBA, not to need to change row and column every change in the worksheet, this is it

  • 1

    I edited the answer, this would be my best suggestion for you. I do not believe it is possible to do what you want in VBA

  • jbueno, I thought it was possible to do something like this, but indirectly you can do it, even though it is more work. Grateful

2 answers

3


I believe that’s what you want: You take a Range, because the name is not a primary key. Then you run all the returns from it. 

Sub Test()

    Dim r As Range
    Dim cell As Range

    Set r = Range("Something")

    If r.Cells.Count > 1 Then
        For Each cell In r.Cells
            MsgBox cell.Value
        Next cell
    Else
        MsgBox r.Value
    End If
End Sub

UPDATING

You can’t do this in VB or C#, be Python, for example, you could.

I suggest creating a list, something like that:

Dim dados As New Dictionary(Of String, String)()
dados.Add("nome", "30/nome")
dados.Add("endereco", "30/endereco")
dados.Add("data_cnsc", "8/dt_nasc")
dados.Add("municipio", "20/municipio")
MessageBox.Show(dados("municipio"))'acessamos os valores por índices

  • I didn’t really explain.

  • I want to take the value of a cell in excel and use as a variable in VBA, so if I include lines, using the cell name the VBA would use the same cell value, I would not need to tinker with the program

  • Thanks Ricardo, I’ll try, I used the range("cell name"). value as indicated earlier and it worked too. Thanks

  • You are welcome to check in if you have answered. Good job.

  • It’s the first time I walked in, clicked on the up arrow, that’s it?

  • And check box below the arrows to say q is answer, please.

  • OK, I’ve done it, thank you

  • I voted in the answer because it is nice and correct. : ) But, it would be important to also put an image indicating how/where the name of range is defined (field in the upper left, before where the formula is displayed/edited), only to facilitate the life of a future reader who does not know so much about the subject.

Show 3 more comments

1

Good morning, by the date of the question you’ve obviously already found a solution. But I will put here the solution I found to help more people.

To access the attributes of a range that can go out of place I assigned this named range to an object and now use the object in the references in the code:

Sub AcessandoRangeMovel()
    Dim endConsult1 As Object
    Dim endConsult2 As Object

    Set endConsult1 = Range("NomeConsultor1")
    Set endConsult2 = Range("NomeConsultor2")

    MsgBox endConsult1.Value
    MsgBox endConsult2.Address
End Sub

Browser other questions tagged excel vba variable-declaration

You are not signed in. Login or sign up in order to post.