Home / Questions / 417815
Accepted
Lorthas
Asked: 2022-01-01 04:50:46 +0800 CST 2022-01-01 04:50:46 +0800 CST

How to loop through a named array constant in Excel VBA?

  • 772

This is a pretty simplified example of what I'm doing regarding the number of cells involved. In Excel I have a name "Cells1" which contains:

=(B1;B3;B4)

I want to loop through this array constant with VBA. I have tried the following:

Sub Vaciar_nombres()
    Dim R1() As Range

    REM En la siguiente linea me salta error.
    R1 = hoja_de_pruebas.Names("Celdas1").RefersTo

    Debug.Print (VarType(R1))
End Sub

I haven't worked with array constants let alone using them with VBA. How can I take an array constant to later go through the ranges that are in that array using VBA? (I already know the loops)

excel

2 Answers

  1. Look at the example. With this code you go through the previously assigned "cells" range. Also if you need it, you could assign the values ​​of the range to an array (if you need it tell me and I'll tell you how)

    Dim c As Range

For Each c In Range("celdas")
    MsgBox "busca el valor stringabuscar en " & c.Value & " en el rango celdas"
    If c.Value = "stringabuscar" Then
      MsgBox "stringabuscar encontrado en " & c.Address
    End If
Next c
    • 2
  2. Best Answer

    Let's see, your code is not right and you are also mixing different things, names with arrays and with ranges, and you have to make changes.

    A name, to simplify it, refers to a series of cells (in your case, B1, B3 and B4). But it is an object of typeName

    A cell or set of cells is an object of type Range. It has other methods and properties totally different fromName

    And finally, there are arrays, which in VBA can be defined in various ways. Normally, what is usually done is to take a whole range of cells to an array, because that way the execution time is less. This very common process always creates two-dimensional arrays , meaning that to call an array value, you always have to specify a row number and a column number. It doesn't have properties and methods like object Rangeor object Name.

    Simplifying a lot, a fundamental difference between the object Rangeand an array, which at first glance may seem to do the same thing (a set of cells with values), there is a fundamental difference: arrays store the value inside the cell, not the reference to the cell itself, so if the value of the cell changes, the old value is still stored in the array (you have to force the update of the value in the array). However, the object Range, each time you call it, will return whatever value is in the cell at that exact moment, even if it has changed throughout your code. But at runtime it takes more time.

    100% PERSONAL OPINION: If you work with many values ​​(and when I say many values, I mean working with hundreds of cells at once), you should consider using arrays. If you work with few cells, you may find the object easierRange

    About matrices, you might be interested in taking a look at this answer, which explains it very simplified.

    https://es.stackoverflow.com/a/186012/74355

    Regarding your code, it is not clear to me what you are trying to do. I understand that you want to take the values ​​of cells B1,B3 and B4 to an array. And you have that reference stored in an object Name.

    I anticipate that you cannot take a set of non-adjacent cells to a matrix in a single step . That is, Cell B3 and B4 are adjacent, but B1 is not because you skipped B2. And because of that, you can't just lump everything into a two-dimensional array. You have to take the values ​​1 by 1

    Next, you have to be clear about whether your name is defined as Book scope or Sheet scope. In my example, it is defined as Book scope:

    enter image description here

    Note that I have also defined it with absolute references (the $ symbol).

    About absolute references, take a good look at this link, because it is important to understand it:

    Toggle between relative, absolute, and mixed references

    The property RefersToreturns or sets the formula that the name should refer to, in A1-style notation and the language of the macro, beginning with an equals sign. Read and write string

    Name property. RefersTo (Excel)

    Also, the cells I'm referencing have these values:

    enter image description here

    My code grabs the range of the name Celdas1(which is defined as Book scope). Counts the total number of cells, resizes the one- dimensional array , and carries the values ​​back to the array. And then I walk through the array and return each and every value.

    In case you don't know them, take a good look at what native VBA functions you can use with arrays:

    split function

    ubound function

    LBound function

    erase statement

    Redim Instruction

    And this is my code:

    Dim rng As Range
Dim MiMatriz() As Variant
Dim i As Long

ReDim MiMatriz(1 To Range("Celdas1").Count) 'redimensiono matriz

i = 1
For Each rng In Range("Celdas1")
    MiMatriz(i) = rng.Value
    i = i + 1
Next rng

'ahora devuelvo todos los valores
For i = 1 To UBound(MiMatriz) Step 1
    Debug.Print MiMatriz(i)
Next i

Erase MiMatriz

    And when I run it, the immediate window returns these values:

    enter image description here

    Note that the number 20 is not returned, because that value was in cell B2, which is not included in the name Celdas1

    I hope this can help you and adapt it a bit, because as I said, I'm not sure what you want to do, but try and tell us.

    • 2