Accessing the value of a custom property gives an "Out of memory" error when the value is null

Question

I am trying to create a custom property in excel and then get its value. This is normal when I do not use an empty string, i.e. "" . When I use an empty string, I get this error:

 Run-time error '7': Out of memory 

Here is the code I'm using:

 Sub proptest() Dim cprop As CustomProperty Dim sht As Worksheet Set sht = ThisWorkbook.Sheets("control") sht.CustomProperties.Add "path", "" For Each cprop In ThisWorkbook.Sheets("control").CustomProperties If cprop.Name = "path" Then Debug.Print cprop.Value End If Next End Sub 

The code does not work Debug.Print cprop.value . Should I set the property "" ?

Answer 1

I think it’s clear from the comments and response from Daniel Dusek that this cannot be done. The property must contain at least 1 character, an empty string is simply not allowed and will throw an error when calling .Value.

So, you Add this property with a length of 1 or more string , and you Delete property again when no actual value is assigned to it.

Answer 2

It works with vbNullChar , sample:

 Sub proptest() Dim sht As Worksheet Set sht = ThisWorkbook.Sheets("control") ' On Error Resume Next sht.CustomProperties.Item(1).Delete ' On Error GoTo 0 Dim pathValue As Variant pathValue = vbNullChar Dim pathCustomProperty As CustomProperty Set pathCustomProperty = sht.CustomProperties.Add("path", pathValue) Dim cprop As CustomProperty For Each cprop In ThisWorkbook.Sheets("control").CustomProperties If cprop.Name = "path" Then Debug.Print cprop.Value End If Next End Sub