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

 

[RawlinsCross]

I successfully summed up a column via supplied dates with my test code:

Test Code

Sub Test1()

Dim sLookUpTag As String
Dim dtStartDate As Date
Dim dtEndDate As Date
Worksheets("EBal").Select
sLookUpTag = "ACFEED_AC_Ni"
dtStartDate = #2/3/2018#
dtEndDate = #4/1/2018#
Debug.Print sLookUpTag & " between dates " & dtStartDate & " and " & dtEndDate & " has the sum: " & GetSumFromWorksheet(sLookUpTag:=sLookUpTag, dtStartDate:=dtStartDate, dtEndDate:=dtEndDate)

but cannot reproduce it when passing Me.DTPicker1.Value and Me.DTPicker2.Value

RoadMap:

Sub SumByElement()
'--the purpose is to step through each point of the flow process, calculate summations at that point and display each sum in the Label control at that pont.
'--each summation will based on the currently selected element and date range selected by the user.
Dim sSelectedElement As String
Dim vChartButton As Variant
Dim sLookUpTag As String
Dim dtStartDate As Date
Dim dtEndDate As Date

'Step 1:  Get the currently selected element
sSelectedElement = msSelectedElement
'Step 2:  For each label in the collection of labels follow steps 3 to 7
'Step 3:  Combine the tag property of the label and the element presently selected.  This is known as the look_up_tag
'Step 4:  Take the look_up_tag and search for that tag which will be above the data to be summated.
'Step 5:  Find the row range that corresponds to DTPicker1 and DTPicker2.
'Step 6:  Sum up the range for the column from Step 4 and the rows from Step 5
'Step 7:  Output the sum in the caption of the label
dtStartDate = Format(Me.DTPicker1.Value, "mm/dd/yyyy")
dtEndDate = Format(Me.DTPicker2.Value, "mm/dd/yyyy")
For Each vChartButton In ChartButtons
    sLookUpTag = vChartButton.ChartButtonGroup.Name & "_" & sSelectedElement
    vChartButton.ChartButtonGroup.Caption = GetSumFromWorksheet(sLookUpTag:=sLookUpTag, dtStartDate:=dtStartDate, dtEndDate:=dtEndDate)
    vChartButton.ChartButtonGroup.WordWrap = False
    vChartButton.ChartButtonGroup.AutoSize = True
Next vChartButton



End Sub

Function:

Function GetSumFromWorksheet(sLookUpTag As String, dtStartDate As Date, dtEndDate As Date) As Double
'--this function returns the summation at this point in the process using the arguments to find the worksheet range
'--(for now) this function returns the column number of the tag which will be above the dta to be summed.
Dim lColumnNbr As Long
Dim lStartDate As Long
Dim lEndDate As Long
Dim lFinalSum As Long
Dim rHeaders As Range
Dim rData As Range
Dim wks As Worksheet
Set wks = Worksheets("EBal")
Set rHeaders = wks.Range("rngHeaders")
Set rData = wks.Range("A:A")

On Error Resume Next
'returns the correct column number/letter referenced from the 'A' column
lColumnNbr = rHeaders.Find(What:=sLookUpTag, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
'returns the row number of the StartDate
lStartDate = rData.Find(What:=dtStartDate, Lookat:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False).Row
'returns the row number of the EndDate
lEndDate = rData.Find(What:=dtEndDate, Lookat:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False).Row
'sum the associated range
lFinalSum = Application.Sum(wks.Range(Cells(lEndDate, lColumnNbr), Cells(lStartDate, lColumnNbr)))
On Error GoTo 0
'returns the column sum to the calling procedure.  If the first character of the string is a number returns NA
If lFinalSum = 0 Then
    GetSumFromWorksheet = 5
Else
    GetSumFromWorksheet = lFinalSum
End If
End Function

 

[RawlinsCross]

After some sleuthing and trying few things, I got it work from the RoadMap/Function route. But I had to add code in the Function that selects or activates the sheet. This slows down the transition from one set of numbers to another set as you click across the element buttons. It's too slow, I have to turn off ScreenUpdating, Select "EBal", run the Function, reSelect the dashboard tab, and turn on ScreenUpdating. Slllooooooowwww! There's got to a better way. Why is the function not working with the "wks.Range" code in the function shown above?

 

[Jerry Sullivan]

The code in post #71 worked correctly for me. It provided the same sum when run from Test1 and in the RoadMap after selecting the date range and clicking Ni.

What happened when you ran the RoadMap code in #71 ?

 

[RawlinsCross]

Yeah, I was puzzled as I read the code over and over and thought, "there's no reason this shouldn't run". It gave me "5" for all 50 labels (in the code I put in '5' for the if statement just to trace that it was failing there. I'll look into it more as I do not what to start selecting pages.

 

[RawlinsCross]

Maybe because of this?

https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells

 

[RawlinsCross]

so yes, changing the lFinalSum to this works, you don't have to select the sheet thus making it super fast.

'sum the associated range
lFinalSum = Application.Sum(Range(wks.Cells(lEndDate, lColumnNbr), wks.Cells(lStartDate, lColumnNbr)))

 

[Jerry Sullivan]

Well done!

 

[RawlinsCross]

Okay, so yeah, mission accomplished on the ElementSum. Now, something that might have taken you 5 minutes to do just took me a week or so. Anyhow, onwards and upwards.

So when I click on an element, and change the dates, you need to click back on the element to update. An improvement (which I have in my head on how to do it *event change call*) would be that numbers automatically change when you change the DTPickers based on the currently chosen element. Seems fairly straight forward that! (famous last words)

But the gorilla in the room now is those pesky graphs of which there are 550 possibilities. So when you click on a number, you should get the userform A1_EBal1 which is has a imagebox for a chart and its own DTPickers were the user can trend a single element at that label location over time.

Right now, when I click on a "summed number" it tries to combine that number with the selected element and you get an error. e.g. Identifier not found: 3998_Ni

So it looks like we need to enter in the tag name somewhere else on the property of the label and reference to that?

 

[Jerry Sullivan]

So when I click on an element, and change the dates, you need to click back on the element to update. An improvement (which I have in my head on how to do it *event change call*) would be that numbers automatically change when you change the DTPickers based on the currently chosen element. Seems fairly straight forward that! (famous last words)

Yes, pretty straight forward.

Your Sub SumByElement is currently only called when a ChartButton label is clicked. You can add code to call SumByElement when either DatePicker is changed.

Currently, the SumByElement code assumes that an element has been selected (because SumByElement only gets run when an elemental button is clicked. You need to add code to either...

1. Handle the scenario of a DatePicker being changed when an element is not selected.

2. Ensure that the scenario in #1 can't happen. This could be done by always having a default element set, or making the DatePickers inactive until an element is selected.

But the gorilla in the room now is those pesky graphs of which there are 550 possibilities. So when you click on a number, you should get the userform A1_EBal1 which is has a imagebox for a chart and its own DTPickers were the user can trend a single element at that label location over time.

Right now, when I click on a "summed number" it tries to combine that number with the selected element and you get an error. e.g. Identifier not found: 3998_Ni

So it looks like we need to enter in the tag name somewhere else on the property of the label and reference to that?

Back in Post #28 , we changed this procedure in the ChartButtonClass to read the Name property instead of the Caption...

Private Sub ChartButtonGroup_Click()
 '--store selection in roadmap property
 ChartButtonGroup.Parent.SelectedLabel = ChartButtonGroup.Name
 
  '--trigger making of new chart
 Call ChartButtonGroup.Parent.MakeChart
End Sub

 

[RawlinsCross]

Okay, I've made a few changes to the code (which you had always maintained in your first post). I didn't add a new workbook to make the chart, simply inserted the chart as a chartobject in the active worksheet that you can delete after it's saved as a jpeg. That 85 ActiveWindow.Zoom makes the chart fit perfectly into Image1 of Al_EBAL1. So all graphs displaying nicely and update with different element selected - very cool.

May have to replace the Match method with the Find method as before as graphs are showing data but I think it's the wrong column. Will use my newly acquired debugging skills (thank you by the way) to see which columns are being pointed to. So I'll attempt that over the next while.

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.