0

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).

Improve this question
2
  • 2
    Why can't you just make ARG an array and then use ARG(1) rather than ARG1? Seems like you're overcomplicating it. Commented Oct 29, 2024 at 9:58
  • 1
    @Rory i thought of this as well, but in the end the actual argument names within that array are all different, like: Array(Mass, Temperature, Pressure, AverageValue, MaxValue, smthelse). I wrote ARG1, ...2, ...3, etc just as an example. It would have been easier to have just ARG as array, not gonna lie. The person below answered about Collection and Dictionary, i'll try that one, i simply didn't know about those things. Commented Oct 29, 2024 at 12:44

2 Answers 2

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

hm, thanks, i didn't know about Collection and Dictionary. Will try it out, thanks a lot!
@Chim'sArchives If your values are always going to be coming from Worksheet References, then you can use the special built-in Workbook.Names collection instead of creating a new one from scratch — which has the added {advantage/disadvantage} (delete as appropriate) that you can use them in Workbook Formulas
@Chronocidal ok, that's a plenty of information to get into, thanks. I'll look into it. I read about the Workbook.Names, but didn't know its the application (not to mention in such a way). btw, any chance that ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:= _ "=Sheet1!R1C1" can loop through rows? Why, because i will have a lot of rows with the same column order.
@Chim'sArchives Apologies, I'm not quite sure on what your question is asking there. Do you mean something like For i=2 to 14 Step 3: ActiveWorkbook.Names.Add Name:="myName_" & i, RefersToR1C1:= "=Sheet1!R" & i & "C1": Next i? If it's more involved, you might need to raise a new question, rather than adding to this one.
@T.M. Sure. Their advantage is that they are not reset when the project is reset.
|
2

A simple class-based approach can work here if what you really want is to refer to "named" values:

Define a class [Insert >> Class Module] and name it (eg) RowData:

Option Explicit

'These are all Variants, but you can (should?) use specific datatypes...
Public Mass
Public Temperature
Public Pressure
Public AverageValue
Public MaxValue
Public smthelse

Here's how you can load instances of that class from your worksheet data (in a regular code module):

Sub Tester()

    Dim rw As Range, obj As RowData
    
    For Each rw In ActiveSheet.Range("A2:F5").Rows 'for example
        Set obj = GetRowData(rw)
        'output some properties to Immediate pane
        Debug.Print "Row " & rw.Row, obj.Mass, obj.Temperature
        'You can add `obj` to a collection for example
        '  if you want to collect all of the data in object form
    Next rw

End Sub

'Return an instance of class `RowData` from range `rw`
Function GetRowData(rw As Range) As RowData
    Dim o As New RowData
    Set o = New RowData
    'populate poperties from cvells in `rw`
    o.AverageValue = rw.Cells(1).Value
    o.Mass = rw.Cells(2).Value
    o.MaxValue = rw.Cells(3).Value
    o.Pressure = rw.Cells(4).Value
    o.smthelse = rw.Cells(5).Value
    o.Temperature = rw.Cells(6).Value
    Set GetRowData = o
End Function
Improve this answer

4 Comments

Set o = New RowData is extra if o is already assigned with the new object in Dim o As New RowData.
It would be more followed OOA to encapsulate GetRowData into RowData.
@rotabor IMO that would overly-couple RowData to the structure of the source data. It's fine to use simple objects where you might otherwise use a Type/Struct (which I typically avoid in VBA because they come with too many restructions). If the data exists on a fixed sheet I'd be more inclined to make GetRowData a member of that worksheet. In the end we don't know enough about the OP's use case to figure out a good structure for the whole thing.
Encapsulation to a sheet is the way as well. At the same time, the standalone function looks good to such small app.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.