[Rozwiązano] Jak skrócić zapis zakresu by uniknąć błędu Series formula is to long (VBA)?

Dzień dobry !

Mam pewien problem z kodem, którego celem jest odczytywanie wartości temperatur z dwóch/trzech/czterech termopar w komorze termicznej w określonym zakresie. Zebrane dane ustawiane są w kolumnach, przeciętna ilość wierszy po całym cyklu zbierania danych to około 13 tysięcy.

W czasie ustawienia pomiaru detektory zczytują wartości wewnątrz komory, co pozwala na porównanie ich z tym co ustawiono na programatorze - zabieg ten prowadzony jest w celu kalibracji urządzenia. Zakres pracy mieści się pomiędzy (-35) a 85 stopni Celsjusza. Piec nastawia się po zaprogramowaniu, na 12godzinne odcinki z daną temperaturą - w moim przypadku 12h (-35), 12h (-20), 12h 60 i 12h 85.

Makro, które stworzyłem ma na celu wybieranie interesującego mnie zakresu, tak by pominąć temperatury przejściowe np. (-30 czy 71), zaciąganie go do pamięci, tworzenie z niego wykresu. Następnie makro zbiera dane z serii z wykresu i przerzuca do zeszytu "DataChart". Makro działa perfekcyjnie dla małych zakresów np. -38,2 do -38,6. W przypadku poszerzenia zakresu wyskakuje mi błąd "Series formula is too long". Prawodpodobnie dzieje się tak, gdyż makro dobiera do wykresu komórki (w tej części makra pojawia się podświetlenie w debugerze) pojedynczo np. E1 E2 E3 E4 E5...Bn nie rozróżniając tego, że podobne wartości są w nastepnej komórce i można zamiast zbierania ich pojedynczo zapisać je jako zakres E1:Bn. Z tego co wiem zapis formuły "z palca" pozwala na zamieszczenie 8196 znaków, natomiast vba umozliwa chyba tylko formułę zapełniać 255 znakami.

I teraz moje pytanie: Czy jest jakiś sposób poszerzenia zakresu w kodzie VBA do tego by makro korzystało z zapisu formuły "z palca"? Czy jest jakis sposób na to by wymusić IF jeżeli szukane wartości są jedna pod drugą THEN utwórz zakres zamiast zbierać je pojedynczo?

Poniżej zamieszczam kod:

Adorov napisał:

Makro działa perfekcyjnie dla małych zakresów np. -38,2 do -38,6. W przypadku poszerzenia zakresu wyskakuje mi błąd "Series formula is too long". Prawodpodobnie dzieje się tak, gdyż makro dobiera do wykresu komórki (w tej części makra pojawia się podświetlenie w debugerze) pojedynczo np. E1 E2 E3 E4 E5...Bn nie rozróżniając tego, że podobne wartości są w nastepnej komórce i można zamiast zbierania ich pojedynczo zapisać je jako zakres E1:Bn

1. Można by wykorzystać jakiś zakres komórek arkusza lub w ogóle oddzielny arkusz na wstępne zgromadzenie danych w jednym ciągłym bloku, którego zakres da się określić i zapisać do zmiennej.
2. Można by też zgromadzić dane w zmiennej obszarowej nie obarczonej aż takimi ograniczeniami i wykorzystać ją jako nośnik danych do wykresu ("SetSourceData Source").

Adorov napisał:

Czy jest jakiś sposób poszerzenia zakresu w kodzie VBA

Pewnie tak, ale bez jakiegokolwiek przykładu, to lekka męczarnia ...

Adorov napisał:

Czy jest jakis sposób na to by wymusić IF jeżeli szukane wartości są jedna pod drugą THEN utwórz zakres zamiast zbierać je pojedynczo?

Pewnie tak, ale bez jakiegokolwiek przykładu, to lekka męczarnia ... : ) ...

Adorov napisał:

ma na celu wybieranie interesującego mnie zakresu, tak by pominąć temperatury przejściowe np. (-30 czy 71), zaciąganie go do pamięci, tworzenie z niego wykresu. Następnie makro zbiera dane z serii z wykresu i przerzuca do zeszytu "DataChart"

Czy rozumieć przez to, że dane są pobierane z czegoś tam (txt, csv, itd.) do arkusza excela, następnie robiony jest wstępny wykres, z którego następnie dane sa pobierane do innego arkusza, z którego następnie wedrują do kolejnego wykresu ? Jeśli tak, to jest to odrobinę complique ... : | ...


Inne:

1. "Union(rRng, .Range("E" & i))" - zarówno "Union", jak i "zlepki" adresów do "Range" mają ograniczenia do 30-32 argumentów
2. Czy "Worksheets("DataChart")" i "Set ws = ThisWorkbook.ActiveSheet" to dwa różne arkusze, czy jeden i ten sam ?
3. Czy nie ma obawy, że przy wykonywaniu ".Replace What:="#N/A", Replacement:="", LookAt:=xlPart" natrafi się na taką sytuację, iż "#N/A" będzie tylko fragmentem jakiegoś dłuższego zapisu (pozostanie wtedy w komórce dalszy ciąg "czegoś") ?
4. Skąd pochodzą dane: csv, txt ? Może można by coś zadziałać na etapie importu danych ... ?

Witam,

Tak worksheet to to samo co ws - dzięki za zwrócenie uwagi, po prostu po czasie dopisywałem ten fragment i jakoś pominąłem, że ws został juz określony przez DIM.

W załączniku wrzucam plik, po ściągnięciu trzeba zmienić rozszerzenie z *.jpg na *.xlsm - wyskakiwał błąd poczas dodawania pliku z roszerzeniem excela, że nie można załączać plików z takim rozszerzeniem.


Obsługiwany akutalnie jest arkusz 24 i 1-wszy zakres dla dwóch czujników. Odpala się je za pomocą guzika w tabeli po prawej. W polu czerwonym i zielonym zaznacza się zakres temperatur jakich makro ma szukać, natomiast temperaturę zadaną wpisuje się na podstawie tego co nastawiłem w piecu. Aktualnie prace prowadzone są w Module 1. Tam zaprogramowałem dwa pierwsze guziki.

Makro czasem robi a czasem nie robi zakresów Ex:En, jednak w przypadku dużej rozpiętości rekordów dalej wyskakuje ten błąd, że too long...

Co do makra usuwającego #N/A - nic więcej się nie pojawia, jest mi to potrzebne do wyświetlania średnich, jak, któryś wiersz ma #N/A to nie można policzyć średniej i w wyniku pojawia się #N/A. Sprawdzałem na kilku zakresach i działa dobrze.

Dane kopiuję z pliku sam do arkusza, zapisywane są w pliku tekstowym na karcie SD na mierniku. Nie importuje calego pliku tylko zaznaczam interesujące mnie kolumny (bo te czasem po zapisie przez miernik są poprzestawiane, poniewać jest czasem jakiś problem na stykach z termoparami) i wklejam je ręcznie, tak by mieć pewność, że zaznaczyłem to co mnie interesi.

Moderowany przez adamas_nt:

Załączać należy plik spakowany ZIP/RAR, co niniejszym pozwoliłem sobie poprawić.

A może zastosować dwa przebiegi? W pierwszym przekopiować właściwe dane do pustych kolumn, zaznaczyć ciągły Region i dopiero robić wykres. Tych błędów z pewnością nie będzie, mogą być inne.

Adorov napisał:

"DataChart"

Jeszcze nie zaglądałem do załącznika, ale dlaczego właściwie w "Sheets("DataChart")" usuwasz dane tylko z jednej kolumny ".Columns(1)" skoro końcówka kodu sugeruje, że może ich być więcej (?) - zmienna "counter":



Edycja:

Tylko na podstawie oględzin twojego kodu można by zrobić takie modyfikacje:

Ciekawe, czy zadziała ?

... a teraz trzeba by zająć się poprawkami do poprawek ... : ) ...

Dodano po 1 [godziny] 2 [minuty]:

Adorov napisał:

Dane kopiuję z pliku sam do arkusza, zapisywane są w pliku tekstowym na karcie SD na mierniku. Nie importuje calego pliku tylko zaznaczam interesujące mnie kolumny (bo te czasem po zapisie przez miernik są poprzestawiane, poniewać jest czasem jakiś problem na stykach z termoparami) i wklejam je ręcznie, tak by mieć pewność, że zaznaczyłem to co mnie interesi.

Mordęga ... jeśli tylko istnieje możliwość zidentyfikowania interesujących nas kolumn po imporcie, to po co robić to ręcznie ?
Proponowałbym jednak import automatyczny, potem identyfikacja kolumn i usunięcie zbędnych - Czy da się tak zrobić ?

Adorov napisał:

Obsługiwany akutalnie jest arkusz 24 i 1-wszy zakres dla dwóch czujników. Odpala się je za pomocą guzika w tabeli po prawej

Nie potrzeba do tego dwóch "różnych-takich samych" makr - wystarczy jedno z identyfikacją opisu na przycisku i w ten sposób różnicujące ewentualne zmiany danych.

Pytania:

Szczerze mówiąc, to przytłacza mnie ta ilość arkuszy, modułów i przycisków, które namnożyłeś ...
... a więc jeszcze raz, tłumaczenie całego procesu dla opornych na wiedzę, wynikające z braku piwa w chłodziarce ... : (
... a do sklepu to chyba z kilometr i 10 cm wysokości zaspy po drodze ...

1. Wstępnie kopiujesz jakieś kolumny z danymi (może być nawet po 13 tys. wierszy w takiej kolumnie) - z układu danych wychodzi mi, że kopiujesz takie dane do zakładki roboczej, w tym przypadku zakładki o nazwie "24", czy tak ?
2. Następnie w trakcie wykonywania makra na zakładce "24" "tworzone" są zakresy (tylko w kodzie) komórek z interesującymi nas danymi, które mają się odłożyć na wykresie, czy tak ?
3. Dodatkowo, dane te, pozyskane z wykresu, są odkopiowywane do zakładki "DataChart" na potrzeby średniej lub innej statystyki, czy tak ?


I teraz, czy nie lepiej to odwrócić:

1. Do jakiejś zakładki tymczasowej - tylko do tego - kopiować takie dane (po 13 tys. wierszy i ileś tam kolumn)
2. Usuwać tu zbędne kolumny
3. Z zakładki tej pozyskiwać dane (na podstawie kryteriów z tabelki w danej zakładce roboczej) do zakładek roboczych typu: zakładka "24", inne ...
4. Po wykonaniu w/w zakładkę tymczasową usuwać - w zeszycie pozostaną tylko wysortowane dane pod wykresy i statystykę - mniejsza objętość pliku i szybsze w nim operacje

lanzul dziękuję za odpowiedź... jestem początkującym vba-excelowcem i powiem szczerze, moje pierwsze makro, polegające na zaznaczaniu fragmentu zeszytu i przenoszenia go gdzie indziej powstało jakieś 2 tygodnie temu. Także dopiero się tego uczę, więc stąd toporność i kompletny beton mojego kodu, bo nie wiem jak np. dynamicznie zmieniać punkty odniesienia minimów i maksimów dla kolejnych guzików. To co powstało do tej pory to kilka godzin myślenia i kombinowania i do tego z pomocą gości ze stackoverflow... Po prostu uczę się sam na podstawie tutoriali internetowych itp.

Co do poprawek, które naniosłeś - niestety wysypuje się error w liniii, w której za wrs deklaroawne jest UBound...
Error o treści:
"Object variable or With block variable not set"

wrs = UBound(ActiveChart.SeriesCollection(1).Values) + 1
i = 1




Co do pytań...

Cytat:

Jeszcze nie zaglądałem do załącznika, ale dlaczego właściwie w "Sheets("DataChart")" usuwasz dane tylko z jednej kolumny ".Columns(1)" skoro końcówka kodu sugeruje, że może ich być więcej (?) - zmienna "counter":

Usuwam dla danego guzika konkretną kolumnę, potem zmieniam wartość w nawiasie (1) dla pierwszego guzika, potem dla drugiego zmieniam (2) itd. Czyli jak mam na razie te dwa przyciski to tak, żeby następny już wiedział, że ma kopiować dane do datachart ale o jedną kolumnę dalej niż poprzedni - robiłem to zmieniając wartość counter - kolejno 1 2 3 itd. Czy, żeby zakres 2 automatycznie przeniósł się do AH i AI 5 z AH i AI 3 dla zakresu 1 też zmieniałem ręcznie.

Cytat:

Mordęga ... jeśli tylko istnieje możliwość zidentyfikowania interesujących nas kolumn po imporcie, to po co robić to ręcznie ?
Proponowałbym jednak import automatyczny, potem identyfikacja kolumn i usunięcie zbędnych - Czy da się tak zrobić ?

Pewnie się da przenieść z pliku po rejestracji danych tak jak mówisz, jednak nie bawiłem się w to, chciałem najpierw uruchomić coś co jakkolwiek przyspieszy pracę.

Cytat:

Nie potrzeba do tego dwóch "różnych-takich samych" makr - wystarczy jedno z identyfikacją opisu na przycisku i w ten sposób różnicujące ewentualne zmiany danych.

Nie wiem jak identyfikować dla przycisku makro tak bym nie musiał go kopiować jak zły dla każdego buttonu.. ale to problem z tym co pisałem powyżej, nie wiem jak opisać w vba coś takiego, że "korzystaj z tego samego kodu, ale przesuń się dalej o liczbę wierszy/liczbę kolumn i wykonaj całą operację dla danych, które są dociągane z innego wiersza/kolumny".

Cytat:

Wstępnie kopiujesz jakieś kolumny z danymi (może być nawet po 13 tys. wierszy w takiej kolumnie) - z układu danych wychodzi mi, że kopiujesz takie dane do zakładki roboczej, w tym przypadku zakładki o nazwie "24", czy tak ?

Numer zakładki to nazwa komory - mamy je ponumerowane i to są ich nazwy, natomiast w nich powinny znajdować się wszystkie dane jak leci, które są skopiowane z odczytów. A DataChart miał być roboczym, w którym kopiowane są tylko interesujące mnie zakresy.

Cytat:

Następnie w trakcie wykonywania makra na zakładce "24" "tworzone" są zakresy (tylko w kodzie) komórek z interesującymi nas danymi, które mają się odłożyć na wykresie, czy tak ?

Tak i to z tym jest największy - jak dla mnie problem, ponieważ zbyt szeroki zakres wywala mi błąd, że jest to long.

Cytat:

Dodatkowo, dane te, pozyskane z wykresu, są odkopiowywane do zakładki "DataChart" na potrzeby średniej lub innej statystyki, czy tak ?

Tak, średnie wyliczane są w zeszycie 24 w kolumnie AJ, na podstawie DataChart. Pewnie da się zrobić tak, że tych wartości nie trzeba kopiować dalej, tylko z pamięci - to co makro zbiera do narysowania wykresu można również od razu wykorzystać do wyliczania średniej - ale nie wiem jak to napisać...

Z mojej strony to totalna amatorka, początkujacego słabiaka, który chce sobie uprościć trochę pracę, ale widzę, że na razie stworzyłem potwora, przez którego przebrnięcie jest mentalną torturą To nie moja wysoka rezystancja na wiedzę, ale brak podstaw, tylko skok na głęboką wodę... z nadzieją, że tak się da

Tak swoją drogą Bardzo Bardzo Ci dziękuję za chęć pomocy i wszystkie uwagi. Jestem pełen podziwu dla Twojej wiedzy - pozwoliłem sobie przeczytać kilka Twoich postów.

Pozdrawiam !

Adorov napisał:

Usuwam dla danego guzika konkretną kolumnę, potem zmieniam wartość w nawiasie (1) dla pierwszego guzika, potem dla drugiego zmieniam (2) itd. Czyli jak mam na razie te dwa przyciski to tak, żeby następny już wiedział, że ma kopiować dane do datachart ale o jedną kolumnę dalej niż poprzedni - robiłem to zmieniając wartość counter - kolejno 1 2 3 itd. Czy, żeby zakres 2 automatycznie przeniósł się do AH i AI 5 z AH i AI 3 dla zakresu 1 też zmieniałem ręcznie.

W arkuszu "24" masz miejsca na dane tylko na 2 kolumny "E" i "F".
1 kolumna to 1 czujnik ?
Nie będzie więcej potrzeba ?
Przycisków pod czujniki masz więcej, bo przygotowane aż na 4 czujniki.

Dodano po 4 [godziny] 6 [minuty]:

No cóż ... więc bez uściślania stanowisk ... sprawdź sobie:
Module..txt (5.93 kB)Musisz być zalogowany, aby pobrać ten załącznik.

Makro należy podpiąć pod każdy przycisk a'la "Oblicz zakres x czujnik y".
Jeśli tylko będzie poprawne nazewnictwo, to zadziała identyfikacja: przycisku, zakresu i czujnika ... ale ... tylko dwóch czujników, bowiem nie wiadomo, gdzie będą umieszczone dane dla innych - na razie wybór ograniczony jest do dwóch kolumn: "E" i "F".
Wstępnie usuń wszystkie dane z zakładki "DataChart".
Na liczenie średnich rzutuje kolejność tworzenia wykresów, czyli ułożenie danych w arkuszu "DataChart" - wynika to ze wzorów odwołujących się na sztywno do poszczególnych kolumn.

Dzień dobry,
właśnie wpadłem na forum...
nie wiem kim jesteś.... ale na sto procent jesteś visual-basicowym rzeźnikiem
to jest tak proste a tak genialne !!!!
Pomysł, z przesuwaniem komórek z temperaturami zakres*2 + 1... to jest tak mega oczywiste, a tak genilane !
Już nie mówiąc o tym, że kod sam sobie zaciąga nazwę nowego guzika z tekstu, który jest na nim wypisany, bez potrzeby tworzenia oddzielnego makra dla każdego oddzielnego guzika ! Nie wpadłbym na to jak to zapisać na tym poziomie na jakim jestem...

Co do dodawania kolejnych przypadków przy kotwiczeniu, bo dla dwóch komór będa też dane z dwóch dodatkowych termopar w większej ilości zakresów - dane będa gromadzone dodatkowo w kol. G i H, czy wystarczy, że dodam je w tym nawiasie gdzie deklarowane jest k ("G", "H") a następnie rozpiszę w przypadkach jako 1-3 i 1-4 i dodając komórki MXX/VXX jako początki dla nowych wykresów ?

Jesteś mistrzem !

Pozdrawiam !

Adorov napisał:

... dla dwóch komór będa też dane z dwóch dodatkowych termopar w większej ilości zakresów - dane będa gromadzone dodatkowo w kol. G i H, czy wystarczy, że dodam je w tym nawiasie gdzie deklarowane jest k ("G", "H") a następnie rozpiszę w przypadkach jako 1-3 i 1-4 i dodając komórki MXX/VXX jako początki dla nowych wykresów ?

Tak jak zauważyłeś, przy większej liczbie czujników, przy założeniu że jeden czujnik to dane w jednej kolumnie, trzeba zmienić zmienną "k" z:

na np.:

, wyznacza ona położenie (kolumnę) danych konkretnego czujnika (o numerze pobranym z opisu guzika).

Podobnie jest w przypadku zakresów, tutaj skorygować należy zawartość poniższej konstrukcji:

W tej chwili jest to przygotowane na 6 zakresów dla 2 czujników, tak jak było w "tabelce" pod wykresy.
Korekcji należy dokonać poprzez zmianę zapisu w nawiasach (najprostszy sposób):

np.: dla 4 czujników da się z tego wygospodarować bez większych zmian 3 zakresy:

Plus oczywiście zmiany w nazewnictwie przycisków oraz w tabelce pod kryteria.
Jeśli zakresów ma być więcej, to będą większe przeróbki związane z rozszerzeniem zakresu "tabeli" pod wykresy.

Raz jeszcze dziękuję za pomoc !
Mam małe pytanie, jak zmienić rozmiar tablicy ? Czy trzeba ją zmienić po ReDimie czy można ponownie zadeklarować jej rozmiar później ? Bo chyba gdy raz się zadeklaruje to nie ma możliwości jej zmiany. Poza tym czy jest potrzeba, żeby w przypadku tego kodu zadeklarowany szyk (tablica) była dwuwymiarowa ?
Bo finalnie chciałbym 6 zakresów i po 4 czujniki na zakres... i za cholerę nie umiem tego ugryźć. Dodałem kolejne przypadki, gdzie wykresy ustawiłem sobie jeden pod drugim - ale to i tak nic za specjalnie nie zminiło, bo tak jak pisałeś powyżej trzeba zmienić wielkośc tablicy, a kompletnie nie ogarniam dlaczego jest ona dwuwymiarowa.



Pozdrawiam

Adorov napisał:

a)... jak zmienić rozmiar tablicy ?...
b)... czy jest potrzeba, żeby ... (tablica) była dwuwymiarowa ?

a) Po co ? Tutaj się nic nie zmieniło, dalej jest to tablica dwuwymiarowa: ileś wierszy x 1 kolumna.
b) Nie ma, ale jest to wymiar "naturalny" danych z arkusza, nawet jak są tylko w wierszu jedne pod drugimi, to znajdują się jednak w jakiejś kolumnie - łatwiej operować takim układem.

Adorov napisał:

... Bo finalnie chciałbym 6 zakresów i po 4 czujniki na zakres

To akurat nie ma nic do tej tablicy.

Adorov napisał:

... jak pisałeś powyżej trzeba zmienić wielkośc tablicy ...

"Tabelce" ... pisałem "tabelce", nie "tablicy" ... : ) ... chodziło o ten "zakres pod wykresy" oraz "zakres pod temperatury" w komorze/wartości: min i maks, średnia.
Sprawdź:
Module..txt (6.61 kB)Musisz być zalogowany, aby pobrać ten załącznik.
Dostosowane do wykresów "jeden pod drugim".

Witam,
zgadza się, źle kombinowałem, ale udało mi się dojść do tego wczoraj w nocy i też namierzyłem błąd z deklaracją wykresów, że M90 i 95 się dublowało. Tylko zapomniałem przy nowym zakresie po mnożeniu odjąć 1 i mi przeskakiwał za daleko o jedną komórkę.

Dzięki wielkie za odpowiedź !

Udało się rozwiązać problem dzięki poprawkom - a w zasadzie napisaniu całego kodu od nowa, kolegi Lanzula. Rozwiązanie znajduje się w postach w niniejszym wątku.

Raz jeszcze dziękuję koledze Lanzulowi za poświęcony czas i anielską cierpliwość.