excel - I'm trying to call a sub, but I keep getting a "Type Mismatch" Error when I put in the variables for the sub (anything to do with ranges?)

Question

A little background

I'm opening the two most recent files in a specific folder. (I have the two actions that open the files commented out.) Then, I want the macro to look at both of those wbs (vars recent & prev) to calculate values for the new wb ("test.xlsx"). To my pea brain, this code makes a little sense:

The Code:

    Sub twolatestfiles()

        Dim FileSys, objFile, myFolder, c As Object
        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim strFilename, strFilename2, FolderName As String
        Dim dteFile As Date

        FolderName = ("C:\FilePath\Folder")
                Set FileSys = CreateObject("Scripting.FileSystemObject")
                Set myFolder = FileSys.GetFolder(FolderName)

        dteFile = DateSerial(1900, 1, 1)
        For Each objFile In myFolder.Files
            If InStr(1, objFile.Name, ".xlsx") > 0 Then
                If objFile.DateLastModified > dteFile Then
                    dteFile = objFile.DateLastModified
                    strFilename2 = strFilename
                    strFilename = objFile.Name
                End If
            End If
        Next objFile

       ' Set wb1 = Workbooks.Open(FolderName & Application.PathSeparator & strFilename)
       ' Set wb2 = Workbooks.Open(FolderName & Application.PathSeparator & strFilename2)

    Set FileSys = Nothing
    Set myFolder = Nothing

    Dim recent, prev As String
    recent = Right(strFilename, Len(strFilename) - 2)
    prev = (CStr(strFilename2))

     Call populate_fields(ByVal prev, ByVal recent, "test.xlsx")
   End Sub
   Sub populate_fields(wb1 As String, wb2 As String, activewb As String)
    ActiveCell.FormulaR1C1 = "='[" & wb1 & "]SUMMARY'!R11C2-'[" & wb2 & "]SUMMARY'!R11C2"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "='[" & wb1 & "]SUMMARY'!R11C2"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "='[" & wb1 & "]SUMMARY'!R11C3-'[" & wb2 & "]SUMMARY'!R11C3"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "='[" & wb1 & "]SUMMARY'!R11C8"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "='[" & wb1 & "]SUMMARY'!R11C3"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "='[" & wb1 & "]SUMMARY'!R11C5-'[" & wb2 & "]SUMMARY'!R11C5"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "='[" & wb1 & "]SUMMARY'!R11C5"
    Range("J4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-4]"
    Range("J5").Select
End Sub

The Problem: Every time I try to run the sub:

Call populate_fields(ByVal prev, ByVal recent, "test.xlsx")

The program tells me that the two are mismatched, but they are both strings. I am stumped on how to resolve this. I get both of the variables at the bottom to return the values I need. Since the files will be changing each day, I cannot type the file names themselves.

I'm very new to VBA. Any help is appreciated.

Edit: I've also tried directly typing the text (name of the files) into the variables. Also, the files are open.

标签： excelvba

Answer 1

You need to be aware of the four key practises for novice VBA programmers

1. Always, always, always put Option Explicit at the start of each Class/Form/Module.

2. Use F1 to get help. In the VBA IDE put your cursor on a VBA or Office object and press F1. The MS helppage for that item will load. Make sure you read and understand the help page.

3. Compile your code before you try to Run it. In the VBA IDE use Debug.Compile Project

4. Install the fantastic free Rubberduck addin for VBA. In particular, pay attention to the code inspections, they are a great learning resource for VBA and the Office applications.