Excel VBA: Keeping formulas written in macro in worksheet, so user can manually change formula in worksheet

I am using a Macro to generate a report. I take information from structures and with formulas calculate various numbers which populate cells in a worksheet. These worksheets are created in the Macro as well. What do I have to do to ensure that the formulas for calculating the numbers I populate in cells stay in the cell when a user clicks the cell? i.e. the user can decide to change the formula for a cell after the report is made.

Here is how I create the new cells in a function createTS():

Function createTS(ByRef index As Integer, ByRef wBStruct As wBStruct, ByRef gcBStruct As gcBStruct) As Worksheet
    '1) set up worksheet

    Set createTS = ThisWorkbook.Worksheets.Add(, ActiveSheet, 1, xlWorksheet)
    With createTS
        ThisWorkbook.VBProject.VBComponents(.CodeName).Name = cnDic(index)
        .Name = wBStruct.wTumorCellLine & "_" & wBStruct.wTypeBlock(index).projNum & "(" & gcBStruct.typeName & ")"
    End With
'...lots of code...
'this is where I populate the cells in the worksheet'
'...lots of code...
End Function

Within the function I would likely copy the information from the structures into certain cells (these cells contain no formulas, but are the original number from which I calculate the values in other cells. In other words, the formulas in the other cells would only be referencing other cells, not structures created in the macro). The point being that if I can get the formulas to stay in the cell, they can only reference other cells? (my initial belief)

an example cell could look like:

createTS.Cells(10,x).Value = createTS.Cells(1,5).Value*100 ' x would be some integer

Where do I include code to ensure that ^ formula stays within cell (10,x) so the user can later change the formula if desired?

Based off what I want to do, is there a better way to do this?

On a side note, assuming I am correct in my assumption with only referencing other cells with the formula, could the formula include variables referencing non cells at all, like a constant (x = 5.3445435 I would rather use x than 5.3445435 over and over again)? Could said variable be another cell? What are the possibilities?

Please note the Macro will shut down after it is done generating the report.

Thanks!

Solution

If you entered:

ws.Cells(1,x).Formula="=" & ws.Cells(1,1).Address & "*" & y

You would actually build up the formula for the cell, not its value.

Regarding the "variable" setting for use in worksheet formulas, the easiest way is to have the value in a cell and then name the range. You can type the name in the "Name Box" that usually has the cell address (next to the formula bar), or do it under the ribbon Formulas > Name Manager.
If you had named cell Z99 as x then your worksheet functions can just reference this by name (e.g. =A1*x)