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, Heslacher Feb 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
  • \$\begingroup\$ 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. \$\endgroup\$ – Lux Claridge Feb 13 at 15:55
  • 1
    \$\begingroup\$ @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. \$\endgroup\$ – Mathieu Guindon Feb 13 at 17:09
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – PeterT Feb 13 at 18:50
  • \$\begingroup\$ @MathieuGuindon Oh now I understand. Thanks! \$\endgroup\$ – Lux Claridge Feb 14 at 14:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.