none
Store permanent variable in Excel with VBA RRS feed

  • Question

  • Hi,

    I have a question about VBA in Excel.
    The program in my Excel asks for a value to the user but can you save this value somewhere in a file?

    The next time you open the Excel the value will be read from the file and the user does not have to enter this value until he or she wants to change the value again.

    Can someone help me out with this or show me a related article?
    Or maybe write a simple example to learn the basics.

    Thank you

    Best regards,
    Yannick

    Wednesday, March 16, 2016 10:13 AM

Answers

  • Hi, Yannick

    >>Here you have mentioned that “store permanent variable”

    We cannot store value in variable permanently.

    >>you have a program in Excel and you want to store the value in file.

    Here I think that it is better that you store the value in hidden sheet. So that when user want to change the value he can easily get the value from file. Because sheet has ranges so you can set your values at particular range and when you need you get back your value easily but if you use text file to save the values and if there are many values are available or values are available for multiple users then how will you choose correct value from that.

    >>Example

    Sub WriteData()
    Dim MyArray As Variant
    MyArray = Array("x", "y", "z")
    Range("A1:C1").Value = MyArray
    ActiveSheet.Visible = False
    End Sub
    
    Sub ReadData()
    Sheets("Sheet4").Visible = True
    Dim MyArray As Variant
    MyArray = Range("A1:C1").Value
    End Sub
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Thursday, March 17, 2016 2:15 AM
    Moderator

All replies

  • Hi,

    I have a question about VBA in Excel.
    The program in my Excel asks for a value to the user but can you save this value somewhere in a file?

    The next time you open the Excel the value will be read from the file and the user does not have to enter this value until he or she wants to change the value again.

    Can someone help me out with this or show me a related article?
    Or maybe write a simple example to learn the basics.

    Thank you

    Best regards,
    Yannick

    Wednesday, March 16, 2016 9:48 AM
  • Hi Yannick,

    I personally would just have the value put into a range that will never be used. I dont think you can save the variable once the sub end.

    Regards,

    Soliddrew

    Wednesday, March 16, 2016 11:04 AM
  • You can put this value into a cell, like 'A1', etc

    An example below:

    Sub askValue()
    
    Dim varValue As Variant
    
    varValue = InputBox("Give me a value", "value")
    
    ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = varValue
    
    End Sub


    André Santo | basevba.wordpress.com

    Wednesday, March 16, 2016 11:09 AM
  • Hi André, 

    I do this like you mentioned until now.
    But I store the variable on an invisible sheet so the user can't see the variable.

    I am looking for a way to do this without the sheet.

    Wednesday, March 16, 2016 11:52 AM
  • You can write your value to a txt archive:

        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
    
        Dim Fileout As Object
        Set Fileout = fso.CreateTextFile("C:\your_path\vba.txt", True, True)
        Fileout.Write "your string goes here"
        Fileout.Close


    André Santo | basevba.wordpress.com

    Wednesday, March 16, 2016 12:35 PM
  • Many ways to accomplish this in VBA ... BUT ... do you want it locally on your computer only? Because you say "user". Will all users be on the same computer in this scenario?

    You can save a value for Excel VBA to pick up next time to a local file, to a network file or in the Registry (local only).

    Please elaborate.


    Best regards George



    Wednesday, March 16, 2016 5:49 PM
  • Hi,

    I suppose the easiest way is to put the value in the workbook where VBA exists.
    You could add a new worksheet (visible = xlSheetVeryHidden) and put the value on it.

    Using an external file (text file?), or using Registry is somewhat laborious, I suppose.
    Regards.
    Wednesday, March 16, 2016 11:54 PM
  • Hi, Yannick

    >>Here you have mentioned that “store permanent variable”

    We cannot store value in variable permanently.

    >>you have a program in Excel and you want to store the value in file.

    Here I think that it is better that you store the value in hidden sheet. So that when user want to change the value he can easily get the value from file. Because sheet has ranges so you can set your values at particular range and when you need you get back your value easily but if you use text file to save the values and if there are many values are available or values are available for multiple users then how will you choose correct value from that.

    >>Example

    Sub WriteData()
    Dim MyArray As Variant
    MyArray = Array("x", "y", "z")
    Range("A1:C1").Value = MyArray
    ActiveSheet.Visible = False
    End Sub
    
    Sub ReadData()
    Sheets("Sheet4").Visible = True
    Dim MyArray As Variant
    MyArray = Range("A1:C1").Value
    End Sub
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Thursday, March 17, 2016 2:15 AM
    Moderator
  • Yes. You can use Workbook.CustomDocumentProperties. See https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.customdocumentproperties.

    There are also other options:

    • I usually prefer to store values on a hidden sheet. 
    • See https://docs.microsoft.com/en-us/office/vba/word/concepts/miscellaneous/storing-values-when-a-macro-ends. The article is about Word, not Excel, so some options are different, but some ideas are relevent, like registry.
    • Write the value to a text file.
    • You can store values in the Excel Application's current session. They disappear when you close Excel, but are available to all workbooks. So, slightly different to your scenario.

    Tim

    Tuesday, December 17, 2019 10:40 PM