Code from mrExcel's book "VBA and Macros for Excel 2007" is not working

[dl7631]

Hello everyone!
I am new to VBA. I am reading Mr Excel's book "VBA and Macros for Microsoft Office Excel 2007".
I am trying to re-create from scratch and run the code from Chapter 2, pp. 59-60 of the book (see below). I do it using F8. However, as soon as I get to the line that defines a new variable (FinalRow = Cells(Rows.Count, 1).End(xlUp).Row), I get an error:
"Compile error: Variable not defined"

My code is correct (in fact, I copied it from the file I downloaded from the link that was provided in the book)

Advice?
Thank you!
Dimitri


Sub ImportInvoiceFixed()
'
' ImportInvoice Macro
' Written 10/23/2003 by Bill Jelen This macro will import invoice.txt and add totals.
'
' Keyboard Shortcut: Ctrl+i
'
' Workbooks.OpenText Filename:= _
' "C:\invoice.txt", Origin _
' :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
' , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
' , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), Array(2, 1), _
' Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers _
' :=True
' Find the last row with data. This might change every day
' FinalRow = Range("A65536").End(xlUp).Row - also does not work
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row)
TotalRow = FinalRow + 1
' Build a Total row below this
Range("A" & TotalRow).Value = "Total"
Range("E" & TotalRow).Formula = "=SUM(E2:E" & FinalRow & ")"
Range("E" & TotalRow).Copy Destination:=Range("F" & TotalRow & ":G" & TotalRow)
Rows("1:1").Font.Bold = True
Rows(TotalRow & ":" & TotalRow).Font.Bold = True
Cells.Columns.AutoFit
End Sub

 

[xld]

Sub ImportInvoiceFixed()
'
' ImportInvoice Macro
' Written 10/23/2003 by Bill Jelen This macro will import invoice.txt and add totals.
'
' Keyboard Shortcut: Ctrl+i
'
' Workbooks.OpenText Filename:= _
' "C:\invoice.txt", Origin _
' :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
' , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
' , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), Array(2, 1), _
' Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers _
' :=True
' Find the last row with data. This might change every day
' FinalRow = Range("A65536").End(xlUp).Row - also does not work
Dim FinalRow As Long
Dim TotalRow As Long
    
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    TotalRow = FinalRow + 1
    ' Build a Total row below this
    Range("A" & TotalRow).Value = "Total"
    Range("E" & TotalRow).Formula = "=SUM(E2:E" & FinalRow & ")"
    Range("E" & TotalRow).Copy Destination:=Range("F" & TotalRow & ":G" & TotalRow)
    Rows("1:1").Font.Bold = True
    Rows(TotalRow & ":" & TotalRow).Font.Bold = True
    Cells.Columns.AutoFit
End Sub

 

[stanleydgromjr]

dl7631,

Try:

Option Explicit
Sub ImportInvoiceFixed()
'
' ImportInvoice Macro
' Written 10/23/2003 by Bill Jelen This macro will import invoice.txt and add totals.
'
' Keyboard Shortcut: Ctrl+i
'
    Dim FinalRow As Long, TotalRow As Long

    Workbooks.OpenText Filename:="C:\invoice.txt", Origin _
        :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers _
        :=True
    
    ' Find the last row with data. This might change every day
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    TotalRow = FinalRow + 1
    ' Build a Total row below this
    Range("A" & TotalRow).Value = "Total"
    Range("E" & TotalRow).Formula = "=SUM(E2:E" & FinalRow & ")"
    Range("E" & TotalRow).Copy Destination:=Range("F" & TotalRow & ":G" & TotalRow)
    Rows("1:1").Font.Bold = True
    Rows(TotalRow & ":" & TotalRow).Font.Bold = True
    Cells.Columns.AutoFit
End Sub

Have a great day,
Stan

 

[dl7631]

Thank you, both, very much!
That's what I suspected (based on some other pieces of code I've seen).

However, does anyone know if it is possible to somehow let MrExcel know that the code - both in the book and on the site - is incorrect? One has to define the nature of the variables before one defines the variables themselves...

Dimitri

 

[xld]

It isn't incorrect, it just is a bad coding practice. It works fine if you don't have Option Explicit at the head of your module. As you should ALWAYS have that, as I said, it is bad practice.

 

[dl7631]

It isn't incorrect, it just is a bad coding practice. It works fine if you don't have Option Explicit at the head of your module. As you should ALWAYS have that, as I said, it is bad practice.

Well - should not only best practices be taught in a high quality book?
The fact is - the book was created (also) for novices. As a novice - I did not know why what is supposed to be working according to the book did not work. Hence, it should be fixed in the book (at least in the next edition).

 

[stanleydgromjr]

dl7631,

If you go back to the book, page 18, and re-read the section "Customizing VB Editor Options Setting".

"By default, Excel doesn't require you to declare variables. I prefer this setting. It can save you time in creating a program. My co-author, Tracy, prefers changing this setting to require variable declaration. This forces the compiler to stop if it finds a variable that it does not recognize. This cuts down on misspelled variable names. It is a matter of your personal preference if you turn this on or keep it off."

Declaring variables is a personal choice.

As you develop your programming skills, you will change the way you code.

I am always learning and changing the way I code. I have been coding since the early 80's. And, there is still more to learn and experiment with.


Have a great day,
Stan

 

[xld]

Well - should not only best practices be taught in a high quality book?
The fact is - the book was created (also) for novices. As a novice - I did not know why what is supposed to be working according to the book did not work. Hence, it should be fixed in the book (at least in the next edition).

What should happen is that VBA should not allow you not to have it, it should not be optional.

My VBA course teaches that best practice .

 

[Fazza]

What should happen is that VBA should not allow you not to have it, it should not be optional.

My VBA course teaches that best practice .

 

[dl7631]

dl7631,

If you go back to the book, page 18, and re-read the section "Customizing VB Editor Options Setting".

"By default, Excel doesn't require you to declare variables. I prefer this setting. It can save you time in creating a program. My co-author, Tracy, prefers changing this setting to require variable declaration. This forces the compiler to stop if it finds a variable that it does not recognize. This cuts down on misspelled variable names. It is a matter of your personal preference if you turn this on or keep it off."

Declaring variables is a personal choice.

Stan

Yes, Stan - and I read that stuff and I really liked it and I changed my setting - to make sure my Excel VBA requires declaring the variables. But then I went through the exercise and nowhere did it mention: "If you changed the settings to require variable declaration, then don't forget to declare the variables like this: ... Or else - it won't work"
It should have this sentence. And it should have it in the next edition - if the book is to become better. Otherwise novices like myself will get confused and will go to mrexcel.com forum to waste smart people's time (like yourself).

Dimitri