Excel has SetSourceData, which I love because it is so elegant.
Unfortunately, there was no yin to the yang: No such thing as
GetSourceData was made to accompany it.

I needed this long ago, but ended up writing some ridiculous
Chart.Series string parsery instead. Which was probably much more
reliable. But damn near indecipherable.

(And Jon Peltier has an excel Add-In to deal with this oversight, no?)

But sometimes there is something to simple, ugly, but devious code, so
I just thought, why not post something that breaks two well-
established rules of robust coding; No sendkeys and no clipboard:



Function GetSourceData(strChtName As String) as String
Dim clipSourceData As DataObject
Dim strSourceData As String

Set clipSourceData = New DataObject

Charts(strChtName).Activate

'Before you think I'm completely crazy, I superstitiously add the
'Numlock toggles as a harmless way to make sure the keys
'that matter (Ctrl+C) aren't sent before the dialog pops up
SendKeys "{NUMLOCK}{NUMLOCK}{NUMLOCK}{NUMLOCK}^c{ESC}"
Application.Dialogs(xlDialogChartSourceData).Show
clipSourceData.GetFromClipboard
strSourceData = clipSourceData.GetText

GetSourceData = strSourceData
MsgBox "Is ~this~ your card?" & vbCR & strSourceData

End Function

To boot, this returns nothing about whether it's plotted by rows or
columns, and if the Data Range is too complex to display, I'm fairly
certain your computer (and also possibly your head) will explode.*

When I whipped this up, I just had too big a smile on my face to worry
about such piddly consequences.

*Other side effects may include gnashing of teeth/wails of disbelief/
shock vomit. Please ask your doctor if GetSourceData is right for you.