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]

Took me a while to get home. Early rise at 430am but before I head in I had to try. Kept getting a "Run-time error '438' Object doesn't support this property or method". But then I had the SumByElement (figured it was typo in your message) in the module and not the roadmap userform. When I placed the code there, presto changeo, everytime I click an element button the msgbox appears.

So far so good!

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.
'Step 1:  Get the currently selected element
'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

'--temporary test only
MsgBox "Element button clicked!"

End Sub

 

[Jerry Sullivan]

Great. Next, see if you can write code under Step 1 that gets the name of the element button that was clicked, then displays a message box like "You clicked Ni" where Ni is the name of the element button. Delete the temporary test message at the bottom of your Sub so you just get one prompt.

 

[RawlinsCross]

Okay, think I have it - atleast it works. I've printed off all the code from the Road Map, EBal1, Module, ChartButtonClass, and ElementButonClass so I can try to follow what's being called and what's going on

Some questions (I'll try to keep them to a minimum and search first):
1. I notice a naming convention using 'r', 's', 'm', 'l' as lowercase letters before variable names. I tried to look it up. 'm' is for member which is for the member variables in a class. What are the other letters?

2. I see a lot of "Set" and other places where it's not used. "So when you want to set a value, you don't need "Set"; otherwise, if you are referring to an object, e.g. worksheet/range etc., you need using "Set". Set is an Keyword and it is used to assign a reference to an Object in VBA". Not sure what that means but when I tried "Set" I got a compile error.

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.

'Step 1:  Get the currently selected element

Dim sSelectedElement As String
sSelectedElement = msSelectedElement 'code compile error if I use "Set"
MsgBox "You clicked " & sSelectedElement

'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



End Sub

 

[RawlinsCross]

Now that I've printed off all the code and am reading it over and over again, I see that the lower case letters match the variable type: r = range, l = long, v = variant, s = string. Cool.

 

[Jerry Sullivan]

Here's the next bite. I'll reply to your general VBA questions in a separate post.

To step through each label that has the Tag "ChartButton", we can use the ChartButtons array that we populate when the RoadMap UserForm is opened. We'll use a For... Each construct to iterate through each ChartButton. The code below should display the Name property of each ChartButton label in the Immediate Window of the VB Editor. If you don't already have that displayed, you can toggle the Immediate Window with Ctrl+G from within the VB Editor.

This serves the same purpose as the MsgBox we used earlier, but you don't have to click OK repeatedly.

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

'Step 1:  Get the currently selected element

sSelectedElement = msSelectedElement 'code compile error if I use "Set"
MsgBox "You clicked " & sSelectedElement

'Step 2:  For each label in the collection of labels follow steps 3 to 7

For Each vChartButton In ChartButtons
 '--temporary test: display name of each control in Immediate Window of VB Editor
 Debug.Print vChartButton.ChartButtonGroup.Name
Next vChartButton


'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

End Sub

The next piece for you is to start Step 3. Use the properties to form the look_up tag. Then as a temporary test, change the Caption property of each ChartButton to display the look_up_tag.

 

[RawlinsCross]

I agree, nevermind any questions right now. They'll interrupt the flow of this exercise. Perhaps this code provides what you were asking for?

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 lookUpTag As String

'Step 1:  Get the currently selected element
sSelectedElement = msSelectedElement 
MsgBox "You clicked " & sSelectedElement

'Step 2:  For each label in the collection of labels follow steps 3 to 7
For Each vChartButton In ChartButtons  
 '--temporary test: display name of each control in the Immediate Window of VB Editor
 Debug.Print vChartButton.ChartButtonGroup.Name
Next vChartButton

'Step 3:  Combine the tag property of the label and the element presently selected.  This is known as the look_up_tag
For Each vChartButton In ChartButtons
    lookUpTag = vChartButton.ChartButtonGroup.Name & "_" & sSelectedElement
    vChartButton.ChartButtonGroup.Caption = lookUpTag
    vChartButton.ChartButtonGroup.AutoSize = True
Next vChartButton

'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

End Sub

 

[Jerry Sullivan]

Yes, that's good. You might rename the variable "sLookUpTag" to stick with a consistent naming convention.

For the next step, we'll start writing a separate function to get the desired summation. We'll place that function in Module1.

Start by using this outline for that function...

Function GetSumFromWorksheet(sLookUpTag as String) as Long
'--(when completed) this function returns the summation at this point in the process using the arguments to find the worksheet range
'--(for this step of development) this function returns the column number of the tag which will be above the data to be summed.

 Dim lColumnNbr as long

 '--edit here to find the column number
 lColumnNbr= ????

 '--return the column number to the calling procedure
 GetSumFromWorksheet= lColumnNbr
End Function

While developing that function, you can test it with a standalone Sub like this.

Sub Test1()
 Dim sLookUpTag as String

 sLookUpTag= "ACFEED_AC_Co"

 '--should return column 670 from your example workbook
 Debug.Print  sLookUpTag & " is found in column: " &  GetSumFromWorksheet(sLookUpTag:=sLookUpTag)

End Sub

Once you've successfully tested your function with 2-3 different lookuptags, you're ready to call the function from the RoadMap UserForm.

Delete the previous msgBoxes and Debug.Print statements, then add a call under Step 4 of SumByElement to get the Column Number. Place that Column Number in each ChartButton's Caption (instead of the lookuptag).

When you've finished this step, you should be able to click on an Elemental Button, and the code will populate all the ChartButtons captions with the corresponding column number from the worksheet.

 

[RawlinsCross]

okay, some interesting developments on the function. Something's not right but there are clues. Code first:

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")

'unfortunately this returns Column 0 in the immediate window but 2 *and then 0* in the watch window)
'lColumnNbr = Application.Match(sLookUpTag, rHeaders.Resize(1), 0)

'tried this one as well (this one returns 0 in the immediate window but 669 *and then 0* in the watch window)
lColumnNbr = rHeaders.Find(What:=sLookUpTag, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Column

'return the column number to the calling procedure
GetSumFromWorksheeet = lColumnNbr

End Function

Do I need to resize "rngHeaders" to use Match to include all rows?

Why do I see a non-zero answer in the Watch window? It's like the stored variable is falling out-of-scope.

 

[RawlinsCross]

Yikes, sorry sorry sorry... code has "GetSumFromWorkSheeeeeeeeeeeet" (similar to that). Must have fell asleep on the 'e'. Okay, I can continue with the FIND function but I need to add '1' I guess.

 

[RawlinsCross]

Don't have to add 1, had Ni instead of Co. I'll work harder to reduce these replies.