Excel VBA : Dodanie formuły, która sumuje wartości z arkuszy

Sum O ile ten blog jest głównie na temat C#, to czasem pojawiają się wyzwania, które sprawdzają jak szybko jestem w stanie rozwiązać jakiś problem, gdy o danym języku programowania lub narzędziu nie wiele wiem.

Dzisiaj dostałem takie wyzwanie związane z Excelem. Czy można w Excelu napisać makro w VBA, które za Ciebie wygeneruje wyrażenie "SUMA=" i do niego doklei za Ciebie wszystkie arkusze, jakie masz w danym zeszycie?

Oczywiście, że się da. Po napisaniu Makra w VBA nawet zdałem sobie sprawę, że ten problem można rozwiązać zapewne lepiej przy pomocy menadżera nazw oraz potęgi klawisza SHIFT do zaznaczania wielu arkuszy.

Ja jednak ponieważ jestem programistą to pokaże Ci jak szybko od podstaw  można nauczyć się pisania makr VBA  Excelu, tak jak ja to zrobiłem w 15 minut.

Na początku w programie Excel musisz dodać do wstążki całą zakładkę Deweloper. Bez niej nic nie zrobisz.

Następnie z tego nowego pola we wstążce wybierz "Visual Basic".

W tym oknie teraz możesz deklarować nowe moduły. Do nich będziemy dodawać kod. Ten kod będziemy potem uruchamiać w specyficznym arkuszu w Excelu.

Dodawanie modułu wygląda tak

Pora napisać naszą pierwszą metodę w naszym module. Sub deklaruje nam funkcje o nazwie "GenerowanieSumy1".

Sub GenerowanieSumy1()
    Dim x, Formula
    Formula = "=SUM(" 'Formuła zaczyna się od =SUM('
    
    For x = 1 To (Sheets.Count - 1)
        Formula = Formula & Sheets(x).Name & "!A1," 'Dodaj Nazwę Arkusza i Komórkę i Przecinek'
    Next x
    
    Formula = Left(Formula, Len(Formula) - 1) & ")" 'Usuwanie przecinka i dodawanie nawiasów'
    Range("A1").Formula = Formula 'Gdzie chcesz umieścić tą formułę?'
End Sub

"Dim" oznacza deklarację zmiennej o danej nazwie. Potem w pętli FOR przejdę po wszystkich arkuszach oprócz ostatniego stąd -1 i wygeneruje się odpowiedni ciąg do formuły. Będzie to wyglądać ostatecznie tak:

=SUMA(Arkusz1!A1;Arkusz2!A1;Arkusz3!A1;Arkusz4!A1)

Gdzie przykładowo "akrusz5" będzie arkuszem ostatnim i do niego będę chciał dodać tą wygenerowaną formułę przez VBA. Na razie napisałem na sztywno komórkę "A1". Zobaczymy co możemy z tym zrobić dalej.

Jak tej metody użyć.

Wchodzimy na "arkusz5" czyli nasz ostatni arkusz i wciskamy ten guzik.

Teraz pojawi Ci się okno ze wszystkimi dostępnymi makrami. Ja mam ich więcej, ponieważ już dodałem do swojego modułu kolejne metody. 

Oto jak nasze makro działa w praktyce.

Oczywiście jest jeden problem z naszym makrem.

Na sztywno do niego dodaliśmy komórkę A1.

Co, jeśli chcielibyśmy tak generować sumy dla każdej komórki?

Możemy przerobić naszą metodę VBA tak, aby przyjmowała ona informacje o komórce jako napis String

Sub GenerowanieSumyZParametrem(CELL As String)
Dim x, Formula
Formula = "=SUM(" 'Formuła zaczyna się od =SUM('
For x = 1 To (Sheets.Count - 1)
    Formula = Formula & Sheets(x).Name & "!" & CELL & "," 'Dodaj Nazwę Arkusza i Komórkę i Przecinek'
Next x
Formula = Left(Formula, Len(Formula) - 1) & ")" 'Usuwanie przecinka i dodawanie nawiasów'
Range(CELL).Formula = Formula 'Gdzie chcesz umieścić tą formułę?'
End Sub

Niestety nie ma możliwości uruchomienia makra z parametrem bez tworzenia do niego jakieś formatki z przyciskiem.

Tworzenie też takich funkcji per komórka, też mija się z celem. Warto zaznaczyć, że jak uruchamiasz funkcje wewnątrz innej funkcji to najpierw musisz podać nazwę modułu tej funkcji. U mnie moduł nazywa się "GenerowanieSumy". 

Przeciwnym wypadku dostaniesz błąd : Expected Variable or Procedure, not Module.

Sub GenerowanieSumyA2()
    GenerowanieSumy.GenerowanieSumyZParametrem("A2")
End Sub

Sub GenerowanieSumyA1()
    Call GenerowanieSumy.GenerowanieSumyZParametrem(CELL:="A1")
End Sub

Na szczęście do problemu można podejść bardzo prymitywnie, ale też i skutecznie.

Przykładowo chce mieć sumy wartości komórek od C3 do N40. Jak to zrobić? Utworzyłem sobie tablice "CellsName", która zawiera nazwy komórek od C do N. Chociaż zaraz Ci pokaże jak to zrobić bez tworzenia takiej tablic.

Sub GenerowanieSumyC3toN40()
    
    Dim CellsName(1 To 12) As String
    CellsName(1) = "C"
    CellsName(2) = "D"
    CellsName(3) = "E"
    CellsName(4) = "F"
    CellsName(5) = "G"
    CellsName(6) = "H"
    CellsName(7) = "I"
    CellsName(8) = "J"
    CellsName(9) = "K"
    CellsName(10) = "L"
    CellsName(11) = "M"
    CellsName(12) = "N"
       
    For x = 3 To 40
        For y = 1 To 12
             Dim CELL As String
             CELL = CellsName(y) & x
             
             Call GenerowanieSumy.GenerowanieSumyZParametrem(CELL)

        Next y
    Next x

End Sub

Potem wykonuje pętle w pętli i tak dla każdej komórki od "C3 do N40" wykonam funkcję "GenerowanieSumyZParametrem".

Oto rezultat działanie tej metody

Jak widzisz funkcje nie są skomplikowane. Nawet taki prymitywnym podejściem można rozwiązać skomplikowany problem.

Visual Basic for Applications nie jest aż taki straszny

Czy można napisać ten kod inaczej? Mały tutorial. Oto jak możesz ustawić wartość komórki A2. 

Cells(2,1).Value = 1
Range("A2").Value = 1

W metodzie Cells w drugim parametrze podajesz kolumnę w formacie cyfrowej. Te dwie linki kodu robią dokładnie to samo

Cells("A1").Formula = Formula 'Gdzie chcesz umieścić tą formułę?'
Cells(1, 1).Formula = Formula 'Gdzie chcesz umieścić tą formułę?'

Wiedząc to możemy teraz zmodyfikować funkcje, która będzie generować te formuły. Jednakże do tego problemu możemy podejść jeszcze inaczej bez modyfikowania istniejącej już funkcji, która tworzy nam formułę

Do szczęścia potrzebujemy pomocniczej funkcję, która zamieni nam zapis cyfrowy kolumny na napis alfabetyczny.

Function Col_Letter(lngCol) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Dzięki temu teraz stworzyć lepszą wersję metody, która zadziała dla komórek od C3 do N40. 

Sub GenerowanieSumy2C3toN40()
    
    For x = 3 To 40
        For y = 1 To 12
             ColumnLetter = GenerowanieSumy.Col_Letter(y)
            
             Dim CELL As String
             CELL = ColumnLetter & CStr(x)
             Call GenerowanieSumy.GenerowanieSumyWithParameter(CELL)

        Next y
    Next x

End Sub

To wszystko.