Declaring a variable to use in Excel but defining it externally

Question

I have a couple of excel functions in one workbook that use a value repeatedly. I often have to change the value and so I am planning on replacing all instances of that value with a variable: MyVal so that I can just change the value of the MyVal next time instead of having to go through all the instances of that value. I know I can do this by naming a cell with that value and hence creating a variable of sorts.

However, I want to give someone else the ability to change the value of MyVal without giving them access to the excel sheet. Is there any way I can define MyVal in an external file? (I was hoping to do it in a .properties file since this is all part of a larger Java project). All the solutions I have found so far have only talked about defining variables within the sheet.

Answer 1

You can't refer, in a cell formula, to a value that is stored externally (in any meaningful sense). But... you could pick the value up on the workbook_open event from a text file and then set a cell to the value you find.

This would look something like:

1. Make an ini like file that holds the value is going to be updated by the user

   C:\my.ini:

   someVar=10
myValue=9.82
someothervar=pete

2. In your locked down excel sheet your formulas can refer to some named range (we'll call it myValue and it will refer to Sheet1!A1).

3. You could make a VBA subroutine that will run on Workbook Open to pick up the value stored in your ini file and populate the Sheet!A1 cell's (or referencing the named range) value so formula's referencing named range "myValue" will have the new value.

Step 3 would look something like

Private Sub Workbook_Open()

    Dim fileName As String
    Dim textRow As String
    Dim fileNo As Integer
    Dim param As String

    fileName = "C:\my.ini"
    fileNo = FreeFile 'Get first free file number

    Open fileName For Input As #fileNo
    Do While Not EOF(fileNo)
       Line Input #fileNo, textRow

       'Check to see if it's the value we are looking for
       'assuming it's in the file as
       'myValue=9.82
       param = Split(textRow, "=")(0)
       myValue = Split(textRow, "=")(1)
       If param = "myValue" Then
            Range("myValue").Value2 = myValue
       End If
    Loop
    Close #fileNo


End Sub

That subroutine has to go in the Workbook's code page (double click "ThisWorkbook" in the VBA's Project pane)

Alternatively, perhaps there is some Java library that could pop open a workbook and update a value in a cell and just do all the code there (not sure how feasible that would be).