Document properties to store variable value for future use

[dorabajji]

Hi,
Is there anyway to store a variable value outside worksheet cells or as a text file, for future use , so that variable value can be retrieved.
Like in google sheets- property service. Like that excel can be done?

[vba_php]

the property service that you speak of, offered by google, does not serve the purpose you are asking about. it is more like the concept of a cookie storing encrypted data when you log into a website. however, to your question.....YES you can store data virtually anywhere on an OS system using almost any method created over the last 30 years basically. but, in VBA, of course you can use a var. just throw the text into it, then throw it out wherever you want. however, a string variable, which is what you want to use here, DOES have limits. so obviously you can't store massive amounts of data in one variable. see here for the bounds of what VBA can do:

VBA string data type limitations, microsoft documentation

[dorabajji]

Hi,
I checked the link you had provided .
But how to store and retrieve.
Currently i use inputbox for getting value and storing in worksheet
and then checking if it exist, if not again showing up input box.

What happens , this file is getting circulated, hence the ranges are getting deleted, hence i want dont want to get the input box
to enter, if the value is set by one user and others simply follow the file .

[vba_php]

well let us see now.....hence, hence, hence, hence....LOL. I don't think I understood anything you said except for the fact that you are using an input box to capture text. so, if I read your orig. post right, you are doing something like this:

get:
s = inputbox("enter the data")
   if s <> "" then
      'THROW THE DATA TO THE WORKSHEET
   else
      go to get
   end if

now, to another relevant point that can provide some value to you in your little journey here....you really need to research the concept called scope. it is a very relevant concept in the world of programming, and it is very closely related to how variables are used in any given program. so, for instance in VBA there are a few different "scopes". they are called global, private and public. and what that means is that variables declared with these qualifying scopes can be ""seen"" by other parts of the program that are located in different architectural areas of the program's layout. you can use this to your advantage, I believe. because, I don't think, that say for instance, global vars or public vars lose their values when code routines behind excel's interface end and/or are terminated. I might be wrong though. it's been a LONG time since I've checked on that.

and of course, storing info can be done countless other ways too. like storing it on a hidden sheet, throwing it to a text file with like, say, the PUT statement in a file I/O stream, etc...

does any of this help??

[dorabajji]

no the file, is sent over mail. So if i store the value in text file or outside workbook , it would be a problem and the contents of the worksheet are cleared when new data comes from import macro. So that also cannot be done.

My point is the user who first sets the value from input box ( like property service in google , where you can set and retrieve the value of variable)
is stored somewhere in the file. Not hidden sheet or in the current worksheet.
so when the file, circulates to other person, then they need not see the input box asking them to enter date again.

at present , i do like this

if range("A1")="" then
inputbox()
else
a=range("A1").value


the above, i store in a hidden sheet on A1 at present. But the macro deletes all the worksheet apart from main sheet, hence i want to use some alternative.

[vba_php]

well if you are circulating a single worksheet around to everyone without linking or using sharepoint or anything like that, then obviously the input box will happen everytime. if you don't want new people to see the input box again, just write an ''IF'' statement in the code and check if the date is stored wherever you put it. it literally should be THAT easy. if the date is being deleted with the macro you speak of, everytime you circulate it to a new person, then obviously you need to fix that. that doesn't make sense.

if the deletion of data is the real problem, and you definitely need the input box to stay in the code and you never know if the date is going to be available to be checked everytime you do whatever, then put an ''IF'' statement in the code and run the box if the date is NOT found. again, it's literally that easy.

[romperstomper]

You could use a function like this - just call it whenever you need the name value and it will either retrieve an existing name, or create a new one and assign the value using the Inputbox:

Function GetNameValue(NameOfName As String, Optional wb As Workbook)
    If wb Is Nothing Then Set wb = ActiveWorkbook
    On Error Resume Next
    Dim nm As Name
    Set nm = wb.Names(NameOfName)
    On Error GoTo 0
    If nm Is Nothing Then
        Dim NameValue
        NameValue = InputBox("Enter value for name")
        wb.Names.Add Name:=NameOfName, RefersTo:=NameValue
    Else
        NameValue = wb.Worksheets(1).Evaluate(NameOfName)
    End If
        
    GetNameValue = NameValue
End Function

[protonLeah]

You can also use one of the workbooks unused built-in document properties such as "content status" like:

ThisWorkbook.BuiltinDocumentProperties("Content status") = myVar
myvar = ThisWorkbook.BuiltinDocumentProperties("Content status")