Solved

Understanding certain aspects of Dynamic Legends for an Excel Chart

Posted on 2015-01-08
4
77 Views
Last Modified: 2015-02-02
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
Comment
Question by:Cook09
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 40538685
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
 

Author Comment

by:Cook09
ID: 40538948
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40539369
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
 

Author Comment

by:Cook09
ID: 40571430
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now