Using a Public Variable in a cell formula
 

Is it possible to use a Public Variable (created in a macro) in a cell
formula?

Specifically: I have a workbook that contains a date as a part of the name
(id MySheet 11.10.03.xls)
When I open the workbook I capture the date (11/10/03) into a Public
Variable "ThisWeek". I want to use that
variable in a cell calculation. Is that possible? When I use the formula
=if(E3<ThisWeek, "Not Started", "Started"),
I get #NAME# in the cell

TIA
Glen

Using a Public Variable in a cell formula
 

You can't directly access the variable, but you could use a UDF:

Public Function GetThisWeek() As Double
GetThisWeek = ThisWeek
End Function

and use it as:

IF(E3<GetThisWeek(), "Not Started","Started")


In article ,
"Glen Mettler" wrote:

Is it possible to use a Public Variable (created in a macro) in a cell
formula?

Specifically: I have a workbook that contains a date as a part of the name
(id MySheet 11.10.03.xls)
When I open the workbook I capture the date (11/10/03) into a Public
Variable "ThisWeek". I want to use that
variable in a cell calculation. Is that possible? When I use the formula
=if(E3<ThisWeek, "Not Started", "Started"),
I get #NAME# in the cell

Using a Public Variable in a cell formula
 

You can do most anything with a date you need to do by using worksheet
function

=Today()

gives todays date. If you want the Monday of the current week (assuming
week starts on Monday)

=TODAY()-WEEKDAY(TODAY(),2)+1

If you want the worksheet name or the workbook name, you can parse the date
information out as well
http://www.cpearson.com/excel/excelF.htm#SheetName

formulas are at the bottom of the page.


If you still want to use the variable, you would need to write a UDF that
returns the value of the variable.

Public Function MyDate()
MyDate = ThisWeek
End Function

--
Regards,
Tom Ogilvy




"Glen Mettler" wrote in message
...
Is it possible to use a Public Variable (created in a macro) in a cell
formula?

Specifically: I have a workbook that contains a date as a part of the
name
(id MySheet 11.10.03.xls)
When I open the workbook I capture the date (11/10/03) into a Public
Variable "ThisWeek". I want to use that
variable in a cell calculation. Is that possible? When I use the formula
=if(E3<ThisWeek, "Not Started", "Started"),
I get #NAME# in the cell

TIA
Glen