Excel VBA: what is the transforming formula given {X;Y} pair of Chart.MouseDown event to a {Category;Value} pair? -

Excel VBA: what is the transforming formula given {X;Y} pair of Chart.MouseDown event to a {Category;Value} pair? -

i trying find formula transforming x , y coordinates of mousedown/mouseup event of chart axes's coordinate system, i.e pair {category;value}. excel object model reference chart.mousedown event says:

the x coordinate of mouse pointer in chart object client coordinates.

i've found useful methods , properties task:

axis: left, width mousedown/mouseup: x, y chartobject: left, width application: centimeterstopoints, inchestopoints, measurementunit window: activepane, pointstoscreenpixelsx, pointstoscreenpixelsy pane: pointstoscreenpixelsx, pointstoscreenpixelsy

i've found x y values of "mouse" event in pixels -- see test:

private sub m_target_mousedown(byval button long, byval shift long, byval x long, byval y long) dim v_l long, v_r long 'x , y in pixels! m_target v_l = activewindow.activepane.pointstoscreenpixelsx(.parent.left) v_r = activewindow.activepane.pointstoscreenpixelsx(.parent.left + .parent.width) debug.print "x="; x; "y="; y; "xc="; x / 12 * 9; "yc="; y / 12 * 9; vba.chr$(13); _ "xlcategory: left="; .axes(xlcategory).left; " top="; .axes(xlcategory).top; " width="; .axes(xlcategory).width; vba.chr$(13); _ "xlvalue: left="; .axes(xlvalue).left; " top="; .axes(xlvalue).top; " width="; .axes(xlvalue).width; vba.chr$(13); _ "plotarea: left="; .plotarea.left; " top="; .plotarea.top; " width="; .plotarea.width; " height="; .plotarea.height; vba.chr$(13); _ "plotarea: insideleft="; .plotarea.insideleft; " insidetop="; .plotarea.insidetop; " insidewidth="; .plotarea.insidewidth; " insideheight="; .plotarea.insideheight; vba.chr$(13); _ "chartobject: left="; .parent.left; " top="; .parent.top; ", width="; .parent.width; " height="; .parent.height; vba.chr$(13); _ "activewindow: caption="; activewindow.caption; " left="; activewindow.left; " usablewidth="; activewindow.usablewidth; " width="; activewindow.width; vba.chr$(13); _ "chartobject: left_px="; activewindow.activepane.pointstoscreenpixelsx(.parent.left); " width_px="; v_r - v_l end end sub

the ratio 9/12 found in "excel vba: why big difference between values returned pointstoscreenpixelsx window , pane?"

clicking right border of chart gives next output:

x= 1838 y= 220 xc= 1378.5 yc= 165 xlcategory: left= 56 top= 186 width= 1286 xlvalue: left= 35 top= 2 width= 21 plotarea: left= 34.7092125984252 top=-4 width= 1320.84480314961 height= 210 plotarea: insideleft= 55.6250393700787 insidetop= 2.10251968503937 insidewidth= 1286.32645669291 insideheight= 183.845826771654 chartobject: left= 132.374954223633 top= 0 , width= 1377.72351074219 height= 210 activewindow: caption=q99708-vsspvfgatssik-dc-1147-dc-r0001-dn.xlsx left=-2 usablewidth= 1279.5 width= 1298.25 chartobject: left_px=-310 width_px= 1837

width_px close x , calculated v_r - v_l using converted left (v_l) , right (v_r) border pixels activewindow.activepane.pointstoscreenpixelsx.

but how find exact formula {category;value} {x;y} should work resolution (pixels per inch), measurementunit, pane.left/width, chart.left/width, plotarea.left/width , axis.left/width?

i've found formula. next surprises considerably complicated this:

chartarea.width <> chartobject.width , chartarea.height <> chartobject.height! it possible move shape in chart coordinate scheme out of positive coordinates.

i've created line shape in chart , moved line left constraint of chart. , line’s left -4! i've tried set shape.left property -4. not work: value becomes 0 after assignment. shape.incrementleft method moved line left constraint of chart (-4). shape.incrementtop method changes shape.top property value. result transformation pixels points found.

then experiments i've found ploatarea.inside<left,top,width,height> must used instead of corresponding properties of axis transform {x;y} pair in points {category;value} pair.

first, find shift of chartarea object in chartobject using motion of line shape left , top constraints:

sub extract_transform(byval p_chart chart, byref p_sh_x double, byref p_sh_y double) dim v_screenupdating boolean, v_sh double p_chart.shapes.addshape(msoline, 0, 0, 20, 0) p_sh_x = 0: .incrementleft -10 while p_sh_x <> .left p_sh_x = .left .incrementleft -10 'move left constaint wend p_sh_y = 0: .incrementtop -10 while p_sh_y <> .top p_sh_y = .top .incrementtop -10 'move top constaint wend .delete 'collecting garbage end end sub

using found {left;top} of chartarea object in chart coordinate scheme transformation of points pixels performed follows:

sub pixels2points(byval p_pane pane, byval x_pi long, byval y_pi long, byref x_pt double, byref y_pt double) dim v_sc double p_pane v_sc = (.pointstoscreenpixelsx(1000) - .pointstoscreenpixelsx(0)) / 1000 x_pt = m_sh_x + x_pi / v_sc v_sc = (.pointstoscreenpixelsy(1000) - .pointstoscreenpixelsy(0)) / 1000 y_pt = m_sh_y + y_pi / v_sc end end sub

the pixels2points assumes x_pi , y_pi in chart coordinate scheme in pixels. m_sh_x , m_sh_y of type double , members of class c_report. p_pane key object: pane.pointstoscreenpixels<x,y> methods transform points pixels , depends on scaling factor of window. same sheet may appear in many windows, hence corresponding pane object must used.

to transform {x;y} pair in points {category;value} pair chart wrapper class has next method:

sub pt2axes(byval p_axes axes, byval p_pa plotarea, byval x_pt double, byval y_pt double, byref p_cat double, byref p_val double) p_axes(xlcategory) p_cat = (x_pt - p_pa.insideleft) / p_pa.insidewidth * (.maximumscale - .minimumscale) + .minimumscale end p_axes(xlvalue) p_val = ((p_pa.insidetop + p_pa.insideheight) - y_pt) / p_pa.insideheight * (.maximumscale - .minimumscale) + .minimumscale end end sub

transformation formula y assumes origin of coordinates @ bottom of chart.

and application of these methods in handling chart.mousedown event:

private sub m_target_mousedown(byval button long, byval shift long, byval x long, byval y long) dim v_x double, v_y double, v_cat double, v_val double m_target debug.print "x="; x; "y="; y m_report.pixels2points activewindow.activepane, x, y, v_x, v_y pt2axes .axes, .plotarea, v_x, v_y, v_cat, v_val debug.print "cat="; v_cat; "val="; v_val end end sub

m_target class fellow member of type chart. m_report class fellow member of type c_report , serves reference parent object has pixels2points method.

to test formulas wide chart, frozen panes , scrolls @ scale 400% used , points @ crossing grid lines visible coordinates {270;2} , {10;6} clicked. result output of immediate window is:

x= 6980 y= 849 cat= 270.030767164932 val= 2.00522623783961 x= 564 y= 522 cat= 9.97421024128619 val= 6.00822155418118

i suppose difference +/-0.03 category axis occurred because of pixels discrete , vertical grid line step had not integer value in pixels.

excel vba excel-vba charts