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?