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]

One thing though:

I'm trying to figure out a way to initialize the userform with Ni selected. Tried to put "Me.Ni.Select" in the initializing sub but no dice. So much for straight forward. Maybe something like initializing the selected element variable to "Ni"? Ni then would have to show green as the RoadMap userform initializes.

To point you in a good direction, one approach would be add code to this Sub in RoadMap that sets up the controls when the Userform is Initialized.

Private Sub InitializeControls()
 '--set control properties
 
 '--date picker range
 Me.DTPicker1.MinDate = Worksheets("EBal").Range("A14") - 365
 Me.DTPicker1.MaxDate = Worksheets("EBal").Range("A14")
 Me.DTPicker2.MinDate = Worksheets("EBal").Range("A14") - 365
 Me.DTPicker2.MaxDate = Worksheets("EBal").Range("A14")
 
 '--default picker settings
 Me.DTPicker1.Value = "1/1/2018"
 Me.DTPicker2.Value = Worksheets("EBal").Range("A14")
 

 '--resets all Element Buttons to default colors
 Call ResetElementButtons
End Sub

The actions that you want to happen are the same as those that occur in the ElementButtonClass when the Ni button is clicked...

Private Sub ElementButtonGroup_Click()
 '--store selection in roadmap property
 ElementButtonGroup.Parent.SelectedElement = ElementButtonGroup.Name
 
 '--resets all Element Buttons to default colors, selection to green
 ElementButtonGroup.Parent.ResetElementButtons
 ElementButtonGroup.BackColor = vbGreen
 ElementButtonGroup.Parent.ResetElementButtons
 ElementButtonGroup.Parent.SumByElement

End Sub

You'll want to incorporate those actions, but since ElementButtonGroup won't have any context (meaning) in RoadMap, you'll need to reference the button control directly.

 

[RawlinsCross]

I've having a huge amount of issues following this code. It's really complex and I simply can't follow it. So I understand the whole summation part - since we walked through it step by step. But when you click on a chart label, I'm assuming it goes to the ChartButtonClass but I don't understand the code there. Ultimately, it calls "ChartButtonGroup.Parent.MakeChart" but there are two subs that are called MakeChart - on in the RoadMap code and one in the A1_EBAL1 code. Which one is called and why do we have to MakeChart sub routines?

 

[RawlinsCross]

Okay, I think I know that the Public MakeChart gets called first and passes the parameters off to the A1_EBAL1 userform where it calls a Private MakeChart.

There's a problem though... wonder if you could look at what happens when you use this lGetHeaderColNumber function. It finds the correct column but then messes up the sGetChartParameter function. Basically it plots the correct column data but in many cases the charts do not have axis titles or userform captions.

Public Function lGetHeaderColNumber(wks As Worksheet, _
   sDataIdentifier As String) As Long
   
 '--finds match for specified data identifier in first row of range
 '    named "rngHeaders" on the worksheet.
 '--returns 0 if no match found
 
 Dim rHeaders As Range
 Dim vColNdx As Variant, vRowNdx As Variant
 
 Set rHeaders = wks.Range("rngHeaders")

'Using the second "FIND" feature to return the correct column
 'vColNdx = Application.Match(sDataIdentifier, rHeaders.Resize(1), 0)
 vColNdx = rHeaders.Find(What:=sDataIdentifier, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
 
      
 'Debug.Print vColNdx
 'Debug.Print vRowNdx
      
 If IsNumeric(vColNdx) Then
   lGetHeaderColNumber = vColNdx
 Else
   lGetHeaderColNumber = 0
 End If
      
End Function

 

[Jerry Sullivan]

If this modified lGetHeaderColNumber function returns the same value as the previous version, then the problem you are having in sGetChartParameter isn't due to lGetHeaderColNumber.

It looks like the original sGetChartParameter function that I wrote was based rngHeaders being a lookup table with multiple rows. That's different than I was recalling a while back when I noted that rngHeader should be one row only. If you've made rngHeader only one row, sGetChartParameter needs a way to read the data below rngHeader. One way to do that is to add another named range, let's say rngParameterLookup that includes rngHeader and the rows of lookup data below it.

I'd suggest you use the technique of having a stand alone Test sub that calls sGetChartParameter. You can use that Test with example arguments during development to make sure it's returning the correct result from the lookup table.

 

[RawlinsCross]

By using a break point immediately at the Public MakeChart sub routine and then stepping through (F8) all the code and putting debug.print for just about every variable I was able to make sense of what's going on. The issue is that absolute position given by the range.find method and the relative position of the match/index method need to be made compatible in the ChartParameter function. I was able to adjust the two function codes to plot the correct data and given the correct chart parameters.

Function which finds the absolute column that the data is stored

Public Function lGetHeaderColNumber(wks As Worksheet, _
   sDataIdentifier As String) As Long
   
 '--finds match for specified data identifier in first row of range
 '    named "rngHeaders" on the worksheet.
 '--returns 0 if no match found
 
 Dim rHeaders As Range
 Dim vColNdx As Variant, vRowNdx As Variant
 
 Set rHeaders = wks.Range("rngHeaders")

'Gives absolute column position
 vColNdx = rHeaders.Find(What:=sDataIdentifier, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
      
 If IsNumeric(vColNdx) Then
   lGetHeaderColNumber = vColNdx
 Else
   lGetHeaderColNumber = 0
 End If
      
End Function

Function that finds the relative position - relative row & relative column (note that vColNdx is absolute so a correction substraction is applied)

Public Function sGetChartParameter(wks As Worksheet, sDataIdentifier As String, _
   sParameter As String) As String
   
 '--looks up and returns value from within range named "rngHeaders"
 '-- on the worksheet.
 '--finds match for specified parameter in first column of range
 '--finds match for specified data identifier in first row of range
   
 Dim lColNdx As Long
 Dim rHeaders As Range
 Dim vRowNdx As Variant
 
 Set rHeaders = wks.Range("rngHeaders")

 lColNdx = lGetHeaderColNumber(wks:=wks, sDataIdentifier:=sDataIdentifier)
 If lColNdx = 0 Then
   MsgBox "Identifier not found: " & sDataIdentifier
   Exit Function
 End If
 
'Since the Find uses the .Row method, its absolute position (since the first row is empty) is subtracted by 1 to make the position relative
 vRowNdx = rHeaders.Find(What:=sParameter, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Row - 1
  
 'Index uses relative position, lColNdx is expressed in absolute position so must be lessened by the number of columns preceeding the rngHeaders range.
 If IsNumeric(vRowNdx) Then
   sGetChartParameter = Application.Index(rHeaders, CLng(vRowNdx), lColNdx - (lGetHeaderColNumber(wks:=wks, sDataIdentifier:="Data Identifier") - 1))  
 Else
   MsgBox "Parameter not found: " & sParameter
 End If
 
End Function

 

[RawlinsCross]

So with those two functions, the graphs show the correct data and the correct chart parameters.

 

[Jerry Sullivan]

By using a break point immediately at the Public MakeChart sub routine and then stepping through (F8) all the code and putting debug.print for just about every variable I was able to make sense of what's going on.

Well done! An alternative to debug.print statements is adding Watches. You might try that sometime to see if you like it better.

Why did you opt to use Find instead of Match?

 

[RawlinsCross]

Because Match for say ACFEED_AC_Co would return 3 (it's in the third column of rngHeaders) but for one to plot the correct data you needed the correct column number (670). Range.Find.Col gave me the 670. Now I guess I could have extended by rngHeaders back to column A but didn't think that wise (rngHeaders start at column 668).

 

[RawlinsCross]

"Tonnage through Autoclave" is for only one label. If you look at the spreadsheet I sent you'll see that from row 3 on the Ebal spreadsheet from column 667 onwards there are a variety of caption descriptions. Also, I have to enter into A1_EBAL1 the other 3 properties (Number format - row 4, Minimum Scale - row 5, Maximum Scale - row 6). I think I can do this now that I've walked through the code (F8).

Also there are BIGGER plans for the Road Map (easiest to hardest):

1. Bring functionality to the A1_EBAL1 graph. I'd like to pass back DTPicker3 and DTPicker4 to re-graph in case the user wishes to zoom in or zoom out in time. Also y-axis scale adjustment.

2. 50 more labels, over each graphic, invisible first. Group into another class?? Add a command button to main RoadMap userform so when the user clicks it each label appears over its graphic presenting a percentage (to what percentage do all the inputs balance the outputs for the element selected). Click again to make labels disappear.

3. THE BIGGEST AND COOLEST of them all. A final command button on the main userform that open another larger userform. An algorithim scans each data column of each label warning if that element at that location is trending up (or down or off target, etc.) I will send an email to give you a picture of what that would look like.

 

[RawlinsCross]

So I've been working on #1 above. The A1_EBAL1 userform is a graph that contains under set of DTPickers so if the user wants to look at another time frame he/she can. I've been able to jerry-rig some code together to test it and I've been somewhat successful but had to hard-code (temporarily) to test it. I couldn't get it to work without destroying the initial A1_EBAL1 and initializing another. Is there a way I can modify the initial A1_EBAL1 userform?

Private Sub Redraw_Click()

Dim rXvalues As Range
Dim rYvalues As Range
Dim frm As A1_EBAL1

'Setup new start and end times
Set rXvalues = rGetXValuesRange(wks:=Worksheets("EBal"), _
   dtStart:=Me.DTPicker3.Value, dtEnd:=Me.DTPicker4.Value)
'test
 Set rYvalues = rXvalues.Offset(0, 670 - 1)
 
 
Unload Me
'
'--create a new instance of charting userform A1_EBAL1
 Set frm = New A1_EBAL1
 
 'I've commented these out as they throw and error
' frm.AxisTitle = sAxisTitle
' frm.CaptionForGraph = sCaptionForGraph
' frm.DataIdentifier = sDataIdentifier

 frm.AxisTitle = "TEST"
 frm.CaptionForGraph = "TEST"
 frm.DataIdentifier = "ACFEED_AC_Ni"
 Set frm.XValues = rXvalues
 Set frm.YValues = rYvalues

'--show frm
 frm.Show vbModeless
End Sub