Excel 2003 - Makro na určenie vzťahu medzi susednými bunkami (rozdiel hodnôt)

VBA Editor spustíš cez Tools / Macro / Visual Basic Editor (Alt+F11). Po dvojitom kliknutí na meno listu zošita Excel, v ktorom chceš mať makro, môžeš začať písať samotný program. Obslužné makro pre nejakú udalosť vytvoríš buď výberom z dvoch komboboxov, ktoré sú nad miestom pre kód makra (napr. v prvom vyberieš Worksheet, v druhom Change) alebo priamo zapíšeš (Private Sub Worksheet_Change(ByVal Target As Range)).

V argumente Target je rozsah buniek, v ktorých nastala zmena. Stačí skontrolovať, či sa medzi nimi nachádza niektorá z buniek, po zmene ktorej chceš prepočítať nejaké hodnoty, a potom už len výsledok zapísať do zošita. Treba myslieť aj na to, aby sa funkcia nevolala zbytočne (alebo donekonečna) pri zmene bunky samotným makrom.

Napríklad jednoduché makro, ktoré po zmene bunky doplní o riadok nižšie hodnotu zväčšenú o jednotku, by mohlo vyzerať takto:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Application.EnableEvents = False
    
    Target.Offset(1, 0) = Target.Value + 1
    
ErrorHandler:
    Application.EnableEvents = True
End Sub

1. Ako zistím z Target o ktorý stĺpec sa jedná (B, C)?

2. Ako zariadim, aby sa nevolala metóda rekurzívne po zmene bunky? Nejakým globálnym parametrom?

if Zmena = 0 then
  Zmena = 1
  urobim_zmenu
  Zmena = 0
end if

Ak sa to dá takto, ako si zadefinujem globálny parameter? Teda niečo ako iniciálizácia počiatočnou hodnotou?

Číslo riadka a stĺpca ľavej hornej bunky rozsahu Target zistíš pomocou Target.Row a Target.Column. Predpokladám, že po stlačení klávesy F1 sa dozvieš viac (aj klávesa F2 je zaujímavá). Asi budeš chcieť prebehnúť všetky bunky v rozsahu Target pomocou konštrukcie For Each.

Ukážka toho, ako zabrániť rekurzívnemu volaniu obsluhy udalosti, je v mojom predchádzajúcom príspevku - nastavením Application.EnableEvents. Tu musíš myslieť na to, aby si túto vlastnosť na konci nastavil na True, pretože inak by si domakroval. Preto je tam použité OnError, aby sa to spamätalo aj v prípade nejakej chyby.

Dá sa to robiť aj cez globálnu premennú, ale celý program je potom o trochu neprehľadnejší. Globálnu premennú si vytvoríš rovnako, ako vo všetkých iných programovacích jazykoch, ktoré globálne premenné podporujú - umiestniš deklaráciu premennej mimo definíciu funkcie/procedúry (Dim Zmena As Boolean).

Tu je výsledok, jedná sa o stĺpce O a P:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Application.EnableEvents = False
    
    If Target.Row = "O" Then
        Target.Offset(0, 1) = Target.Offset(-1, 0) - Target.Value
    End If
    If Target.Row = "P" Then
        Target.Offset(0, -1) = Target.Offset(-1, -1) - Target.Value
    End If
    
ErrorHandler:
    Application.EnableEvents = True
End Sub

Edit: Zabudol som napísať, že to nefunguje. Niečo mi tam zrejme chýba.

Target.Row je číslo riadka. A nechceš vlastne Target.Column? To je pre zmenu číslo stĺpca.

BTW: Target je rozsah, takže to kľudne môže byť viacero buniek. Preto by som ten rozsah upravil pomocou metódy Resize na jednu bunku.

Opravil som to. Ja mám večný bordel v Row a Column. Kedysi to bol problém s Width a Height. Teraz je výsledný kód tento:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Application.EnableEvents = False
    
    If Target.Column = 15 Then
        Target.Offset(0, 1) = Target.Offset(-1, 0) - Target.Value
    End If
    If Target.Column = 16 Then
        Target.Offset(0, -1) = Target.Offset(-1, -1) - Target.Value
    End If
    
ErrorHandler:
    Application.EnableEvents = True
End Sub

Pracuje to správne, len nerozumiem ako použiť Resize. Pozerám aj do helpu, ale nechápem tomu. Ide o to, že naraz zmením viac buniek, že? Dobre, ale ako s tým súvisí Resize? Ako zistí, ktorú bunku mi má vybrať?

Edit: Keď zmením viac buniek naraz (kopírovanie a tak, tak sa to vykonať nemusí, ale ako to zariadim? Medzitým som ešte pridal podmienku, že Row musí byť viac ako 3, inak sa to nevykoná.

Pri spracovaní zmeny nejakého rozsahu sa často zabúda na to, že sa môže meniť viacero buniek naraz, t.j. rozsah Target neobsahuje len jednu bunku. Riešení môže byť viacero. Môže sa napríklad spracovať každá bunka rozsahu pomocou cyklu For Each, čo však môže robiť problém pri zmene veľkého rozsahu buniek. Alebo sa môže spracovať len jedna bunka (ľavá horná) celého rozsahu, čo môže občas vadiť.

Keby si chcel spracovávať zmenu v každej bunke, môžeš to napísať nejako takto:

For Each cell in Target.Cells
    ... (tu už pracuješ s premennou cell)
Next cell

Ak by si chcel spracovávať len prvú bunku, tak by to mohlo vyzerať takto:

Set cell = Target.Resize(1, 1)
... (tu už pracuješ s premennou cell)

Zistiť, či bolo zmenených viacero buniek naraz, je jednoduché:

If Target.Cells.Count > 1 Then

Tvoj program by som prepísal asi do takéhoto tvaru:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Application.EnableEvents = False

    Set intersection = Intersect(Target.Cells, Range("O:P"))

    If Not intersection Is Nothing Then
        For Each cell In intersection
            If cell.Column = 15 Then cell.Offset(0, 1) = cell.Offset(-1, 0) - cell.Value
            If cell.Column = 16 Then cell.Offset(0, -1) = cell.Offset(-1, -1) - cell.Value
        Next
    End If

ErrorHandler:
    Application.EnableEvents = True
End Sub

Veľmi pekne ďakujem. Veľmi si mi pomohol. Myslím, že na mnohé veci vôbec nebudem potrebovať Delphi, stačí sa pohrať s Excelom.

Zaujímalo by ma, či by niečo takéto fungovalo v OpenOffice?

Na co Makro ?
Stačí do C2 dát "=B1-B2", pak to C2 chytit za růžek a stáhnout dolů,
kam až potřebuješ. Vzorec se automaticky mění.

Vzorec nestačí, pretože tú hodnotu v stĺpci C nechce dostať vždy len výpočtom, ale niekedy tam chce dosadiť konkrétnu hodnotu, z ktorej sa spätne vypočíta hodnota v stĺpci B.

Aha.