Az elmúlt napokban valami influenzaszerű kórság megakadályozott benne, hogy posztoljak, nagyjából örültem, hogy élek - de ez már a múlt, folytassuk tanulmányainkat, közös gondolkodásunkat egy kis Excellel, azon belül is makrózzunk, hiszen általában minden, magát tapasztaltnak tartó Excel-felhasználó ezt a szintet szeretné megugrani, ha még nem tette meg korábban.

Ezúttal sem egy komplex kóddal fogunk foglalkozni, hiszen ahhoz még jópár VBA-poszton szeretnék végigmenni, hogy az alapdolgok menjenek mindenkinek - viszont mindenképpen hasznos dologról lesz szó, hiszen aki olyan Excel-fájlban dolgozik, amelyben akár sok tucat worksheet található, egy Summary (Összegző) oldalra mindenkinek szüksége van. Kell, hogy szüksége legyen, hiszen egyébként könnyen átláthatatlan és kaotikus fájlokat adhatunk ki a kezünkből, ami nem biztos, hogy jó fényt vet ránk.

Szóval ma egy nagyon rövid kis kódot fogunk készíteni, ami nem tesz majd mást, mint az első sheetünkre létrehoz egy Summary oldalt szépen linkelve a többi sheetünkre, amelyeken pedig az A1 cellába egy, a summary oldalra visszamutató linket helyez majd el.

Azzal most már nem fogok foglalkozni, hogy tudunk kódot felvinni VBA-ban, hiszen erről már írtam többször, úgyhogy onnan veszem fel a történet fonalát, hogy a VBA editorban vagyunk és a Workbook objektumunkban elkezdjük írni a kis kódunkat. Ennek első lépéseként egyrészt defináljuk Subunk nevét, majd létre kell hoznunk két változót, egyet Worksheet típussal, hiszen ide töltjük majd be munkalapjainkat, egy másikat pedig egyszerű Integer típusként, ezt fogjuk majd a ciklusunkban használni a sheetek számának nyomonkövetésére. Ennek első kiindulóértékeként állítsunk be egy egyest.

Ahogy már volt róla szó, a With...End With parancs a közötte felsorolt utasításokat hajtja végre ugyanarra az objektumra vonatkozóan, tehát jelentős mennyiségű gépelést megspórol nekünk, és ezt most itt is be fogjuk vetni, méghozzá az Me objektum felhasználásával. Mi is az az Me?

Az Me mindig arra az ún. szülő objektumra hivatkozik, amelyikben a kódunk csücsül, tehát jelen esetünkben az egész első munkalapra, azaz Me nagyjából a Worksheets("Sheet1"). utasítással lesz egyenlő. Ezt azért célszerű használni, mert ezzel egyrészt átláthatóbb lesz a kódunk, másrészt nem kell törődnünk többé holmi névváltozásokkal a későbbiekben.

Tehát most fogjuk elkészíteni az első sheetünkön a linkeket a többi sheetre. Mondjuk pakoljuk a linkeket az első oszlopba, tehát ClearContents utasítással ürítsük ki az első oszlopot, majd az első cellába írjuk be, hogy "Summary of worksheets", aztán legyen a cellánk neve Summary, hogy később már így tudjunk hivatkozni rá. Ahogy látni fogjuk, szépen használjuk a With...End With parancsot:

Ezután jön az a lépés, amelyben egy For Each...In Next ciklus bevetésével az első munkalapunktól kezdve végigmegyünk az összes munkalapunkon és megnöveljük r változónk értékét eggyel. Erre azért van szükségünk, mert ebből a változóból fogjuk kiolvasni azt, hogy hányadik sheeten vagyunk, hiszen a Summary sheeten annyiadik sorba kell majd a linket is bevinni az adott munkalapra. Célszerű beletenni egy egyszerű IF utasítást is, hogy ezt csak akkor csinálja meg, ha nem a Summary sheetünkről van szó, mert azzal eléggé nem a kívánt eredményt érnénk el:

Most van hátra még egy, esetleg két lépés, attól függően, hogy akarunk-e az aktuális sheetek első cellájába egy Summary sheetre mutató linket vagy sem. Mindenesetre mi most teszünk minden sheet A1-es cellájába egy visszafelé mutató linket, amelynek első lépéseként elnevezzük minden egyes sheetünk A1-es celláját egy konstans "Start_" szöveg és az adott worksheet index tulajdonsága segítségével, amely utóbbi az adott sheet helyzetét adja vissza sheetjeink halmazában.

Ezután pedig alkalmazzuk a Hyperlinks.Add metódust, amelynek fel kell töltenünk szépen a paramétereit:

Anchor - ez maga a a linkünk helye
Address - a linkünk címe
SubAddress - ide a belső hivatkozási címeket írjuk
ScreenTip - ha akarjuk, hogy az egér odahúzása esetén kiírjon valamit
TextToDisplay - ezt fogja megjeleníteni

Ergó a mi esetünkben:

With ws

.Range("A1").Name = "Start_" & ws.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Summary", TextToDisplay:="Vissza a Summary sheetre!"

Elnevezzük szépen az A1-es cellánkat minden sheeten (kivéve értelemszerűen a Summaryt, hiszen még mindig a fent megnyitott IF-ben vagyunk), majd itt létrehozunk egy linket, amely a Summary sheetre fog majd mutatni és szövegként megjeleníti a "Vissza a summary sheetre!" mondatot. Address kötelező paraméter, de most nincs rá szükségünk, ezért üresen visszük fel.

Utolsó lépésként pedig nagyjából ugyanezekkel az utasításokkal és paraméterekkel Me (azaz a Sheet1) megfelelő celláit is frissítjük a sheetek linkjeivel:

Me.Hyperlinks.Add Anchor:=Me.Cells(r, 1), Address:="", _
SubAddress:="Start_" & ws.Index, TextToDisplay:=ws.Name

Két dolgot vegyünk észre, egyrészt, hogy az r paramétert itt használjuk fel, hiszen így tudjuk megszámolni, hányadik sorba írja hányadik sheetet, másrészt, hogy a SubAddress paraméterünk az előző With ciklusban a sheetek A1-es cellájának adott név lesz majd.

Ezzel már csak az IF ciklusunkat kell bezárni és Next utasítással ugrunk a következő worksheetre.

Ha valaki nem szeretne gépelni, akkor itt a másolható kód:

Sub Summary_generator()
Dim ws As Worksheet
Dim r As Integer
r = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "Summary of worksheets"
.Cells(1, 1).Name = "Summary"
End With

For Each ws In Worksheets

If ws.Name <> Me.Name Then

r = r + 1

With ws

.Range("A1").Name = "Start_" & ws.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Summary", TextToDisplay:="Back to summary sheet!"

End With

Me.Hyperlinks.Add Anchor:=Me.Cells(r, 1), Address:="", _
SubAddress:="Start_" & ws.Index, TextToDisplay:=ws.Name

End If

Next ws

End Sub