arraysexcelvbawindowsoffice365

How to assign cell values to an array with named arguments

What I am trying to figure out is how to pass cell values to an array with arguments, without making it a hardcoded array of values.

i.e. the array should look like this

ValuesRow = Array("ARG1", "ARG2", "ARG2", "ARG3", "ARG4",... "ARGi")

and then assign specific cell values to each ARGn inside this array without explicitly writing it as i.e.

ARG1 = Worksheet.Cells(1, 1)
ARG2 = Worksheet.Cells(1, 3)
ARG3 = Worksheet.Cells(3, 5)
'after which i could for example do
MsgBox ARG1 & Chr(13) & ARG2 & ....
...etc

Because I already do that when I am gathering values from different procedures, but there it is not an issue (as well as the only way around).

So I'd rather make an array

ValuesRow = Array("ARG1", "ARG2", "ARG2", "ARG3", "ARG4",... "ARGi") 'probably without the "
    i = 1
    For i = LBound(ValuesRow) To UBound(ValuesRow)
        ValuesRow(i) = .Cells(1, i).Value  'magic
    Next i
'after which i could for example do
MsgBox ARG1
'and it would give me a message box with the Value.
'instead of explicitly writing
MsgBox Array(1)
'As well as be able to
ActiveSheet.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Smth smth Argument 1 = " & ARG1
'and it would show me the value saved in ARG1 after text

I need this because:

  1. I have a lot of values to read;
  2. it is more comprehensive;
  3. there will be a point in time when I will have to access this array between procedures (so the array itself will be defined among Global Parameters), therefore in a separate procedure that reads e.g. Argument 6, and there is no way I will recall what this value actually is.

The values are neatly structured in rows in a separate TEMPFILE.xlsx, which I can access any time. i.e. ARG1 to ARG8 would be .Cells(row, 1) to .Cells(row, 8). So this is less of an issue. So any dynamic arrays and whatnot is not a requirement. As well as the order in which each value is laying in a row.

Secondly referring to the values in array via Array(1), Array(2), etc is not very much an option, because this will become unreadable later down the line. I could obviously make Arg1 = Array(1) statements and then refer to Arg1 ever after, but this is just extra lines of code and not much sense (except for convenience of writing the code).

Solution

  • It seems you need just to refer values by names for convenience. You can make this with Collection or Dictionary.

    enter image description here

    Option Explicit

Sub TextConception()
  Dim ValuesRow, References, myData As New Collection, i As Long
  ValuesRow = Array("ARG1", "ARG2", "ARG3")
  References = Array(Cells(1, 1).Value, Cells(1, 3).Value, Cells(3, 5).Value)
  For i = LBound(ValuesRow) To UBound(ValuesRow)
    myData.Add References(i), ValuesRow(i)
  Next
  Debug.Print myData("ARG1"), myData("ARG2"), myData("ARG3")
End Sub

    Another option is to use Classes.

    Credits to @Chronocidal: You can name the key cells and then refer them in the code by their names:

    enter image description here

    Debug.Print Thisworkbook.Names("Mass").RefersToRange