?
Solved

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

Posted on 2011-05-09
17
Medium Priority
?
1,639 Views
Last Modified: 2012-05-11
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
Comment
Question by:BryanKipp
  • 8
  • 7
15 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 35725526
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
 

Author Comment

by:BryanKipp
ID: 35725542
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35725642
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:BryanKipp
ID: 35728254
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35728315
Was this on the first line read?

What is the content of the strHeadings variable when the error occurs?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35728367
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
 

Author Comment

by:BryanKipp
ID: 35728379
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
 

Author Comment

by:BryanKipp
ID: 35728426
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35728434
what happened to the 2 as the third parameter in the Split() function?
0
 

Author Comment

by:BryanKipp
ID: 35728477
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35728496
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35728504
please post your code.
0
 

Author Comment

by:BryanKipp
ID: 35728559
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
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 35728574
look back at my earlier code: http:#35725642

your index values need to be in the 0-3 range, not 1-4 range.
0
 

Author Closing Comment

by:BryanKipp
ID: 35728635
Absolutely excellent!

Thank you for your patience and assistance!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Screencast - Getting to Know the Pipeline

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question