Als julliie de vorige tips en trucs over het zelf schrijven van een vba functie ook hebben gelezen en de smaak te pakken hebben dan gaan we nu wat functies bekijken die ik tegen ben gekomen waar we de werkbladnamen in gebruiken. Het voorbeeldbestand met de zelfgemaakte vba functies kun je hier downloaden!
Stappen:
Om de functies die we in ieder werkblad aan kunnen roepen toe te voegen kiezen we na het openen van een Excel / maken van een nieuwe Excel voor ALT-F11 en klikken in de menubalk bovenin op 'Invoegen - Module'. Daarna 'Kopiëren' en 'Plakken' we de functies hieronder die we willen gebruiken met uiteindelijk het volgende resultaat:
In het voorbeeldbestand hebben we 12 tabbladen voor de maanden in een jaar.
Huidige werkbladnaam
Geef de naam van het huidige werkblad:
Function HuidigeWerkbladNaam() As String
Application.Volatile True
HuidigeWerkbladNaam = Application.Caller.Parent.Name
End Function
Eerste werkbladnaam
Geef de naam van het eerste werkblad:
Function EersteWerkbladNaam() As String
Application.Volatile True
With Application.Caller.Parent.Parent.Worksheets
EersteWerkbladNaam = .Item(1).Name
End With
End Function
Deze functie kunnen we nu ook heel eenvoudig in een andere functie gebruiken. Als voorbeeld gebruiken we de functie indirect waarmee we door een combinatie te maken tussen de nieuwe functie EersteWerkbladNaam() en de celverwijzing A3 eigenlijk zeggen 'Geef me de waarde uit cel A3 van het eerste werkblad'!
=INDIRECT(EersteWerkbladNaam() & "!A3")
Huidige positie werkblad
Geef het nummer / de positie van het huidige werkblad
Function HuidigeWerkpladPositie() As IntegerApplication.Volatile True
HuidigeWerkpladPositie = Application.Caller.Parent.Index
End Function
Toon aantal werkbladen
Deze functie geeft het totaal aantal werkbladen aan wat er in een Excel file zit
Function AantalWerkbladen() As IntegerApplication.Volatile True
AantalWerkbladen = Application.Caller.Parent.Parent.Worksheets.Count
End Function
Laatste werkbladnaam
Toon de naam van het laatste werkblad
Function LaaststeWerkbladNaam() As StringApplication.Volatile True
With Application.Caller.Parent.Parent.Worksheets
LaaststeWerkbladNaam = .Item(.Count).Name
End With
End Function
Toon de naam van het vorige werkblad t.o.v. het huidige werkblad
Dit is een handige functie waarin we de naam van het vorige werkblad ophalen, staan we op het eerste werkblad (januari in het testbestand), dan toont de formule de maand december!
Function VorigeWerkbladNaam(Optional ByVal WS As Worksheet = Nothing) As StringApplication.Volatile True
Dim S As String
Dim Q As String
If IsObject(Application.Caller) = True Then
Set WS = Application.Caller.Worksheet
If WS.Index = 1 Then
With Application.Caller.Worksheet.Parent.Worksheets
Set WS = .Item(.Count)
End With
Else
Set WS = WS.Previous
End If
If InStr(1, WS.Name, " ", vbBinaryCompare) > 0 Then
Q = "'"
Else
Q = vbNullString
End If
Else
If WS Is Nothing Then
Set WS = ActiveSheet
End If
If WS.Index = 1 Then
With WS.Parent.Worksheets
Set WS = .Item(.Count)
End With
Else
Set WS = WS.Previous
End If
Q = vbNullString
End If
VorigeWerkbladNaam = Q & WS.Name & Q
End Function
Ook deze functie kunnen we eenvoudig in een andere functie gebruiken. Als voorbeeld gebruiken we nogmaals de functie indirect waarmee we door een combinatie te maken tussen de nieuwe functie EersteWerkbladNaam() en de celverwijzing A3 eigenlijk zeggen 'Geef me de waarde uit cel A3 van het vorige werkblad'!
=INDIRECT(VorigeWerkbladNaam() & "!A3")
Volgende werkblad naam
Function VolgendeWerkbladNaam(Optional WS As Worksheet = Nothing) As StringApplication.Volatile True
Dim S As String
Dim Q As String
If IsObject(Application.Caller) = True Then
Set WS = Application.Caller.Worksheet
If WS.Index = WS.Parent.Sheets.Count Then
With Application.Caller.Worksheet.Parent.Worksheets
Set WS = .Item(1)
End With
Else
Set WS = WS.Next
End If
If InStr(1, WS.Name, " ", vbBinaryCompare) > 0 Then
Q = "'"
Else
Q = vbNullString
End If
Else
If WS Is Nothing Then
Set WS = ActiveSheet
End If
If WS.Index = WS.Parent.Worksheets.Count Then
With WS.Parent.Worksheets
Set WS = .Item(1)
End With
Else
Set WS = WS.Next
End If
Q = vbNullString
End If
VolgendeWerkbladNaam = Q & WS.Name & Q
End Function
=INDIRECT(VolgendWerkbladNaam() & "!A3" geeft ons ook weer de waarde van A3 op het volgende werkblad.
Een andere manier om een celwaarde van een vorig werkblad te krijgen is m.b.v. de volgende functie:
Function PakCelOpVorigWerkblad(Addr As String) As VariantApplication.Volatile True
With Application.Caller.Parent
If .Index = 1 Then
RefOnPrevSheet = _
.Parent.Worksheets(.Parent.Worksheets.Count).Range(Addr).Value
Else
PakCelOpVorigWerkblad = .Previous.Range(Addr).Value
End If
End With
End Function
We geven als parameter een tekststring mee, bijvoorbeeld: =PakCelOpVorigWerkblad("A6"). Let op de dubbele quotes om A6!
Ook hier geldt weer, staan we op het eerste werkblad, pak dan de waarde uit de cel waar naar verwezen wordt op het laatste werkblad.
Wat opvalt is dat iedere functie Application.Volatile True heeft staan, dit betekent dat de functie niet alleen herberekend moet worden als de input parameter van waarde verandert, maar dat Excel moet herberekenen bij iedere wijziging van het werkblad.