# Shortening Custom Document Property additions [closed]

## Context

I have blocks of if statements that check if a certain cell contains a particular string and to add to the counter of the respective custom property. Originally I was using global variables, but those are zeroed out when excel crashes. I learned of custom document properties and their non-volatility and made the switch. However, adding to the counter is a long, barely readable statement.

With global variables I had a statement like:

If cell = "Heavy Duty" Then numHD = numHD + 4


But now statements look like:

If cell = "Heavy Duty" Then ThisWorkbook.CustomDocumentProperties("numHD").Value = ThisWorkbook.CustomDocumentProperties("numHD").Value + 4


It's immensely longer and with 27 other statements doing the same thing for other strings, the code becomes taxing to read.

## What I would like

Is there a way that I can shorten the custom document property edits to look more in line with what I had before with global variables? I had tried setting up a global variable as a properties type and setting to the property, then just using the variable in the actual sub:

Public numHD As Properties
Set numHD = ThisWorkbook.CustomDocumentProperties("numHD")

Sub count()
If cell = "Heavy Duty" Then numHD = numHD + 4
End Sub


But Set cannot be used outside of a sub or function apparently. Any advice?

## closed as off-topic by Toby Speight, IEatBagels, AJNeufeld, vnp, HeslacherFeb 15 at 11:22

This question appears to be off-topic. The users who voted to close gave this specific reason:

• "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – Toby Speight, IEatBagels, AJNeufeld, vnp, Heslacher
If this question can be reworded to fit the rules in the help center, please edit the question.

Writing to a document property is a concern in its own right, and thus deserves its own scope. Write a procedure that's responsible only for this!

Public Sub IncrementHeavyDutyCount(ByVal increment As Long)
With ThisWorkbook.CustomDocumentProperties.Item("numHD")
.Value = .Value + increment
End With
End Sub


And now you can increment your counter with a simple, self-explanatory procedure call:

If cell = "Heavy Duty" Then IncrementHeavyDutyCount 4

• Can you elaborate on what makes writing to a document property a concern? Maybe I'm misunderstanding something. All of my research, which admittedly was quite some time ago, pointed to this being the best route. – Lux Claridge Feb 13 at 15:55
• @LuxClaridge rather hard to explain when all context I have is a single line of code from your project, but as a general rule of thumb, adhering to the Single Responsibility Principle means writing small procedures that are responsible for as little as possible - a procedure that does one thing, only has one reason to fail. In this case, "index out of range" if there's no "numHD" custom property in ThisWorkbook. – Mathieu Guindon Feb 13 at 17:09
• My own approach when wanting to "save" application-specific values within a workbook is to create an "ApplicationData" worksheet and store all my data there. Quite often, I'll make that worksheet hidden (and sometimes veryhidden). Data on a worksheet is automatically persistent storage in this respect. So while in the distant past I researched the possibility of using document properties, I've never had the need for them. – PeterT Feb 13 at 18:50
• @MathieuGuindon Oh now I understand. Thanks! – Lux Claridge Feb 14 at 14:06