Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

Understanding certain aspects of Dynamic Legends for an Excel Chart

Attached is an Excel file that uses dynamic legends and values for a chart(s). Most of it is understood, but there are a couple questions about the retrieving and formatting of the Legends and some values.  The questions are on the first page of the workbook.
Dynamic-Legends---Questions.xlsm
0
Cook09
Asked:
Cook09
  • 2
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you are asking these questions in the Excel sheet:

1. How does B12 return only with a numerical value?
2. For Legend Tabs 1-5, how is the width calculated?
3. How is the long color bar generated within each Legend page, above the State?
4. It seems that the Camera feature is being used for the State Legends, but what method changes the picture setting?

Here are my answers:

1. B12 contains the formula =MATCH(Report!$K$3,LegendSheet,0)  The Match function always returns a number. It returns the position of the matched value in the list.

2. The width is calculated with a VBA macro. It is a custom function called RangeWidth() and it uses the range name s.Legend as a parameter. The s.Legend named range exists multiple times, once on each Legend sheet, and contains the range with the mocked up legend on the sheet. I assume that the column widths have been manually set to the optimal fit on each sheet.

3. It's an empty cell with a background fill and the row height is set to 4

4. The legend on the report is a picture. The source for the dynamic picture can be seen in the formula bar when the picture is selected. It is the range name "LegendChoice". The definition of that name is

=CHOOSE(LegendNum,Legend1!s.Legend,Legend2!s.Legend,Legend3!s.Legend,Legend4!s.Legend,Legend5!s.Legend)

So, based on the Legend Number selected in cell B5 on the control sheet, the formula returns the s.Legend range on the respective legend sheet.

The use of the camera tool and the colors look like this comes from ExcelUser.com by Charley Kyd. Is that where you got the file?

cheers, teylyn
0
 
Cook09Author Commented:
Teylyn,
Yes, this is one of Charley's, I'm on his subscription list...but the explanations behind some of the charts are a little weak.

The reason for question 2, is not being able to find the variable rngCurv. Normally, if a Named Range is selected, the Name of the Range will appear in the Name Box.  But, I can't find where this variable is used within the UDF.  The total is based upon pixels, but how is that calculated? rngCurv has to perform some conversion, or so it would seem.  Would you have some insight on how this is being done?

I saw that the picture was LegendChoice, but is it one picture that changes its values and data based upon a particular Legend? If it's part of the Camera function, then at what point is a variable assigned to show the appropriate data?

Regards,
Cook09
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I think you are referring to this code:

Function RangeWidth(rngCur As Range) As Single
    ''Calculate this macro each time the workbook is calculated
    Application.Volatile
    
    ''Return the width, in points, for rngCur
    RangeWidth = rngCur.Width
End Function

Open in new window


rngCur is defined as a range variable in the function call. This is how VBA works.

I can create a new function, for example

Function MyTest(myString as String, myNumber as Long, myRange as Range) as string
.... do something
end Function 

Open in new window


At the same time as starting the function, I have declared three different variables. When the function is called, it will require three different parameters: a text string, a number and a range, like =MyTest("Hello",2015,$A$1)
While the function is running, it uses myString, myNumber and myRange as variables that have been populated with the parameters from the function call.

In the spreadsheets, the RangeWidth() function is used in a cell like this:

=RangeWidth(s.Legend)

So the s.Legend range is passed to the RangeWidth function. The statement

    RangeWidth = rngCur.Width

will pass the width in points of that range (which is now represented by the rngCur variable) back to be used as the result of the function.
0
 
Cook09Author Commented:
teyln...I have tried four times to award a grade (A) and points (500), but it won't move the question as Solved...just abadoned.  Can you help??

Cook09
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now