Excel 2016 and newer VBA: how to maximize a chart within/to the worksheet size?-CodePudding

In https://i.stack.imgur.com/s6gII.png and How to get the size (width and height) of the area with excel vsto c# excluding the ribbon area? a similar question has already been put.

Excluding all GUI elements outside/around the "inner/client" worksheet area, e.g. without the ribbon, "X/ok/fx" cell contents, Sheet menu, "Ready Record Macro" footer line frame heights, left right border frame widths, etc.

And together with How to get the screen position of an active workbook? neither of these links has answered my needs yet. Or their instructions do not work for me, because neither of the .PageSetup.*Margin values corresponds to my worksheet "inner/client" OR "outside/GUI" borders frames overhead sizes.

I know, by writing

With ActiveChart.Parent    'access the chart container = the Shape
   .Left = 0
   .Top = 0
   .Width = 4 * 72         'fixed size example
   .Height = 3 * 72        'fixed size example
End With

I can set the chart position size, in this example to a fixed size of 3 * 4 inches.

But HOW TO GET/fetch the whole currently visible dynamic worksheet "inner/client" size (at least once a method call) ?

In order to set the chart size, so that it covers/uses the whole currently visible dynamic worksheet "inner/client" size (at least once a method call). Hopefully: nothing more (no scrollbars shifting needed to see the whole chart), nothing less (no unneccessary "tiny" chart) ?

[I know, that size assignment will work for me only "for a moment" until a workbook resize happens. So I'll have to adjust the chart size again (and again (and again ...)), e.g. by using a timer (or a system timer). Or is there some "anchor/chain/nail" chart property, so that it ALWAYS keeps up with the "inner/client" worksheet size (within the workbook) ?]

Thanks for your help

CodePudding user response：

This is about as good as you can do.

Sub SizeChartToWindow()
  Dim VisibleRange As Range
  Set VisibleRange = ActiveWindow.VisibleRange
  
  Dim UsableRange As Range
  Set UsableRange = VisibleRange.Resize(VisibleRange.Rows.Count - 1, _
    VisibleRange.Columns.Count - 1)
  
  With ActiveChart.Parent
    .Left = UsableRange.Left
    .Top = UsableRange.Top
    .Width = UsableRange.Width
    .Height = UsableRange.Height
  End With
End Sub

CodePudding user response：

Many thanks, that helped me a lot.

This also takes care of the worksheet scrollbars "scrolled away", wonderful.

And the right bottom remainder of 0.00 .. 0.99 * cell sizes is "good enough" (and I have reduced the cell width to match the cell height = 20 pixels in order to reduce that remainder effect).

:)))