LinkBack URL
About LinkBacks
Hello everyone. Can you apply a variable that is defined in a VBA module, sub macro, to a formula in an excel spreadsheet. If so, how do you go about doing this?
Hello everyone. Can you apply a variable that is defined in a VBA module, sub macro, to a formula in an excel spreadsheet. If so, how do you go about doing this?
Last edited by AnthonyWB; 04-19-2010 at 03:49 PM. Reason: Fix thread title.
You could output the variables value to a cell and the formula can then reference the cell.
To fully answer your question you need to provide more detail about what you are trying to do and why.
A global variable, "fPriorDate" , is declared as a string, and is determined by another global variable fDate, which is "gathered" from a user via an InputBox. For instance if the user enters 31-dec-2009, then fDate = "31-dec-2009" and fPriorDate = "30-nov-2009". A folder called fDate & "_157" is then created. Within the "fDate & "_157" folder are two workbooks, one called "CDS.xlsm" and and another called fDatePrior & "_CDS".
Now here is the kicker, a third workbook, in the same folder, called Formulas needs to reference (using vlookup, and performing some calculations) both the "CDS.xlsm" and fDatePrior & "_CDS" workbooks.
Please see the excel formula below. But instead of having a hard-coded-dates, as seen below I need the formul to utilize fDate and fPriorDate.
Please Login or Register to view this content.
If you outputted the details to a cell you could then reference it.
You would need to use the INDIRECT formula to build the reference to include the cell containing the value, as text.
A1: =31-dec-2009
=IF(VLOOKUP(D2,INDIRECT("'L:\" & A1 & "_157\Support_Summaries\[CDS.xlsm]CDS'!$H$3:$J$175"),3,FALSE)="divide",O2*VLOOKUP(D2,'L:\30-nov-2009_157\Support_Summaries\[30-nov-2009_157_CDS.xlsm]CDS'!$H$3:$J$175,2,FALSE),O2/VLOOKUP(D2,'L:\31-dec-2009_157\Support_Summaries\[30-nov-2009_157_CDS.xlsm]CDS'!$H$3:$J$175,2,FALSE))
.
Is it possible for me to set a range say "AV1" in the formulas sheet to be equal to "fPriorDate", As String, that is in the date format that it is in, for example 30-nov-2009 (It cannot be converted into a number !), and then use that , cell value in the vlookup?
Last edited by AnthonyWB; 04-19-2010 at 11:51 AM.
Yes and the same logic of building the reference using cell and static values would be applied.
Note that the other workbooks will need to be open for the formula to work.
Everyone who confuses correlation and causation ends up dead.
Andy,
How is the Indirect formula utilizing fDate and fPriorDate?
It's not directly. You have to store the contents of those variables in a cell.
You could write a user defined function to return the values, seeing as your VBA variables are public.
But as RS points out the use of INDIRECT will require all workbooks to be open.
Andy, and Rompstomper,
So I can set, for instance cell, AV1 in the Formulas workbook = fDatePrior ? Is this done in the VBA module, or is it hardcoded into the actual workbook? It seems the latter would cause a Name? error.
Please see the code below for a larger picture of what I am doing:
Please Login or Register to view this content.
Andy: You could write a user defined function to return the values, seeing as your VBA variables are public.
How do you go about doing that?
Last edited by AnthonyWB; 04-19-2010 at 01:34 PM.
I was able to pass the variable from the VBA code to that excel spreadsheet doing the following:
Please Login or Register to view this content.
I am attemting the following formula in the spreadsheet, where AV1 has assumed the value passed on from the variable fPriorDate. I am receiveing an error. Do I still have to use the indirect method. Anyone?
Please Login or Register to view this content.
I have solved the VBA part, I am moving the remaining question over to Worksheet functions. Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks