VB.NET Excel Automation of Charts with Named Variables

fergy2004

New Member
Joined
Feb 1, 2010
Messages
7
Hi all! I'm new to the forums and hope I am placing this post in the correct location. I'm having a lot of trouble automating charts from my VB.NET program. I'm simply graphing one series of data as a time series that must maintain its order as the user filters the data, deletes some data, etc. Because of the changes the user can make, I am using a named variable with the offset command to maintain a dynamic range. I am aware of how to manually build the graph by supplying the series range =workbookname.xls!namedvariable, but I can't figure out how to supply this to my chart from my VB program. The following is some of my VB.NET code.

xlapp.names.add("graph" & i, "=OFFSET(Hidden!$A$20,0," & 1 + i * 3 & ",Hidden!$A$20,1)")
chartrange= ???????
chartPage.ChartWizard(Source:=chartRange, HasLegend:=False, Title:=ParameterNames(i) & " Time Series")

I can supply more code if needed, but figured this would give a general idea of what I am trying to do. This has to work across multiple Office platforms so I am using late binding. All help is greatly appreciated!
 
Does anyone know why Office 03 seems to take a snapsot of the range with the above code rather than use the named variable while Office 07 handles it just fine? I can't seem to find any information on it elsewhere.
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Good news all! I have figured out a couple more things that have solved my problem. It appears that the chartwizard routine is the cause of my different results between Office versions. If anyone has an explanation to why this is, I would love to hear it. For now, I just removed the chartwizard call since it wasn't doing anything but formatting for me and I can do that other ways. As per my discussion of using late binding with a series and accessing the .values and .XValues, it is possible after all. I was trying to pass them a range object, but it needs a string that includes the worksheet names, not just b3:b10.

Thanks for all the help! If anyone wants a better explanation of my fix, just let me know.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top