Ontbrekende tabbladen in array zetten en vervolgens laten zien in een Range (VBA2010)

[ErikdV]

Ik heb een aantal genummerde tabbladen, meestal tussen de 10 en 20.
Normaal gesproken zijn deze genummerd van 1 tot x (naam tabblad)
Elk tabblad heeft dezelfde structuur en layout en bevat dezelfde soort gegevens.

Op elk tabblad zijn ook namen gedefinieerd.
Om direct te kunnen zien op welk genummerd tabblad de gedefinieerde naam betrekking heeft heb ik als laatste karakter van de naam het nummer van het tabblad gebruikt (bijv. tot9)
Deze namen worden weer gebruikt in een tabblad TOTAAL.

Soms moeten er genummerde tabbladen toegevoegd of verwijderd worden; dit kan vooraan, tussenin of aan het eind.
Soms worden ook de nummers van de tabbladen gewijzigd.

Het wijzigen van de tabbladen heeft als gevolg:
1. Verwijderen: Namen verwijzen niet meer naar een bestaand tabblad
2. Van naam wijzigen: Namen verwijzen naar een verkeerd tabblad
3. Invoegen: Er bestaan nog geen namen voor het tabblad

Ik wil dit automatiseren, zodat ik in een keer de namen kan aanpassen.
Hiervoor heb ik al een VBA-code gemaakt die het volgende doet:


1. tabblad met het hoogste nummer bepalen
2. Nagaan voor alle tabbladen met een nummer lager dan het hoogste nummer of het bestaat
3. Niet bestaande nummers in een array zetten
4. Array afdrukken in een array op een nieuw tabblad

Het lijkt erop dat 1 t/m 3 goed gaat, maar dat 4 problemen geeft:
De grootte van de array klopt wel, maar het eerste element heeft de waarde 0 en de laatste ontbreekt.
Als ik dus 3 ontbrekende tabbladen heb met nummer 2,3, en 5 dan zie ik in de range het volgende:
0
2
3

Klopt het dat de array correct is en wat gaat erfout bij het omzetten naar een range?

Sub BepaalOntbrekendeTabbladen()
Dim aantal As Integer 'aantal tabbladen
Dim s As Integer 'volgnummer van het tabblad
Dim se As Integer 'volgnummer van laatste genummerde tabblad
Dim Shtname As String 'naam van het tabblad
Dim hoogste As Integer 'naam van het tabblad met het hoogste nummer
Dim aantalNB As Integer 'aantal niet bestaande genummerde tabbladen
Dim TabbladNB() As Integer 'Dynamische Array

'1. Hoogste bepalen
hoogste = 1
aantal = AantalBladen()
se = AantalBladen() - 1 'laatste genummerde tabblad

'Tabblad met het hoogste nummer als naam bepalen
For s = 2 To se
Shtname = Sheets(s).Name
If Val(Shtname) > hoogste Then
hoogste = Val(Shtname)
End If
Next

'2. Bepalen welk tabblad bestaat en kleiner is als hoogste
For s = 1 To hoogste

If s < 10 Then p = 1 '1 tot 9 tabbladen
If s > 9 And s < 100 Then p = 2 '10 tabbladen of meer -> p=2
If s > 99 Then p = 3 '100 tabbladen of meer
Shtname = Right(s, p) 'Nummer als string in de variabele zettenlaatste character(s) wordt omgezet in een string

If Not SheetExists((Shtname)) Then
MsgBox "Tabblad bestaat niet: " & Shtname
aantalNB = aantalNB + 1
'3. Array maken
ReDim Preserve TabbladNB(aantalNB) 'Array aanpassen
TabbladNB(aantalNB) = Shtname

Debug.Print "Naam tabblad:"; Shtname
Debug.Print "grootte array:"; aantalNB
Debug.Print "waarde array:"; TabbladNB(aantalNB)
End If

Next
MsgBox "Het aantal genummerde tabbladen dat niet bestaat is " & aantalNB
ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count) 'nieuw tabblad invoegen aan het eind

'4. range vullen
With Range("A1")
.Resize(aantalNB, 1).Name = "nietbestaandetabbladen"
End With
Range("nietbestaandetabbladen").Value = Application.WorksheetFunction.Transpose(TabbladNB)

End Sub

Function SheetExists(Shtname As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet
If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
Set sht = wb.Sheets(Shtname)
On Error GoTo 0
SheetExists = Not sht Is Nothing
'MsgBox "Tabblad bestaat niet:" & shtName
End Function

 

[wampier]

arrays beginnen standaard met "0" als laagste member, niet 1 (tenzij je dat aanzet via de "OPTION BASE" functie)

een redim array (3) bestaat dus uit:

array(0)
array(1)
array(2)
array(3)

Je vult item (0) niet, maar wel item 1,2,3. Vervolgens toon je (via resize) de eerste 3 items, wat voor VBA items 0,1,2 zijn.

 

[ErikdV]

Klinkt onlogisch dat een array(3) uit 4 items bestaat, maar zoals je het uitlegt klopt het wel.

Ik heb de OPTION BASE=1 aangezet voor de gehele module en de array wordt met de juiste waarden weergegeven in de range.

Ik moet nu alleen niet vergeten, dat ik hierdoor voor alle andere subs in de module dezelfde option base heb...

 

[wampier]

Het is ook onlogisch, maar is een overblijfsel van het mergen van VB6 met scripts. Een ander opties is natuurlijk om gewoon te beginnen met tellen vanaf 0 of niet resize te gebruiken om de weergave te doen.