Can you use a variable in VBA code inside an excel worksheet formula?

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.

=IF(VLOOKUP(D2,'L:\31-dec-2009_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))
.

Andy,

How is the Indirect formula utilizing fDate and fPriorDate?

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:

Option Explicit

    Dim fDate            As String
    Dim fPath            As String
    Dim fDatePrevious    As String
    Dim fPriorDate       As String

Sub CreateFolder()
'***********************************************************************
'CreateFolder() macro creates the folders for the 157 Automation process.
'This includes a monthly rollover folder.
'***********************************************************************

    Dim Fldr   As String
    Dim ErrBuf As String

    fDate = Application.InputBox("Enter a date in the format shown:", "Date to add...", Format(Date, "DD-MMM-YYYY"))
    If fDate = "False" Then Exit Sub
    
    fDatePrevious = DateSerial(Year(fDate), Month(fDate), 0)
    '**********************************************************************
    'Formula used to calculate the prior month.
    '**********************************************************************


    fPath = "L:\"

    On Error GoTo ErrorHandler
        
        Fldr = fPath & fDate & "_157"
        MkDir Fldr
        
        Fldr = fPath & fDate & "_157\" & "157_Reports"
        MkDir Fldr
        
        Fldr = fPath & fDate & "_157\" & "Support_Summaries"
        MkDir Fldr

        Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Roll_forward_wTA"
        MkDir Fldr
        
        Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Roll_forward_12m"
        MkDir Fldr
        
        Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Terminated"
        MkDir Fldr
        
        Fldr = fPath & fDate & "_157\" & "157_Reports\" & "L3_IDA"
        MkDir Fldr
        
        Fldr = fPath & fDate & "_157\" & "157_Reports\" & "IBRD_L3"
        MkDir Fldr
        
        Fldr = fPath & fDate & "_157\" & "105_Reports"
        MkDir Fldr
    
    If Len(ErrBuf) > 0 Then MsgBox "The following folders already existed:" & vbLf & vbLf & ErrBuf
    
    Exit Sub
    
ErrorHandler:
    ErrBuf = ErrBuf & vbLf & Fldr
    Resume Next
    
End Sub
___________________________________________________

Sub MoveFilesFolder2Folder()
'***********************************************************************
'MoveFilesFolder2Folder() copies all files from the prior months,_
'Support_Summaries folder and pastes them into the current months
'Support_Summary folder.
'***********************************************************************
    
    Dim fso
    Dim sfol As String
    Dim dfol As String
    
    fPriorDate = Format(fDatePrevious, "DD-MMM-YYYY")
    '***********************************************************************
    'Changes the format of the PreviousDate format.
    '***********************************************************************

    sfol = "L:\" & fPriorDate & "_157\" & "Support_Summaries\"
    dfol = fPath & fDate & "_157\" & "Support_Summaries"

    Set fso = CreateObject("Scripting.FileSystemObject")
        
        On Error Resume Next
        
        If Not fso.FolderExists(sfol) Then
        MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"

        ElseIf Not fso.FolderExists(dfol) Then
        MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
        
        Else
        fso.CopyFile (sfol & "\*.*"), dfol ' Change "\*.*" to "\*.xls" to move Excel Files only
        
        End If

        If Err.Number = 53 Then MsgBox "File not found"

End Sub
___________________________________________________

Sub CreateFiles()
'***********************************************************************
'CreateFiles() macro creates the two most important files that are used,_
'in Phase I of the 157 Disclosure process: 105_Version1.xlsm and CDS.xlsm.
'***********************************************************************

    Dim sPath1      As String
    Dim sPath2      As String
    Const sFileOut1 As String = "105_version1.xlsm"
    Const sFileOut2 As String = "_CDS.xlsm"
      
    sPath1 = fPath & fDate & "_157\105_Reports\"
    sPath2 = fPath & fDate & "_157\Support_Summaries\"
    fPriorDate = Format(fDatePrevious, "DD-MMM-YYYY")
    
    Workbooks.Add
    With ActiveSheet
        .Name = fDate & "_105_v1"
        .Parent.SaveAs Filename:=sPath1 & sFileOut1, _
                       FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                       CreateBackup:=False
        .Parent.Close
    End With
    
    Workbooks.Add
    With ActiveSheet
        .Name = fPriorDate & "_CDS"
        .Parent.SaveAs Filename:=sPath2 & fPriorDate & sFileOut2, _
                       FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                       CreateBackup:=False
        .Parent.Close
    End With
    
End Sub
___________________________________________________

Sub CDS_Roll_Over()
'*************************************************************************
'CDS_ROll_Over() takes the prior months FX rates via "Common Data Stores,_
'or CDS", and places them into the prior months 157 disclosure folder in,_
'the Support_Summaries subdirectory.
'*************************************************************************
    
    Dim sPath1      As String
    Const sFileInp1 As String = "CDS.xlsm"
    Const sFileInp2 As String = "_CDS.xlsm"
    Dim wb1         As Workbook
    Dim wb2         As Workbook
    
    
    sPath1 = fPath & fDate & "_157\Support_Summaries\"
    
    Set wb1 = Workbooks.Open(sPath1 & sFileInp1)
        Range("A1:E200").Copy
        
    Set wb2 = Workbooks.Open(sPath1 & fPriorDate & sFileInp2)
        With ActiveSheet
        .Range("A1").PasteSpecial
        End With
        
    wb2.Close SaveChanges:=True


End Sub

I was able to pass the variable from the VBA code to that excel spreadsheet doing the following:

Sub Update_Formulas_Data()
'***********************************************************************
'Update_Formulas_Data() macro assigns the global variable, fPriorDate,_
'to the cell "AV1" in the formulas workbook. Indirect formulas then,_
'reference cell "AV1" in order to update the data that will be used to,_
'in the proceeding macro.
'***********************************************************************
    Dim sPath1      As String
    Dim wb1         As Workbook



    Const sFileInp1 As String = "Formulas.xlsm"
    
    sPath1 = fPath & fDate & "_157\Support_Summaries\"
    
    

    Set wb1 = Workbooks.Open(sPath1 & sFileInp1)
        Range("AV1").Value = fPriorDate
        Range("AV1").NumberFormat = "dd-mmm-yyyy"
    


End Sub

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?

vlookup(D2,'[AV1]30-Nov-2009_CDS'!$B$3:$D$175,3,False) = "divide", o2*vlookup(d2,'[AV1]30-Nov-2009_CDS'!$B$3:$D$175,2,False),o2/vlookup(d2,'[AV1]30-Nov-2009_CDS'!$B$3:$D$175,2,False))

I have solved the VBA part, I am moving the remaining question over to Worksheet functions. Thanks