By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

Looping the Array to be used in data point in Chart

P: 4
I'm fairly new to VB.net, and I want to create a program that can display 3 different charts in one page. What I want is to open the Excel workbook with that have the data required. The program then read the data in the given range and store it as an array. After that, it will loop the array so that it can display each data point for the chart. I can get it to display the chart title. Other than that is nothing, just white blank screen. Where did I mess up? It didn't give me any error, so I don't know why nothing is shown. Here's the code I have so far:

Expand|Select|Wrap|Line Numbers
  1. Imports System.Reflection
  2. Imports Excel = Microsoft.Office.Interop.Excel
  3. 'Add reference Assemblies, Framework, System.Windows.Forms.DataVisualization
  4. Imports System.Windows.Forms.DataVisualization.Charting
  5.  
  6.  
  7. Public Class Form1
  8.  
  9.     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  10.  
  11.         Dim excelApp As Excel.Application
  12.         Dim excelWB As Excel.Workbook
  13.         Dim excelWS As Excel.Worksheet
  14.         Dim FNameRng As Excel.Range
  15.         Dim AveRng As Excel.Range
  16.         Dim AveCLRng As Excel.Range
  17.         Dim AveUCLRng As Excel.Range
  18.         Dim FNameArry As System.Array
  19.         Dim AveArry As System.Array
  20.         Dim AveCLArry As System.Array
  21.         Dim AveUCLArry As System.Array
  22.  
  23.         excelApp = CreateObject("Excel.Application")
  24.         excelApp.Visible = False
  25.         'Open the Workbook
  26.         excelWB = excelApp.Workbooks.Open("C:\Users\Joesph\Documents\Charts\Control Limit\18x17 - 10 mil stop.xlsx")
  27.         excelWS = excelApp.Sheets("18x17 - 10 mil stop")
  28.  
  29.         'Set the Range for File Name
  30.         FNameRng = excelWS.Range("A2", excelWS.Range("A2").End(Excel.XlDirection.xlDown))
  31.         'Set the Range for Average Data
  32.         AveRng = excelWS.Range("B2", excelWS.Range("B2").End(Excel.XlDirection.xlDown))
  33.         AveCLRng = excelWS.Range("H2", excelWS.Range("H2").End(Excel.XlDirection.xlDown))
  34.         AveUCLRng = excelWS.Range("I2", excelWS.Range("I2").End(Excel.XlDirection.xlDown))
  35.  
  36.         'Read in the values of a range of cells
  37.         FNameArry = CType(FNameRng.Value, System.Array)
  38.         AveArry = CType(AveRng.Value, System.Array)
  39.         AveCLArry = CType(AveCLRng.Value, System.Array)
  40.         AveUCLArry = CType(AveUCLRng.Value, System.Array)
  41.  
  42.         Me.CenterToScreen()
  43.         Me.WindowState = FormWindowState.Maximized
  44.  
  45.         Chart1.Titles.Add("Title1")
  46.         Chart1.Titles(0).Text = "Average"
  47.         Chart1.Titles(0).Font = New Font("Garamond", 24, FontStyle.Bold)
  48.  
  49.         'Looping the Array
  50.         For FNameEle As Integer = 0 To FNameArry.Length - 1
  51.             Chart1.Series("Series1").XValueMember = "FNameEle"
  52.         Next
  53.         For AveEle As Integer = 0 To AveArry.Length - 1
  54.             Chart1.Series("Series1").YValueMembers = "AveEle"
  55.         Next
  56.         For AveCLEle As Integer = 0 To AveCLArry.Length - 1
  57.             Chart1.Series("Series1").YValueMembers = "AveEle"
  58.         Next
  59.         For AveUCLEle As Integer = 0 To AveUCLArry.Length - 1
  60.             Chart1.Series("Series1").YValueMembers = "AveUCLEle"
  61.         Next
  62.  
  63.         'excelWB.Close(SaveChanges:=False)
  64.     End Sub
  65. End Class
  66.  
Mar 10 '14 #1
Share this Question
Share on Google+
4 Replies


100+
P: 294
Have you looked into Structures?
Mar 14 '14 #2

P: 4
I got my answer already. My problem now is to get the last two loop to show only data with value. I can't get that to work. The points is either come all out or none at all.
Here's my code:
Expand|Select|Wrap|Line Numbers
  1. 'Set the Range
  2. A_GTRng = excelWS.Range("I2", excelWS.Range("I2").End(Excel.XlDirection.xlDown))
  3.  
  4. 'Read in the values of a range of cells
  5. A_GTArry = CType(A_GTRng.Value, System.Array)
  6.  
  7. 'Looping through the A_GTArry
  8.         For x As Integer = 1 To A_GTArry.GetUpperBound(0)
  9.             For y As Integer = 1 To A_GTArry.GetUpperBound(1)
  10.                 Dim A_GT As Object = A_GTArry(x, y)
  11.                 For iPt = 1 To Chart1.Series("A_GT").Points.Count
  12.                     If IsNumeric(A_GT) Then
  13.                         Chart1.Series("A_GT").Points.Add(A_GT)
  14.                     Else
  15.                         Chart1.Series("A_GT").Points(iPt).MarkerStyle = MarkerStyle.None
  16.                     End If
  17.                 Next
  18.             Next y
  19.         Next x
  20.  
In my workbook, there are some data that display as "#N/A" and the other as value (ex. 0.032). The code above display nothing. How can I make it to just display the one with value? Thanks!
Mar 14 '14 #3

100+
P: 294
Are the #N/A fields actually containing data? It sounds like you would need to convert them to "" if #N/A in your worksheet is equivalent to Null. Excel to VB.NET handle null very differently.
Mar 17 '14 #4

P: 4
In the worksheet, I have formula =IF($S2>=$T2,$S2,NA()). It should be equivalent to null, but I don't know how to code it.
Mar 17 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.