473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Looping the Array to be used in data point in Chart

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
4 2430
mcupito
294 256MB
Have you looked into Structures?
Mar 14 '14 #2
cten
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
mcupito
294 256MB
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
cten
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

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

Similar topics

2
by: ensnare | last post by:
Hi all, I'm using a database session handler and am looking to loop through data residing in the sessions table to make a 'Users online' array. I've found that using urldecode on the data...
14
by: Susan Rice | last post by:
I want to create a readonly array of data, then a readonly array of a structure. This is data I access but I want it protected against accidental change. The following is my test code. #include...
4
by: daqmaneng | last post by:
does anyone have any suggestions for writing a two dimensional array of data to a file. Currently, I have an array of 5 columns, with 1000 elements per array. I know that I can use a for next...
0
by: Charls | last post by:
Hi, How do I get the value of a Data Point from a column in a barchart. I have tried: ActiveChart.SeriesCollection(2).Select ActiveChart.SeriesCollection(2).Points(1).Select ...
15
by: Madhur | last post by:
Hi All, I would like you help me in creating an array of data types. I am interested in look at the the data type which looks like this Array...
5
by: grant | last post by:
I'm trying to use a scatter chart to plot level reading for a pump station level sensor. The sensor takes a reading every 4 seconds, and there are 23,000 reading per chart There appears to be...
7
by: ramana | last post by:
I'm wondering if someone could point me to the flaw in the following code that uses the while(!FP.eof()) condition to read the input data. This condition is reading the last data point of the file...
5
by: billelev | last post by:
I have a large array of data (1000 x 40 x 3) that I am inserting into a database table. It is incredibly slow, and so I was wondering if there is a quicker way of inserting array data into a table....
1
by: bandy | last post by:
HI there, Need Help... This is data file's result: { "elements": , "dot-style": { "type": "dot", "tip": "#val#", "dot-size": 5, "colour": "#DFC329" }, "width": 2, "colour": "0x9933CC", "tip":...
2
by: tmarynell | last post by:
I have created an SPC chart in Access. I need specific details of each data point wihtin the line graph to display when you hover over or double-click on the specific data point. It should include...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.