I have been experimenting with VBA generation of XY scatter chart based on a listing made by the user. The Chart is totally created, modified, etc using VBA. During the course of creating this code and worksheet, I observed the following and I was wondering if anyone has made similar observations. (Excel97, used on Win98SE and WinXP)
1) Chart Label sizing>
As is known there is NO method to alter (or obtain) label box sizing (Width or Height), BUT - from testing it appears that the label's max <available> width is determined by the ChartArea width setting. The label will display the (label) contents to this maximum width and then will commence wrapping the contents onto the next line/s. If the contents do not require the max width, the box will shrink. (Legend width does NOT appear dependent on PlotArea width setting.) Label box height is determined by Font size selected.
2) Chart Label>
Using VBA to define the label contents by setting ....Text = "?????"
Directly entering a value via code as ...Text = "123.4"
The Label Text will be considered a number, even if the code first sets NumberFormat = "@" (i.e. Text)
The same occurs if the code References a cell, such as ...Text = Cells(5,3). Contents will be taken as being a number, even if the cell's contents has been preformatted as "Text" and testing the cell using isnumber(cell) = False
In all cases, when the specific label is clicked upon, and the label format window is opened, the <number> category will always appear as "general"
3) Chart Label formatting - adding the comma to numbers>
When using VBA to define specific labels, setting ...NumberFormat = "#,###.0" or "#,###.#" does not act as supposed. The label will show the contents as #,###.#0. And when the value is ####.12, the label will still show as #,###.10
The same occurs when .NumberFormat is not set or ="@" and VBA sets the label by ...Text = format(value,"#,###.0")
But, the format #,### works if you do not need the 1st decimal
1) Chart Label sizing>
As is known there is NO method to alter (or obtain) label box sizing (Width or Height), BUT - from testing it appears that the label's max <available> width is determined by the ChartArea width setting. The label will display the (label) contents to this maximum width and then will commence wrapping the contents onto the next line/s. If the contents do not require the max width, the box will shrink. (Legend width does NOT appear dependent on PlotArea width setting.) Label box height is determined by Font size selected.
2) Chart Label>
Using VBA to define the label contents by setting ....Text = "?????"
Directly entering a value via code as ...Text = "123.4"
The Label Text will be considered a number, even if the code first sets NumberFormat = "@" (i.e. Text)
The same occurs if the code References a cell, such as ...Text = Cells(5,3). Contents will be taken as being a number, even if the cell's contents has been preformatted as "Text" and testing the cell using isnumber(cell) = False
In all cases, when the specific label is clicked upon, and the label format window is opened, the <number> category will always appear as "general"
3) Chart Label formatting - adding the comma to numbers>
When using VBA to define specific labels, setting ...NumberFormat = "#,###.0" or "#,###.#" does not act as supposed. The label will show the contents as #,###.#0. And when the value is ####.12, the label will still show as #,###.10
The same occurs when .NumberFormat is not set or ="@" and VBA sets the label by ...Text = format(value,"#,###.0")
But, the format #,### works if you do not need the 1st decimal
