VBA Excel File not compiling: Sub or Function not defined

Question

Here is the associated code: It says the error is in the definition of the function operation() I feel like I need to include something, but I am not sure what. Can anybody please advise on what I need to do? I know to go to Tools > References > .. but after that I am not sure.

Sub operation()
'
' Macro5 Macro
'

'
Dim Erw, firstRow, lastRow
firstRow = 1
Last Row = Range("B" & Rows.Count).End(xlUp).Row
For Erw = firstRow To lastRow
    Dim newRow
    newRow = firstRow + 4
    Range("B" & newRow).Select
    ActiveCell.FormulaR1C1 = Range("B" & newRow)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;ActiveCell.FormulaR1C1", _
        Destination:=Range("$D$5"))
        .Name = "collections1504_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    nextRow = nextRow + 1
    Next Erw

    Range("D3").Select
    Selection.Copy
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D5:P143").Select
    Application.CutCopyMode = False
    Selection.QueryTable.Delete
    Selection.ClearContents
End Sub

If you set Option Explicit at the top of your code, it will automatically highlight and capitalize your variable names for you as you type. Anything that doesn't highlight/capitalize properly is a typo. Very handy tool for finding these errors. (for example, I'd Dim LastRow as Integer, then typing lastrow would get changed to LastRow, so I know it's right. If I'd typed last row it wouldn't get capitalized correctly, so I'd know something was wrong) — FreeMan
– FreeMan, Commented Jun 25, 2015 at 19:10

sous2817 · Accepted Answer · 2015-06-25 19:06:09Z

3

You've got a typo in your variable names. You have:

Last Row = Range("B" & Rows.Count).End(xlUp).Row

but it should be:

lastRow = Range("B" & Rows.Count).End(xlUp).Row

If you put

Option Explicit

at the top of your module, you get a slightly more helpful error message. You really should get in to the habit of including that as it will check to make sure all of your variables are declared. It's so helpful in fact, that you can have it set at module creation by default. In the VBA IDE go to Tools => Options => Tick the box labeled "Require Variable Declaration".

answered Jun 25, 2015 at 19:06

sous2817

3,9602 gold badges37 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Ogilvie · Accepted Answer · 2015-06-25 19:07:08Z

1

As the compiler tells you (and halts on the line), the function Last is not defined: there is a space too many:

Last Row

should be

lastRow

answered Jun 25, 2015 at 19:07

Paul Ogilvie

25.4k4 gold badges26 silver badges43 bronze badges

Collectives™ on Stack Overflow

VBA Excel File not compiling: Sub or Function not defined

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related