excel-vba
CustomDocumentProperties in de praktijk
Zoeken…
Invoering
Het gebruik van CustomDocumentProperties (CDP's) is een goede methode om door de gebruiker gedefinieerde waarden op een relatief veilige manier in hetzelfde werkboek op te slaan, maar om te voorkomen dat gerelateerde celwaarden eenvoudig in een onbeschermd werkblad worden weergegeven *).
Opmerking: CDP's vertegenwoordigen een afzonderlijke verzameling die vergelijkbaar is met BuiltInDocumentProperties, maar laten toe om door de gebruiker gedefinieerde eigennamen te maken in plaats van een vaste verzameling.
*) Als alternatief kunt u ook waarden invoeren in een verborgen of "zeer verborgen" werkmap.
Nieuwe factuurnummers organiseren
Een factuurnummer verhogen en de waarde opslaan, is een veel voorkomende taak. Het gebruik van CustomDocumentProperties (CDP's) is een goede methode om dergelijke nummers op een relatief veilige manier in hetzelfde werkboek op te slaan, maar om te voorkomen dat gerelateerde celwaarden eenvoudig in een onbeschermd werkblad worden weergegeven.
Aanvullende tip:
Als alternatief kunt u ook waarden invoeren in een verborgen werkblad of zelfs een zogenaamd "zeer verborgen" werkblad (zie xlVeryHidden Sheets gebruiken . Natuurlijk is het mogelijk om gegevens ook op te slaan in externe bestanden (bijv. Ini-bestand, csv of een ander type) of het register.
Voorbeeld inhoud :
Het onderstaande voorbeeld laat zien
- een functie NextInvoiceNo die het volgende factuurnummer instelt en retourneert,
- een procedure DeleteInvoiceNo, die de factuur-CDP volledig verwijdert, evenals
- een procedure showAllCDP's met de volledige CDP's-verzameling met alle namen. Als u VBA niet gebruikt, kunt u ze ook vermelden via de informatie in de werkmap: Info | Eigenschappen [DropDown:] | Geavanceerde eigenschappen | Op maat
U kunt het volgende factuurnummer (laatste niemand plus) krijgen en instellen door eenvoudigweg de bovengenoemde functie te bellen en een tekenreekswaarde te retourneren om het toevoegen van voorvoegsels te vergemakkelijken. "InvoiceNo" wordt impliciet gebruikt als CDP-naam in alle procedures.
Dim sNumber As String
sNumber = NextInvoiceNo ()
Voorbeeld code:
Option Explicit
Sub Test()
Dim sNumber As String
sNumber = NextInvoiceNo()
MsgBox "New Invoice No: " & sNumber, vbInformation, "New Invoice Number"
End Sub
Function NextInvoiceNo() As String
' Purpose: a) Set Custom Document Property (CDP) "InvoiceNo" if not yet existing
' b) Increment CDP value and return new value as string
' Declarations
Dim prop As Object
Dim ret As String
Dim wb As Workbook
' Set workbook and CDPs
Set wb = ThisWorkbook
Set prop = wb.CustomDocumentProperties
' -------------------------------------------------------
' Generate new CDP "InvoiceNo" if not yet existing
' -------------------------------------------------------
If Not CDPExists("InvoiceNo") Then
' set temporary starting value "0"
prop.Add "InvoiceNo", False, msoPropertyTypeString, "0"
End If
' --------------------------------------------------------
' Increment invoice no and return function value as string
' --------------------------------------------------------
ret = Format(Val(prop("InvoiceNo")) + 1, "0")
' a) Set CDP "InvoiceNo" = ret
prop("InvoiceNo").value = ret
' b) Return function value
NextInvoiceNo = ret
End Function
Private Function CDPExists(sCDPName As String) As Boolean
' Purpose: return True if custom document property (CDP) exists
' Method: loop thru CustomDocumentProperties collection and check if name parameter exists
' Site: cf. http://stackoverflow.com/questions/23917977/alternatives-to-public-variables-in-vba/23918236#23918236
' vgl.: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_other/using-customdocumentproperties-with-vba/91ef15eb-b089-4c9b-a8a7-1685d073fb9f
' Declarations
Dim cdp As Variant ' element of CustomDocumentProperties Collection
Dim boo As Boolean ' boolean value showing element exists
For Each cdp In ThisWorkbook.CustomDocumentProperties
If LCase(cdp.Name) = LCase(sCDPName) Then
boo = True ' heureka
Exit For ' exit loop
End If
Next
CDPExists = boo ' return value to function
End Function
Sub DeleteInvoiceNo()
' Declarations
Dim wb As Workbook
Dim prop As Object
' Set workbook and CDPs
Set wb = ThisWorkbook
Set prop = wb.CustomDocumentProperties
' ----------------------
' Delete CDP "InvoiceNo"
' ----------------------
If CDPExists("InvoiceNo") Then
prop("InvoiceNo").Delete
End If
Einde Sub
Sub showAllCDPs()
' Purpose: Show all CustomDocumentProperties (CDP) and values (if set)
' Declarations
Dim wb As Workbook
Dim cdp As Object
Dim i As Integer
Dim maxi As Integer
Dim s As String
' Set workbook and CDPs
Set wb = ThisWorkbook
Set cdp = wb.CustomDocumentProperties
' Loop thru CDP getting name and value
maxi = cdp.Count
For i = 1 To maxi
On Error Resume Next ' necessary in case of unset value
s = s & Chr(i + 96) & ") " & _
cdp(i).Name & "=" & cdp(i).value & vbCr
Next i
' Show result string
Debug.Print s
End Sub