Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Understanding certain aspects of Dynamic Legends for an Excel Chart

Posted on 2015-01-08
4
Medium Priority
?
88 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
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 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 …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

610 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