X-Y Scatter plot using VBA

[Abhi86]

Hello friends, I am a beginner in VBA coding. Code which i have attempted consist of two parts (a) Data extraction and (b) Plotting of xy-scatter graph from extracted data. My objective of the coding is listed below:-

• Data Extraction :- In this part of code i am trying to extract data from text files having .RS extension kept in a folder, into an excel. I have tried to automate the folder pick up which contain the text file, along with creation of excel file which will be having the extracted data. Based on the number of text files in the folder the cells in excel file will get arranged automatically and data will be extracted. (With this portion of code i am having no problem and i am able to perform the desired function).

• Plotting of extracted data into xy-scatter graph :- In this portion of code my objective is to automate the plotting of extracted data on a chart (XY-scattersmooth). Say i am having 5 text file in my folder then my program will extract the data from each of the file and import/paste it into excel, so i have now 5 series to be plotted (This number 5 (Text file) is variable it can go from say 1 to 1000). So to paste all the series on same chart i have done looping. I am facing problem in assigning a variable range to .Values and .XValues as my series ranges are not fixed i can't use the "A2:A245" kind of syntax for defining range. Portion of code where i am facing problem is pasted below;

'**************************************************************
' Chart plotting
sh1.Cells(4, d).Select
Set ch = ActiveWorkbook.Charts.Add
Set ch = ch.Location(Where:=xlLocationAsObject, Name:="NRS-Data")
With ch
.ChartType = xlXYScatterSmooth
End With
a = 4
b = 1
For z = 1 To count
k = sh1.Cells(Rows.count, 1).End(xlUp).Row
If k = 1048576 Then
k = 1
End If
'MsgBox (k)
'**************************************************************
' Problem in this section of code
' I want to assign the range to xaxis and yaxis
' I want to have a variable range as data for my series are in different columns and the number of rows is also a variable
' How to correct it
Set xaxis = Range(Cells(a, b), Cells(k, b)) ' I want to set xaxis as range which is variable in nature
Set yaxis = Range(Cells(a, b + 1), Cells(k, b + 1)) ' I want to set yaxis as range which is variable in nature
Set s = ch.SeriesCollection.NewSeries
With s
.Values = yaxis ' Facing error at this line, which may be because xaxis is not getting set with range
.XValues = xaxis
.Name = sh1.Cells(2, b).Value
' set other series properties
End With
b = b + 2
'**************************************************************
Next z


At first instance i had two separate codes for performing the above written objective perfectly, and its working where as when i am trying to combine the two codes to make a single code for performing both the tasks at a single click its showing absurd graph view.

I am attaching both the "combined code" file and the seprated "code file for plotting the data" for your refernce.


I would appreciate the help of members to solve this issue.

Thank you

[SamT]

At first instance i had two separate codes for performing the above written objective perfectly, and its working where as when i am trying to combine the two codes to make a single code for performing both the tasks at a single click

Why? Combining two procedures just makes it harder... As you see.

Leave the Procedures separate and just "call" the Plotting Procedure at the end of the Data Extraction Procedure.

Sub Extracting()
'Do a biunch of stuff
'
'
'Call Graphing
Graphing
End Sub

Sub Graphing()
'Do some different stuff'
'
'
'End Sub

[Abhi86]

I do not know how to writing function and call them. I wanted to combine the codes so that the variables which i am invoking in first code can be used in the second code also.

[SamT]

so that the variables which i am invoking in first code can be used in the second code also.

There are two ways:
The Module level declarations way

'All these Variables are Declared before any subs.

'Var1 and Var2 are available to all subs in the Workbook
Dim Var1 As Long
Dim Var2 As Long

'Var3 is available to any sub in this code Module
Private Var3 As Long

Var4 is available to all Subs in all Open Workbooks
Public Var4 As Long

Sub Extraction
Var1 = 1
Var2 = 2

'Call Graphing
Graphing
End Sub

Sub Graphing
Dim Z
Z = X + Y
End Sub

The Secure Way:

Sub Extraction
Dim Var1 As Long
Dim Var2 As Long

Var1 = 1
Var2 = 2

'Call Graphing
Graphing X, Y         'X and Y must be in the same order as A and B in Sub Graphing input Parameters
End Sub

Sub Graphing(A As Long, B as Long)
Dim Z As Long
Z = A + B
End Sub