?
Solved

Understanding certain aspects of Dynamic Legends for an Excel Chart

Posted on 2015-01-08
4
Medium Priority
?
86 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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
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

Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

Question has a verified solution.

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

Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

764 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