Excel cell formulas and VBA

[bteddy]

Hello,
On a worksheet, in a cell, with in a formula I need to reference a global
variable set in a vba modual.

Example: = (a1+z13)*2.537/myvar

How is this done? What is the proper term for this? I tried searching on the
net, but found nothing.

Thank you for your time.

 

[Gary''s Student]

Running something like:

Sub Macro1()
ActiveWorkbook.Names.Add Name:="myvar", RefersToR1C1:="=5678"
End Sub

will create and assign a value to a variable that can be used on the
worksheet.

 

[EricJohnson]

A variation of this approach is to define the name through Insert - Names -
Define. Give it the name "myvar" and in the "Refers To" section at the
bottom of the window, type in 5678 and click on OK. You've now defined a
name, myvar, and given it a value.

Under macro control, you can do something like this:

Sub Updatemyvar(NewVal As Single)
ThisWorkbook.Names("myvar").Value = NewVal
End Sub

The previous post would be a convenient way to create and initialize the
name, if you didn't want to perform this task manually, as described above.