Jak odnaleźć źródło danych dla wykresu?

Utworzenie wykresu z wykorzystaniem kodu VBA jest relatywnie łatwe, zawsze można wspomóc się rejestratorem makr co szczególnie dotyczy początkujących adeptów programowania. W jaki sposób odnaleźć źródło danych dla wskazanego wykresu, a więc zakres komórek, na bazie których nasz wykres rysuje linie czy kolumny? W tym zadaniu nie pomoże rejestrator makr, całą koncepcję musimy oprzeć na obiektach powiązanych z wykresem wykorzystując precyzyjnie hierarchię tych obiektów i odpowiednie właściwości.

Jako punkt odniesienia przyjmijmy następujący wykres liniowy posiadający jedną serię danych.

W rozwiązaniu, które poniżej musimy wykorzystać następujące obiekty i ich właściwości:

1. Obiekt Chart, czyli wykres, i jego właściwość:
2. Series, czyli serię danych wykresu, oraz jej właściwość
3. Formula, zwracającą formułę tworzącą referencję do zakresu danych.

Właściwy kod niezbędny do odnalezienia poszukiwanego zakresu wraz z dodatkowymi komentarzami znajduje się poniżej.

view sourceprint?

01	Sub GetSourceCellsAddress()

02

03	'wykorzystujemy zmienną obiektową,

04	'tworzymy referencję do pierwszego wykresu w arkuszu

05	Dim CH As Chart

06	Set CH = ActiveSheet.ChartObjects(1).Chart

07

08	'zmienna obiektowa wskazująca serię danych wykresu

09	'(pierwszą i w naszym wypadku jedyną)

10	Dim SC As Series

11	Set SC = CH.SeriesCollection(1)

12

13	'to czego tak na prawdę teraz poszukujemy to

14	'formuła dla serii danych

15	Dim strFormula As String

16	strFormula = SC.Formula

17	'test formuły:

18	Debug.Print strFormula

19	'wynik dla przykładu:

20	'=SERIE(Arkusz1!$B$1;Arkusz1!$A$2:$A$7;Arkusz1!$B$2:$B$7;1)

21

22	'w praktyce poszukujemy fragmentu formuły, trzeciego parametru

23	'który odpowiada za obszar danych, ty wykorzystamy kilka

24	'podstawowych tekstowych funkcji VBA

25	Dim strRangeFromFormula As String

26	strRangeFromFormula = Mid(strFormula, _

27	InStrRev(strFormula, "!") + 1, _

28	InStrRev(strFormula, ",") - InStrRev(strFormula, "!") - 1)

29	'test poszukiwań

30	Debug.Print strRangeFromFormula

31	'wynik: $B$2:$B$7

32

33	'poszukiwany zakres zaznaczymy więc tak

34	Range(strRangeFromFormula).Select

35

36	'*kod bonusowy- wyjaśnienie poniżej

37

38

End Sub

A co jeżeli chcielibyśmy odnaleźć kolejne elementy, punkty naszego wykresu i referencję do zakresu dla każdego z nich? W tym celu w miejscu oznaczonym gwiazdką w powyższym przykładzie musimy wstawić poniższy kod.

view sourceprint?

1	'w celu odczytania kolejnych punktów wykresu w naszym zakresie komórek

2	'-wykonamy tą operację z wykorzystaniem prostej pętli

3	Dim rngPT As Range

4	For Each rngPT In Range(strRangeFromFormula)

5	Debug.Print rngPT.Address

6	'wynik to kolejno $B$2, $B$3, $B$4, itd...

7	Next rngPT