Select Array element based on a variable

Question

I have the following multi dimensional array type made up as follows :

Type Wedge
    C407                As Long
    C417                As Long
    C507                As Long
    C516                As Long
    C607                As Long
    C617                As Long
    C707                As Long
    C716                As Long
    C807                As Long
    C817                As Long
    C907                As Long
End Type

It goes on further for another 20 or so elements, I then define the array as such

Dim myWedge() As Wedge
ReDim myWedge(99, 4)

This works OK, and is structured so that I can break down the time stamps into 15 minute segments through out the day. so far so good

The problem I have is that the text string I search such as :

--- YTD05 C707 DC5 64:Right wedge not in OK

I pull out the C707 using the line below, again this is OK,

myWedgeFault = (Mid(myFaultDesc, 11, 4))   ' gives me C707

but then want to increment the array element by 1 using something like :

myWedge(myftime, ArrayCol).C707 = myWedge(myftime, ArrayCol).C707 + 1

BUT I want to set the C707 part by using a variable such as myWedgeFault so that I don't have to hard code all the different options in

Something like :

myWedge(myftime, ArrayCol).myWedgefault = myWedge(myftime, ArrayCol).myWedgefault + 1

Is this possible

Thanks

Tomalak · Accepted Answer · 2017-05-16 16:11:26Z

1

If you want to keep the current data structure, there is little you can do in VBA, except to write a few helper functions like this:

Function GetWedge(w As Wedge, key As String) As Long
  Select Case key
    Case "C407": GetWedge = w.C407
    Case "C417": GetWedge = w.C417
    Case "C507": GetWedge = w.C507
    Case "C516": GetWedge = w.C516
    Case "C607": GetWedge = w.C607
    Case "C617": GetWedge = w.C617
    Case "C707": GetWedge = w.C707
    Case "C716": GetWedge = w.C716
    Case "C807": GetWedge = w.C807
    Case "C817": GetWedge = w.C817
    Case "C907": GetWedge = w.C907
    Case Else: Err.Raise vbObjectError + 1, "GetWedge", "Unknown key: & key"
  End Select
End Function

Sub SetWedge(w As Wedge, key As String, value As Long)
  Select Case key
    Case "C407": w.C407 = value
    Case "C417": w.C417 = value
    Case "C507": w.C507 = value
    Case "C516": w.C516 = value
    Case "C607": w.C607 = value
    Case "C617": w.C617 = value
    Case "C707": w.C707 = value
    Case "C716": w.C716 = value
    Case "C807": w.C807 = value
    Case "C817": w.C817 = value
    Case "C907": w.C907 = value
    Case Else: Err.Raise vbObjectError + 1, "SetWedge", "Unknown key: & key"
  End Select
End Sub

Sub IncrementWedge(w As Wedge, key As String)
  SetWedge w, key, GetWedge(w, key) + 1
End Sub

and use them like this:

IncrementWedge myWedge(myftime, ArrayCol), "C707"

edited May 16, 2017 at 16:11

answered May 16, 2017 at 15:48

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fabby Over a year ago

Hi Tom, thanks for the replay, still working out how this works, but looking at the "Key" part is this then hard coded ?

Tomalak Over a year ago

No, you can calculate the key from your input, that's the point of it all. :) Use your myWedgeFault variable.

Fabby Over a year ago

Hi Tom, thanks doesn't seem to like the IncrementWedge Sub comes up with a syntax error SetWedge(w, key, GetWedge(w, key) + 1) do I need to add "Call" in front ?

Tomalak Over a year ago

No. The outer parentheses need to go away, see updated answer. (I think adding Call instead works as well - personally I never use Call)

Tomalak Over a year ago

Use F9 to set a breakpoint on the line that calls the IncrementWedge Sub and start your code. As soon as the breakpoint is reached, the code stops running and you are in "break mode". The yellow line is the current line. You can step through the code line-wise (F8 jumps to the next line when the code is in break mode). Enable the "Locals Window" from the View menu beforehand. Then you can see what it does.

Collectives™ on Stack Overflow

Select Array element based on a variable

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related