1

I want to declare a variable in vba code of an Excel document and use it in a cell.

Something like this:

Vba Code:

Dim foo as Integer
foo = 2

Excel Cell (any)

=foo

Is that possible?

Community
  • 1
  • 1
Sergi Nadal
  • 900
  • 13
  • 23
  • 1
    No, that's not possible unless you want to make use of a [modeless](https://stackoverflow.com/a/16860597/1153513) and possibly invisible `userform`. You can only store values in your sheets (and hide them if you want). Cells can be used as a named range and then you could refer to them using `=foo`. Yet, the value would have to be in a cell (again). – Ralph Sep 13 '17 at 06:43
  • Ok. Thank you very much! – Sergi Nadal Sep 13 '17 at 06:51
  • 1
    Well you kinda can. If you create a `Public` UDF in a module, you can then call this UDF from your cell just like any other built in function call... i.e. lets say you have a UDF called `GetFoo` in your module. In your cell just type `=GetFoo()` and your cell will show the value returned by your UDF. Problem you have is that this is only triggered when you initially call the function. If you then go and change the returned value in `GetFoo`, cell doesn't get updated unless you trigger it again – Zac Sep 13 '17 at 07:17

1 Answers1

2

You can declare public function in a code module

Public Function Foo() As Integer
  Foo = 2
End Function

and use it in a cell

=Foo()
xmojmr
  • 8,073
  • 5
  • 31
  • 54
  • :) great minds and all that. Problem still is that these UDF's have to triggered to update values after the initial call – Zac Sep 13 '17 at 07:19
  • 2
    You can set the function to be `Volatile` so that it is calculated every time calculations are performed. (Or sometimes I pass a dummy variable of `Now()` to the function to achieve the same thing - so `=Foo(Now())` as the formula, and `Public Function Foo(d As Date) As Integer` as the procedure declaration. [I usually only do this if I am too lazy to write proper code, e.g. a quick-and-nasty one-off task.]) – YowE3K Sep 13 '17 at 08:20
  • 1
    While this is certainly a feasible solution to the question I still feel compelled to point out that [volatile UDFs](https://msdn.microsoft.com/en-us/library/office/bb687891.aspx#sectionSection2) can severely reduce the performance of your Excel file. Furthermore, you might want to read through this post: https://stackoverflow.com/questions/24353506/non-volatile-udf-always-recalculating – Ralph Sep 13 '17 at 08:57