Store variable in excel-file

[wmbb]

Is it possible to store a variable inside an excel-file to use this var when a file is re-opened.
I want to store the unlocked cells is a range and subsequently lock these cells for editing.
But I want to keep the range for future use to restore the cell-protection.

 

[PHV]

Can't you use a named range ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wmbb]

Thank you for your response.
I was also thinking of that and for this purpose it is possible.
But if there is a possibility to store variables into the file I can also use this for other purposes.
So the question is more general then for this specific case.

 

[PHV]

You may store any values in an hidden sheet.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ClulessChris]

Is this what you mean?

Dim myVar
myVar = worksheet($Sheet1).range("A1").text

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[wmbb]

Thank for pointing me in the right direction.
I think I will use this suggestion.

 

[strongm]

Since this would appear to be an application configuration setting, the registry might also be a suitable location (although obviously the setting would not persist if you sent the spreadsheet to another user)

 

[combo]

You can also directly use names to store values, after setting the name's visibility to false it's not visible for the user.

combo

 

[wmbb]

Hi Combo,

What exactly do you mean with "directly use names to store values" ?
Can you give an example of code to store a variable using names ?
Maybe this is the way to go....

 

[strongm]

CustomDocumentProperties may also be of use here

 

[combo]

An example with hiding name for the user:

With ThisWorkbook
    .Names.Add Name:="VariableInName", RefersTo:="=0.5"
    .Names("VariableInName").Visible = False
    MsgBox Evaluate(.Names("VariableInName").RefersTo)
    .Names("VariableInName").RefersTo = "=""Sample text"""
    MsgBox Evaluate(.Names("VariableInName").RefersTo)
End With

combo

 

[strongm]

I should really have said CustomDocumentProperties or CustomProperties. Either would do.

Here's an example of using CustomProperties:

[blue]Public Sub wombat()
    If GetCustomProperty("varExample") Is Nothing Then ActiveSheet.CustomProperties.Add "varExample", 5

    MsgBox GetCustomProperty("varExample").Value [green]' could rely on default property, but let's be explicit[/green]
    GetCustomProperty("varExample").Value = "An example"
    MsgBox GetCustomProperty("varExample").Value    
End Sub

Public Function GetCustomProperty(strPropertyName As String, Optional wshTarget As Worksheet) As CustomProperty
    Dim item As Variant
    If wshTarget Is Nothing Then Set wshTarget = ActiveSheet
    For Each item In wshTarget.CustomProperties
        If UCase(item.Name) = UCase(strPropertyName) Then
            Set GetCustomProperty = item
            Exit For
        End If
    Next
End Function[/blue]

 

[wmbb]

I wanted to use this code but an error ocurred "object required"
What is wrong with this code ?
It seems the ActiveDocument is empty but how to solve this ?

Sub start()
If ReadProp("protection") = "" Then AddProp "protection", "false" Else WriteProp "protection", "true"
MsgBox ReadProp("protection")
End Sub

Sub WriteProp(sPropName As String, sValue As String)
ActiveDocument.CustomDocumentProperties(sPropName).Value = sValue
End Sub

Sub AddProp(sPropName As String, sValue As String, Optional lType As Long = msoPropertyTypeString)
ActiveDocument.CustomDocumentProperties.Add Name:=sPropName, LinkToContent:=False, Type:=lType, Value:=sValue
End Sub

Function ReadProp(sPropName As String) As String
On Error Resume Next
ReadProp = ActiveDocument.CustomDocumentProperties(sPropName).Value
End Function

 

[PHV]

ActiveDocument is a Word object, not an Excel one.
Use ThisWorkbook or ActiveWorkbook instead.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wmbb]

Thank you for your quick response.
Changing ActiveDocument to ActiveWorkbook was the solution !

I've done some research of reading this customdocumentproperties from a closed file but I haven't found a solution for office 2010. I've found the option using dsofile.dll but this doesn't work in office 2010.
Is it possible to read the CustomDocumentProperties("protection") as set in the previous code from a closed file in vba of office 2010?