Passing arguments to a graphing function

[RawlinsCross]

Good day. I have a userform with approximately 50 labels each containing a number which is the latest number in a column of numbers (i.e. I have 50 columns and say 365 rows). I have a code that calls another userform which has a graph when you click on any of the 50 labels (see code below).

So the long of it is - I don't want to make 50 separate userforms for each of the labels. I'd like to have just one userform containing the code (see below again) that accepts arguments for things like axis title, MinimumScale, NumberFormat, .Range - things like that. How is this done?

Private Sub UserForm_Initialize()

Dim MyChart As Chart
Dim ChartData As Range
Dim ChartName As String
Application.ScreenUpdating = False
Worksheets("Dashboard").Range("H4").Value = ActiveWindow.Zoom
ActiveWindow.Zoom = 85
 
Set ChartData = Worksheets("Main Element Profiles").Range("Y7:Y37")
        

ActiveSheet.Range("B2").Select
Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

With MyChart
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = ChartName
    .SeriesCollection(1).Values = ChartData
    .SeriesCollection(1).XValues = Worksheets("Main Element Profiles").Range("B7:B37")
    .Legend.Select
        Selection.Delete
    .Axes(xlCategory).Select
        Selection.TickLabels.NumberFormat = "m/d/yyyy"
        Selection.TickLabels.NumberFormat = "[$-409]mmm-dd;@"
    .Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0.00"
        Selection.TickLabels.NumberFormat = "#,##0.0,"
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).MinimumScale = 0
    .Axes(xlValue).AxisTitle.Text = "Na Concentration (g/L)"
End With
   

Dim ImageName As String
ImageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.jpeg"
MyChart.Export filename:=ImageName
ActiveSheet.ChartObjects(1).Delete
ActiveWindow.Zoom = Worksheets("Dashboard").Range("H4").Value
Application.ScreenUpdating = True
ASSAY221FLASH2NA.Image1.Picture = LoadPicture(ImageName)
 
End Sub

 

[Jerry Sullivan]

Hi RawlinsCross,

You have the right idea about passing arguments instead of having separate code for each scenario.

I'd suggest you create a relationship between the Labels and the corresponding column of data to be charted. One way, would be to have the control named "Label01" correspond to column 1 of your dataset (with "Label02" mapped to column 2).

A more adaptable approach would be have each Label Caption property correspond to a Field Header above your dataset. This makes it easier to rearrange your UserForm or dataset without needing to rename the Labels.

If you'll explain what you want that relationship to be, I'll suggest some code.

 

[RawlinsCross]

Hey Jerry, thanks for the reply. You're absolutely right about having each Label Caption property Corresponding to a Field Header above your dataset. I've already done that although I didn't include it in my original post as I felt it was a separate issue relative to passing arguments to a function.
I use the "Find" function and then the "Split function" to determine the column letter designation. Then I use the "Sum" function to sum up the values between to dates via DTPicker.

'Setting up DT Picker
Set sh = Sheets("EBal")
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker1), sh.[a7], xlValues)    ' first date
rn1 = r.Row                                                             ' where the date is
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker2), sh.[a7], xlValues)    ' second date
Set rng1 = Worksheets("EBal").Range("A2:AZZ2").Find("ACFEED_AC_Ni", , xlValues, xlWhole)
ColumnLetter1 = Split(Cells(2, rng1.Column).Address, "$")(1)
Me.ACFeed_AC.Caption = Format(WorksheetFunction.Sum(Worksheets("EBal").Range(ColumnLetter1 & r.Row & ":" & ColumnLetter1 & rn1)), "#,##0")

So this works fine. So I what I want to do is click on a label that brings up the day-to-day numbers between the two dates. But I need to pass the following information to this userform:

axis titles
axis ranges
source data
Number Format

John

 

[RawlinsCross]

I found a site that discusses variable assignment to called functions. But the complication here (at least as I understand it) is that you cannot place variables within the initialization of a userform.

So I got around it by declaring public variables.

Public Date1 As Date
Public Date2 As Date
Public AxisTitle As String
Public Index As String

Then I can assign these variables when I click on 1 of the 50 labels on my main userform.

Private Sub ACFeed_AC_Click()  'this is a label name
Date1 = Me.DTPicker1.Value  'first date value from the main userform
Date2 = Me.DTPicker2.Value   'second date value from the main userform
AxisTitle = "Ni Tonnage (t)"  'vertical axis title
Index = "ACFEED_AC_Ni"        'The reference index title above the column I wish to graph
A1_EBAL1.Show vbModeless  'launch the second userform that will hold the graph
End Sub

Then when the second (graph) userform initializes, it can grab values from the Global variables.

Private Sub UserForm_Initialize()
Dim rng1 As Range
Dim ColumnLetter1 As String
Dim EBalData As Range
Me.DTPicker1 = Date1
Me.DTPicker2 = Date2
Me.DTPicker1.MinDate = Date - 365
Me.DTPicker1.MaxDate = Date
Me.DTPicker2.MinDate = Date - 365
Me.DTPicker2.MaxDate = Date
'Setting up DT Picker
Set sh = Sheets("EBal")
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker1), sh.[a7], xlValues)    ' first date
rn1 = r.Row                                                             ' where the date is
Set r = sh.Range("a:a").Find(CDate(Me.DTPicker2), sh.[a7], xlValues)    ' second date

Set rng1 = Worksheets("EBal").Range("A2:AZZ2").Find(Index, , xlValues, xlWhole)
ColumnLetter1 = Split(Cells(2, rng1.Column).Address, "$")(1)

Application.ScreenUpdating = False
Worksheets("Dashboard").Range("H4").Value = ActiveWindow.Zoom
ActiveWindow.Zoom = 85
Me.TextBox1.Value = "Auto"
Me.TextBox2.Value = "Auto"
Set ChartData = Worksheets("EBal").Range(ColumnLetter1 & r.Row & ":" & ColumnLetter1 & rn1)
        

ActiveSheet.Range("B2").Select
Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatter).Chart

With MyChart
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = ChartName
    .SeriesCollection(1).Values = ChartData
    .SeriesCollection(1).XValues = Worksheets("EBal").Range("B" & rn1 & ":B" & r.Row)
    .Legend.Select
        Selection.Delete
    .Axes(xlCategory).Select
        Selection.TickLabels.NumberFormat = "m/d/yyyy"
        Selection.TickLabels.NumberFormat = "[$-409]mmm-dd;@"
    .Axes(xlValue).Select
        Selection.TickLabels.NumberFormat = "#,##0.00"
        Selection.TickLabels.NumberFormat = "#,##0"
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).AxisTitle.Text = AxisTitle
End With
If IsNumeric(Me.TextBox1) And IsNumeric(Me.TextBox2) Then
    ActiveChart.Axes(xlValue).MinimumScale = Me.TextBox1.Value * 100
    ActiveChart.Axes(xlValue).MaximumScale = Me.TextBox2.Value * 100
End If
   
Dim ImageName As String
ImageName = Application.DefaultFilePath & Application.PathSeparator & "TempChart.jpeg"
MyChart.Export filename:=ImageName
ActiveSheet.ChartObjects(1).Delete
ActiveWindow.Zoom = Worksheets("Dashboard").Range("H4").Value
Application.ScreenUpdating = True
A1_EBAL1.Image1.Picture = LoadPicture(ImageName)
 
End Sub

 

[Jerry Sullivan]

It wasn't clear earlier that the chart was being displayed in a second userform. I assumed it was an image in the same userform. Is that the way you prefer it, or would you rather have the chart in the same userform as the parameter entries?

 

[RawlinsCross]

Well, the userform is like a road map with figures with 50 or so labels describing points along the road map so there's not a lot of space left over. So yes, the second userform is a necessity I'm afraid.

I do have a follow up question if I may. Say I want to click on one of the 50 labels, a graph userform comes up and I can see the daily data. I've gotten that far and all is good. But say I want to click on another label and have two of graph userforms together to compare. The way I have it set up now, there's only one graph userform and if I click on another label it'll keep reusing that one userform.

So this whole exercise was because I didn't want to have 50 separate userforms, one for each label. But what if I have say 5 graph userforms, name graph1, graph2, graph3, graph4, graph5 (for example). The thought is that the user would not want to compare more than 5 sets of data. Is there a code you envision that would count how many userforms are open (the main roadmap userform being the first ofcourse) and would cycle around the 5 graph userforms?

Hopefully that made some sense...

John

 

[Jerry Sullivan]

Hi John,

Yes, you can create multiple instances of your second Userform class.

The other changes I'd suggest to your existing code, would be to use Properties instead of Global variables, and to use a Class for your 50 labels. The latter change will allow you to have one event procedure instead of 50 separate Label_Click events.

I'll post some example code later tonight.

 

[Jerry Sullivan]

John,

We'll need some way to collect the 50 Label controls that will be in the Class. Is there currently something that they have in common that distinguishes them from any other Labels on your UserForm? It could be a pattern in their name or their location inside a Frame? If not, a simple method would be to set the Tag property of those controls to a common value.

 

[RawlinsCross]

Okay, so I might throw in a curveball at this time. At the top of the main "roadmap" userform (the one that has the 50 labels) there are 11 command buttons which represent 11 different elements of the periodic table Anyhow, each time you click on one of the elements, I've coded it so that its backcolor turns vbgreen (and the other command buttons normal) and all 50 labels spit out their values for that element. Each of the 50 labels then has one of 11 elements for a total of 50 x 11 possible graphs - now you may understand my panic as previous to this I was importing individual userforms PER graph. *yikes*

Private Sub Co_Click()
Me.Ni.BackColor = &H8000000F
Me.Co.BackColor = vbGreen
Me.Cu.BackColor = &H8000000F
Me.Fe.BackColor = &H8000000F
Me.Na.BackColor = &H8000000F
Me.Mg.BackColor = &H8000000F
Me.Mn.BackColor = &H8000000F
Me.Ca.BackColor = &H8000000F
Me.Si.BackColor = &H8000000F
Me.B.BackColor = &H8000000F
Me.Cl.BackColor = &H8000000F

Doing it this way, when you click on a label, the code first sees what element command button is vbgreen (i.e. selected)

Private Sub ACFeed_AC_Click()  'ACFeed_AC is the label name
If Me.Ni.BackColor = vbGreen Then
Date1 = Me.DTPicker1.Value  'first date value from the main userform
Date2 = Me.DTPicker2.Value   'second date value from the main userform
AxisTitle = "Ni Tonnage (t)"  'vertical axis title
Index = "ACFEED_AC_Ni"        'The reference index title above the column I wish to graph
Caption1 = "Ni Tonnage through Autoclave"

ElseIf Me.Co.BackColor = vbGreen Then
Date1 = Me.DTPicker1.Value  'first date value from the main userform
Date2 = Me.DTPicker2.Value   'second date value from the main userform
AxisTitle = "Co Tonnage (t)"  'vertical axis title
Index = "ACFEED_AC_Co"        'The reference index title above the column I wish to graph
Caption1 = "Co Tonnage through Autoclave"
End If
A1_EBAL1.Show vbModeless  'launch the second userform that will hold the graph
End Sub

Not sure what you mean by collecting label controls but in the data set 550 columns and 365 rows (one year of data), I have identifiers. So the first label is called ACFeed_AC and in the data tab I have on top of each row I have "ACFeed_AC_Ni ACFeed_AC_Co ACFeed_AC_Fe ACFeed_AC_Ca..... etc". 11 identifiers off the label identifier, one for each element of the periodic table.

Hope this is making sense.

 

[Jerry Sullivan]

I think that I'm following you.

I'd like to help you set it up so that you don't need 50 different Click procedures. To do that, we need to have a way that AxisTitle, Index and Caption1 can be extracted from the properties of the controls when one of the 50 Labels is clicked.

So instead of the code you show for ACFeed_AC_Click, we'd like to have a generic procedure with the pseudo code.

Sub GenericChartLabel_Click()
  Date1 = Me.DTPicker1.Value  'first date value from the main userform
  Date2 = Me.DTPicker2.Value   'second date value from the main userform

  Element=(GetElementByColorOfButtons)  'e.g. "Co"

  'assuming caption reads  e.g. "Tonnage (t)"  
  AxisTitle= Element & " " & ThisLabel.Caption  

  'assuming name reads e.g. "ACFEED_AC"  
  Index=  ThisLabel.Name & "_" & Element       
 
  'assuming this expression is used for all 50 labels
  Caption1=Element & " " & Tonnage through Autoclave"  
 
  '--will add code here to pass variable values to Properties
  '...
  '...
  Call MakeChart  
End Sub

If it isn't possible to interpret those 3 variables from the controls properties and selected element button, then we could modify the label control properties to support that.

Please let me know if I've correctly described the relationship between the control properties and the 3 string variables.