Working with Arrays in Classes (Excel VBA)

Question

I'm working on a part of a program that does the following:

1) Determines there is an "X" in a certain cell and whether the header matches the user input

2) Adds the contents of the header to an element in the class if this is true

For example, if there is an "X" under the "clutch" header for a row, then "clutch" is added to, in this case, pos.clutch.

Here is what accomplishes this (edit: this is in a Do While loop, hence the R + 1, sorry if that was not clarified originally):

If sh.Cells(R + 1, Clutch) = "X" And _
   sh.Cells(1, Clutch).Value = CStr(cboPart.Value) Then
     pos.Clutch = sh.Cells(1, Clutch)

Now, the problem is that I have a long list of ElseIf statements for each element (there are 6 in total.) I would like to convert this to a For Loop and after some research I figured an array that I would include in the class module would be the best way, as I could just loop through each value in the array.

I would like to know how to create an array made of class elements in the class module, and if it would be possible to set the value for each of the class elements in a For loop. Additionally, if there is a better solution I would like to know more about it.

You don't really convert an If Else to a Loop. You might consider using Select Case instead of a series of nested If/Else statements. — David Zemens
– David Zemens, Commented Jun 11, 2013 at 16:10
I encountered a problem because select case short circuits. How do you recommend avoiding this? — Al.Sal
– Al.Sal, Commented Jun 11, 2013 at 17:15
I have no idea what you mean by "Select Case short circuits". I can't read your mind -- and neither can anyone else here -- so, please update your Question to include what code you have tried, and indicate which line raises an error. If no error is raised, describe how the output is not consistent with what you want/expect. — David Zemens
– David Zemens, Commented Jun 11, 2013 at 17:33
I managed to use a completely different method to solve my problem but thank you for taking the time to help. I'll be more clear next time I have a question. — Al.Sal
– Al.Sal, Commented Jun 12, 2013 at 12:20

user2140173 · Accepted Answer · 2013-06-11 16:02:22Z

I am not 100% sure what you mean. But based on the first part of your post here is some sample code with comments

Option Explicit

Sub tryme()

    Dim inp As String
    inp = InputBox("Whats the header:")
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim rng As Range
    Dim i As Long, j As Long

    For i = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
        If StrComp(inp, CStr(ws.Cells(1, i).Value), 1) = 0 Then
            For j = 2 To ws.Cells(Rows.Count, i).End(xlUp).Row
                Set rng = ws.Cells(j, i)
                    If StrComp(CStr(rng.Text), "X", 1) = 0 Then
                        ' youve just found a match
                        ' so if your class constructor takes some params
                        ' for example
                        ' ...MyClass(header as String, row as Long)
                        ' then you can call it
                        ' ...dim newMyClassObj(Cstr(ws.Cells(1, i).Text), rng.row)
                    End If
                Set rng = Nothing
            Next j
        End If
    Next i

End Sub

Collectives™ on Stack Overflow

Working with Arrays in Classes (Excel VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related