• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1686
  • Last Modified:

Use VBA to fill Combo Box and extract data from an array based on selection

I realize this may be a multi-part question, but here goes!

FIrst off, I am working with VBA 6.5 for reference.

I am trying to utilize an array to first populate a combobox based on a .csv file column, then extract the rest of the data in the the .csv file row to pass into an argument that adds a display pen to a trend chart. I have the code functioning without using the .csv file and array, and am unfamiliar with working with arrays, so please assume a complete lack of knowledge on how to accomplish this.

This is how it works currently:

Private Sub Display_Load()

Pen1_ComboBox.Clear
Pen1_ComboBox.AddItem "Chaffee Hill Suction Pressure", 0
Pen1_ComboBox.AddItem "Chaffee Hill Discharge Pressure", 1
Pen1_ComboBox.AddItem "Recycle Tower Level", 2
Pen1_ComboBox.AddItem "Well 1 Discharge Pressure", 3
Pen1_ComboBox.AddItem "Well 1 Discharge Flow", 4
Pen1_ComboBox.AddItem "Well 1 Level", 5

End Sub

Function Add_Trend_Pens() As Boolean
Dim Pen1Description As String
Dim Pen1Tag As String


On Error GoTo ErrorHandler

If Pen1_ComboBox.Value = "Chaffee Hill Suction Pressure" Then
    Pen1Description = "Chaffee Hill Suction Pressure"
    Pen1Tag = "[CR]CH_Suction_Pressure"
ElseIf Pen1_ComboBox.Value = "Chaffee Hill Discharge Pressure" Then
    Pen1Description = "Chaffee Hill Discharge Pressure"
    Pen1Tag = "[CR]CH_Discharge_Pressure"
ElseIf Pen1_ComboBox.Value = "Recycle Tower Level" Then
    Pen1Description = "Recycle Tower Level"
    Pen1Tag = "[CR]RT_Tank_Level"
ElseIf Pen1_ComboBox.Value = "Well 1 Discharge Pressure" Then
    Pen1Description = "Well 1 Discharge Pressure"
    Pen1Tag = "[CR]W1_Discharge_Pressure"
ElseIf Pen1_ComboBox.Value = "Well 1 Discharge Flow" Then
    Pen1Description = "Well 1 Discharge Flow"
    Pen1Tag = "[CR]W1_Discharge_Flow"
ElseIf Pen1_ComboBox.Value = "Well 1 Level" Then
    Pen1Description = "Well 1 Level"
    Pen1Tag = "[CR]W1_Level"
End If


If Pen1_ComboBox.Value > " " Then
    Example_Trend.Pens.Add "{" & Pen1Tag & "}", Pen1Description, "eu", 0, 100
End If

Add_Trend_Pens = True

Exit Function

ErrorHandler:

    Add_Trend_Pens = False
    MsgBox "Error adding trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function

The .csv file has the following columns:
Index, Area, Description, TagName, EngineeringUnits, Minimum, Maximum

There is first, a selection of area, then the combobox should populate with the descriptions from the .csv file based on that area selected. When a selection has been made in the combobox, the rest of the data would populate the argument for the Trend.Pens.Add function.

Any assistance would be greatly appreciated! And plese feel free to ask for clarification on anything I missed,

Thanks!
Bryan

0
BryanKipp
Asked:
BryanKipp
  • 8
  • 7
1 Solution
 
aikimarkCommented:
Please upload the CSV file.

It looks like the Pen1Tag value is the first letter of the first two words of the Pen1Description with a modified version of the rest of the string (spaces replaced by underscores).  Is that the rule?  Please correct me if I'm wrong with this assertion.
0
 
BryanKippAuthor Commented:
I have the first part of this functioning now, and have provided the code that populates the combobox. The rest of the need is described below.

I have a .CSV file that has 5 columns. I want to populate an argument that will fill in data from the file based on a combobox selection. I am populating the combobox with the following code:

Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer

 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings

While Not EOF(1)
   strContent = Split(strHeadings, ";")
   Pen1_ComboBox.AddItem strContent(0)
   Line Input #1, strHeadings
Wend


Close #1

End Sub

Open in new window


Once the operator selects an item from the combobox, I need to fill the following argument with the rest of the data:

If ComboBox.Value > " " Then
Trend.Pens.Add "{" & TagName& "}", Description, "Engineering Units", Min, Max
End IF

The file data is, in the following format:

Description (As String); TagName (As String); Engineering Units (As String); Min (As Single); Max (As Single)

Annapolis Hill Discharge Pressure;AH_DISCHARGE_PRESSURE;PSI;0;500
Annapolis Hill Suction Pressure;AH_SUCTION_PRESSURE;PSI;0;500
Annapolis Hill Tank Level;AH_TANK_LEVEL;FT;-25;-5
Backwash Flow Control Valve Position;BKWASH_FCV_POS_IN;Percent;0;100
Backwash Flow;BKWASH_FLOW;GPM;0;250
Backwash Flow Set Point;BKWASH_FLOW_SP;GPM;0;250
Chaffee Hill Discharge Pressure;CH_DISCHARGE_PRESSURE;PSI;0;500
Chaffee Hill Suction Pressure;CH_SUCTION_PRESSURE;PSI;0;500
Chaffee Hill Tank Level;CH_TANK_LEVEL;FT;-25;-5
Clear Well Level 1;CLEARWELL1_LEVEL;FT;0;10
Clear Well Level 2;CLEARWELL2_LEVEL;FT;0;10
Clear Well Level 3;CLEARWELL3_LEVEL;FT;0;10

Open in new window

There is also a possibility that the option to filter the data in the combobox by area (a new column in the .csv fle) will be desired, but for now it would be wonderful to be able to just get the data based on the selection in the combobox.

0
 
aikimarkCommented:
For this, I would recommend using a Dictionary object.

Option Explicit
Dim dicTags As Object


Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")
 
 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings

While Not EOF(1)
   strContent = Split(strHeadings, ";", 1)
   Pen1_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Open in new window


Once your Display_AnimationStart() has finished, you can easily get the tag data from the name data.
Dim strParsed() As String

If ComboBox.Listindex <> -1 Then
  strParsed = Split(dicTags(ComboBox.Text), ";")

  Trend.Pens.Add "{" & strParsed(0) & "}", ComboBox.Text, strParsed(1), strParsed(2) , strParsed(3)

End If

Open in new window


Depending on the type of combobox, you might protect yourself against users typing text into the combobox.  You might need to use
ComboBox.List(ComboBox.ListIndex) to get the text of the selected items accurately.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BryanKippAuthor Commented:
I am trying to run this code and get an error saying
 "VBA Code has been halted, Reason: Run-time error - Subscript out of range"

Choosing Debug takes me to line 17 above:

dicTags.Add strContent(0), strContent(1)
 Not sure where to go from here.
0
 
aikimarkCommented:
Was this on the first line read?

What is the content of the strHeadings variable when the error occurs?
0
 
aikimarkCommented:
oops.  The third Split() parameter should be a 2 instead of a 1
Option Explicit
Dim dicTags As Object


Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")
 
 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Open in new window

0
 
BryanKippAuthor Commented:
This error appears as soon as I run the code. The ComboBox should be clear until I select an item. I have included all the code here.

Option Explicit
Dim dicTags As Object

Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings

While Not EOF(1)
   strContent = Split(strHeadings, ";")
   Pen1_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Function Add_Trend_Pens() As Boolean
Dim strParsed() As String
Dim Pen1Min As Single
Dim Pen1Max As Single

On Error GoTo ErrorHandler

If Pen1_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen1_ComboBox.Value), ";")
  Pen1Min = strParsed(3)
  Pen1Max = strParsed(4)
  Example_Trend.Pens.Add "{" & strParsed(1) & "}", Pen1_ComboBox.Value, strParsed(2), Pen1Min, Pen1Max

End If


Add_Trend_Pens = True

Exit Function

ErrorHandler:

    Add_Trend_Pens = False
    MsgBox "Error adding trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function
Function Remove_Trend_Pens() As Boolean

On Error GoTo ErrorHandler

While Example_Trend.Pens.Count > 0
    Example_Trend.Pens.Remove (1)
Wend

Remove_Trend_Pens = True

Exit Function

ErrorHandler:

    Remove_Trend_Pens = False
    MsgBox "Error removing trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function

Private Sub RefreshTrend_Button_Released()

On Error GoTo ErrorHandler

' Uncomment out the if statement below to remove the pens

If Remove_Trend_Pens() = False Then
    Exit Sub
End If

' Uncomment out the if statement below to add the selected pens

If Add_Trend_Pens() = False Then
    Exit Sub
End If

'Add the Trend command to make the trend refresh the chart with the new pens
'Here is a hint.  Type example_trend. below and then select the method from this list

Example_Trend.RefreshChart

Exit Sub

ErrorHandler:
    
    MsgBox "Error refreshing trend.  Error:  " & Error, vbOKOnly, "Trend Error"

End Sub

Open in new window


Thanks so much for your assistance!
0
 
BryanKippAuthor Commented:
I did change the original example you provided thinking the error was being generated due to the strParsed(3) and strParsed(4) values needing to be "Single" instead of "String"

If I understand how this works, using the first row of the CSV file should return the following:

strParsed(0) Annapolis Hill Discharge Pressure
strParsed(1) AH_DISCHARGE_PRESSURE
strParsed(2) PSI
strParsed(3) 0
strParsed(4) 500

Is this assumption correct?
0
 
aikimarkCommented:
what happened to the 2 as the third parameter in the Split() function?
0
 
BryanKippAuthor Commented:
With the change to make the third split() parameter a 2, I still get a message box with:

"Error adding trend pens. Error: Subscript out of range"
0
 
aikimarkCommented:
after adding the 2 parameter to the Split() function, add CSng() around those values that need to be single.
Example
  Example_Trend.Pens.Add "{" & strParsed(1) & "}", Pen1_ComboBox.Value, strParsed(2), CSng(strParsed(3)), CSng(strParsed(4))


Or you can use the two single variables to convert the string data into single.
0
 
aikimarkCommented:
please post your code.
0
 
BryanKippAuthor Commented:
This generates the same error

Option Explicit
Dim dicTags As Object

Private Sub Display_AnimationStart()

 Dim strHeadings, strContent() As String
 Dim iCtr As Integer
 Set dicTags = CreateObject("Scripting.Dictionary")


 Open "C:\RSLogix 5000\Projects\ArrayTags.csv" For Input As #1
  Line Input #1, strHeadings
 
Pen1_ComboBox.Clear

While Not EOF(1)
   strContent = Split(strHeadings, ";", 2)
   Pen1_ComboBox.AddItem strContent(0)
   dicTags.Add strContent(0), strContent(1)
   Line Input #1, strHeadings
Wend

Close #1

End Sub

Function Add_Trend_Pens() As Boolean
Dim strParsed() As String
Dim Pen1Min As Single
Dim Pen1Max As Single

On Error GoTo ErrorHandler

If Pen1_ComboBox.ListIndex <> -1 Then
  strParsed = Split(dicTags(Pen1_ComboBox.Value), ";")
  Example_Trend.Pens.Add "{" & strParsed(1) & "}", Pen1_ComboBox.Value, strParsed(2), CSng(strParsed(3)), CSng(strParsed(4))

End If


Add_Trend_Pens = True

Exit Function

ErrorHandler:

    Add_Trend_Pens = False
    MsgBox "Error adding trend pens.  Error:  " & Error, vbOKOnly, "Trend Error"

End Function
0
 
aikimarkCommented:
look back at my earlier code: http:#35725642

your index values need to be in the 0-3 range, not 1-4 range.
0
 
BryanKippAuthor Commented:
Absolutely excellent!

Thank you for your patience and assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now