CODEWITHSUNDEEP
excelvba
BuZz
BuZz

Reputation: 17485

load VBA data to line chart

I need to feed data into a line chart with VBA. I need to give a basic unidimensional range when I do it with Excel in a raw test. The thing is that now I need to hide some things in VBA code, so that the user just sees a curve resulting of some basic input at the click of a button.

I think there are at least two options for me :

  1. either have the chart object placed in the Excel before, and having it point to some VBA snippet. In this case, should I somehow point to VBA from Excel ? Or would VBA access the chart area by some ID ? chart select data

  2. create everything dynamically from VBA, the chart window, the chart, and the series input. This might be trickier to figure out maybe as I have frozen panes, so there would be the question of which pane is active (I'd like to avoid to have to do Selects to select the right one)

What would you recommend ? And in either case, how to actually do it ? What type of data to put in ?

Thank you so much !

Upvotes: 0

Views: 441

Answers (1)

Tony Dallimore
Tony Dallimore

Reputation: 12403

I was involved in a project a couple of years ago that might be relevant.

A report was created monthly for the organisation's management board which contained dozens of charts. It was a near full time job for a statistician to collate and process data from across the organisation that provided the source data for these charts. I was asked to automate what could be automated.

It became clear that the appearance of the chart could not be automated. The statistician and the management board's staff held regular discussions and often agreed minor changes which they thought would better present the organisation's performance.

I could automate the collection of the data, its processing and placing of the results in the ranges from which the charts got their data. The most I ever did with a chart was change the size of a source range.

What would be the parameters that would control your macro's changes to a chart? How would your macro get the determine: type of chart, nature of the axes, text, etc?

Hope these thoughts help. I could look out my notes if my experience seems particularly relevant.

Upvotes: 1

Related Questions