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]

Had some gliches as not all formed look_up_tags are listed in rngHeaders. Got around that with "On Error Resume Next". Not sure if this is an entirely appropriate way to do things but it works.

Function:

 Function GetSumFromWorksheet(sLookUpTag As String) As Long

'--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 rHeaders As Range
Dim wks As Worksheet

Set wks = Worksheets("EBal")
Set rHeaders = wks.Range("rngHeaders")

On Error Resume Next

'returns the correct column number referenced from the 'A' column
lColumnNbr = rHeaders.Find(What:=sLookUpTag, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Column

'returns the column number to the calling procedure.  If it cannot find the sLookUpTag is returns 0
If IsNumeric(lColumnNbr) Then
    GetSumFromWorksheet = lColumnNbr
Else
    GetSumFromWorksheet = 0
End If

End Function

RoadMap Code:

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

'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.
For Each vChartButton In ChartButtons
    sLookUpTag = vChartButton.ChartButtonGroup.Name & "_" & sSelectedElement
    vChartButton.ChartButtonGroup.Caption = GetSumFromWorksheet(sLookUpTag:=sLookUpTag)
Next vChartButton

'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

End Sub

 

[Jerry Sullivan]

Using an On Error Resume Next statement allows execution of the code to continue, but that method should be used with caution.

Always follow up with an On Error GoTo 0 statement, as soon as you've gotten past the code that might throw an error. Otherwise, the Resume Next error handler could mask errors further down in your procedure.

In this case, you could place that here...

Set rHeaders = wks.Range("rngHeaders")

On Error Resume Next

'returns the correct column number referenced from the 'A' column
lColumnNbr = rHeaders.Find(What:=sLookUpTag, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Column

On Error GoTo 0 '--resets error handler

'returns the column number to the calling procedure.  If it cannot find the sLookUpTag is returns 0
If IsNumeric(lColumnNbr) Then

The other caution, is to think through what you want to have happen when a match for sLookUpTag isn't found. As written, your code will pass 0 back to the calling procedure. That's okay during development because you know that there's no Column Number 0, but when you develop this into a Sum that represents units of flow, pressure or volume, returning 0 is ambiguous since it might represent 0 units instead of "Tag not found".

The next task as you might have guessed is to further develop function GetSumFromWorksheet to accomplish:

Step 5: Find the row range that corresponds to DTPicker1 and DTPicker2.

To do so, you'll need to add some parameters to the function for the Start date and End Date. Instead of returning a Long data type, you'll return a String that represents the Address range to be summed (e.g. "BB42:BB71"). Modify SumByElement to display this String in the Label of each ChartButton.

 

[RawlinsCross]

I've hit a snag with the exact code that works in post #51 . I had even Step 5 showing ("BB42:BB71") working but then after starting my computer again (not having changed anything), nothing worked again.

I've looked into for the last few hours, not sure what's on the go. The code continues to fail at the "FIND" command. Does this command need to be reset? Using "Application.FindFormat.Clear" doesn't seem to help.

 

[Jerry Sullivan]

Try isolating the problem using a standalone sub to call your function as you did with the Test1 Sub in post #47 . Use the same parameters for lookuptag, startdate, enddate that were in use when your previous error occurred. Temporarily comment out the On Error Resume Next statement, so that the Excel will return the error number and description.

 

[RawlinsCross]

I went back to an earlier version where I was finding the column numbers via the look_up_tag. Getting a "Run-time error '91. Object variable or With block variable not set" in the function at lColumnNbr find method. Having been reading that that's the error kicked up with the FIND function finds nothing.

Sub Test1()

Dim sLookUpTag As String

Worksheets("EBal").Select
sLookUpTag = "ACFEED_AC_Ni"

'--should return column 670 from your example workbook

Debug.Print sLookUpTag & " is found in column: " & GetSumFromWorksheet(sLookUpTag:=sLookUpTag)

End Sub


Function GetSumFromWorksheet(sLookUpTag As String) As Long

'--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 rHeaders As Range
Dim wks As Worksheet

Set wks = Worksheets("EBal")
Set rHeaders = wks.Range("rngHeaders")

Application.FindFormat.Clear

'On Error Resume Next

'returns the correct column number referenced from the 'A' column
lColumnNbr = rHeaders.Find(What:=sLookUpTag, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Column

'On Error GoTo 0

'returns the column number to the calling procedure.  If it cannot find the sLookUpTag is returns 0
If IsNumeric(lColumnNbr) Then
    GetSumFromWorksheet = lColumnNbr
Else
    GetSumFromWorksheet = 0

End If

End Function

 

[Jerry Sullivan]

That’s why you added the Resume Next. Weren’t you saying the code didn’t work even when you had On Error Resume Next?

 

[RawlinsCross]

If I include the "On Error Resume Next" - all labels on the Road Map read "Tag Not Found" whereas before they did. I'll keep poking around with the code. In case there was something obvious that I was missing, I mentioned it here.

 

[Jerry Sullivan]

I've hit a snag with the exact code that works in post #51 . I had even Step 5 showing ("BB42:BB71") working but then after starting my computer again (not having changed anything), nothing worked again.

I've looked into for the last few hours, not sure what's on the go. The code continues to fail at the "FIND" command. Does this command need to be reset? Using "Application.FindFormat.Clear" doesn't seem to help.

John, I'm sorry but I'm not understanding the problem you're describing.

You have some tag combinations that won't be found in the lookup range. You can use Resume Next, along with code that does whatever actions you want when a tag doesn't exist. What specifically is happening that you don't expect?

 

[RawlinsCross]

yes but it was throwing errors for everything - tags that weren’t there and tags that were. So in my code above, went back to testing one I knew was there “ACFEED_AC_Ni” but even that is throwing up the same error. Very perplexing.

 

[Jerry Sullivan]

Now I understand what you mean.

A few things to try...
1. When the code stops, type this expression in the Immediate Window, then Enter. This will confirm that the correct range is being assigned.
?rHeaders.Address

2. Try doing a manual Find operation in the worksheet. Select the range, paste "ACFEED_AC_Ni" in the dialog, to make sure there's not a mismatch due to a typo or hidden characters.