Accessing custom property's value gives 'Out of Memory' error when value is null

Question

I'm trying to create a custom property in an excel sheet, then retrieve its value. This is fine when I don't use an empty string, i.e. "". When I use the empty string, I get this error:

Run-time error '7':
Out of memory

Here's 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 fails at Debug.Print cprop.value. Shouldn't I be able to set the property to "" initially?

Answer 1

With vbNullChar it works, 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

Answer 2

I think from the comments and the answer from Daniel Dusek it is clear that this cannot be done. The property should have at least 1 character to be valid, an empty string just isnt allowed and will give an error when the .Value is called.

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