Excel Vba Macro to Format Border on Page Bottom by qlv85395

VIEWS: 85 PAGES: 11

More Info
									                           Errata Sheet
                               For
“Learn Microsoft Excel 2002 VBA Programming with XML and ASP”

Page 15, Item 2 code:
      To remain more consistent with the rest of this procedure, revise the code
fragment as follows:

       „ Find and format cells containing numbers
               Selection.SpecialCells(xlCellTypeConstants, 1).Select
               With Selection
                       .Font.ColorIndex = 11 „Sets the font color to Violet
               End With
               Range(“C6”).Select

Page 48, Tip 2-4 last line:
        The last sentence should read: You will learn how to use the Object Browser
later in this chapter.

Page 50, Rule #4, 1st paragraph, last line:
    To clear the formatting in cell A4, you should use the following instruction:

    Range(“A4”).ClearFormats

    and to clear the contents in cell A4, use the following instruction:

    Range(“A4”).ClearContents

Page 94, Item 6 code, last line:
    To minimize typing, the line of code:
       strMsg= “The calculator total is “ & “$” & Cost & “.”
    should be replaced with: strMsg= “The calculator total is $” & Cost & “.”

Page 99, code, 2nd line:
    Delete the word “With”.
    The correct statement should read: Range(“B6”). Formula = Cost

Page 115, Running Function Procedure from Worksheet, 1st line:
     Replace: “A custom function is like built-in function.” with “A custom function is
like a built-in function.”

Page 158, 4th line from the bottom:
      Replace (Active.Value = 0) with (ActiveCell.Value = 0).
Page 159, Item 3:
       Delete one "End If" following the example on page 158. The WhatValue
procedure on the book's CD does not require this correction.

Page 215, Paragraph 1, last line:
      Change “ramdom” to “random” (refers to: file access type: random access file).

Page 297, Item 1 Table:
       Change the name of the First command button from “butOK” to “cmdOK”
(without quotes). Change the name of the Second command button from “butCancel” to
“cmdCancel” (without quotes).

Page 306, Item 1 Code:
      Replace “With InfoSurvey.lboxSystemy” with “With InfoSurvey.lboxSystems”.

Page 309, Item 1 Code (cmdOK_Click procedure):
       Add the following variable declaration in a line preceding the “Me.Hide”
statement:

       Dim r As Integer

       This variable declaration was erroneously omitted in the book‟s text; however it is
not missing in the equivalent procedure on the book‟s CD.

Page 313, Item 5, 3rd bullet:
        Instead of creating two labels as instructed in this bullet, create four labels (see
Figure 10-17). Set the Caption property of the labels shown on the left-hand side to:
Grade and Date. It is not necessary to assign names to these labels as they will not be
referenced in the code. Next, add the other two labels which in Figure 10-17 are shown
on the right-hand side. Change the Name property of these labels to lblGrade and
lblDate. These labels should have their Caption property set to Actual Grade and Exam
Date.


Page 315, Item 2, Code (UserForm_Initialize procedure), 8th line of code:
       Change the statement “lblLast.Visible = False” to “lblNames.Visible = False”.
The procedure on the book‟s CD does not require this correction.

Page 315, Item 2, Code (UserForm_Initialize procedure), 15th line from bottom:
       For the consistency sake, replace the comment “ „populate a combo box with
grades “ with “ „populate the Grade combo box”.


Page 315, Item 2, Code (UserForm_Initialize procedure), 6th line from bottom:
       Replace the statement “Me.lblDate.Caption = Me.Calendar.Value” with the
following: “Me.lblDate.Caption = Me.Calendar1.Value”.
Note: When you first add a Calendar control to the User Form, the system automatically
assigns it a name: Calendar1. You may rename this control to whatever you want and use
the new name in the above statement. The procedure on the book‟s CD assumes that the
Calendar control uses the default/system assigned name: Calendar1.

Page 316, Item 3, Code (optNew_Click procedure):
       Remove the statement “Me.txtSSN.SetFocus” located inside the If…End If
statement block. Me.txtSSN.SetFocus is going to happen regardless of the state of
lboxStudents.RowSource. The same correction is required in the procedure provided on
the book‟s CD.

Pages 316, Code (refNames_Change procedure):
         To make the procedure code more efficient, use the With… End With statement
to set the values of the lboxStudents list box as in the following example:

 With lboxStudents
   .RowSource = refNames.Value
   .ListIndex = 0
   .Visible = True
 End With

Page 317, Code (cmdOK_Click procedure):
         To make the procedure code more efficient, use the With… End With statement
to set the values of various controls on the UserForm as in the following example:

  With Me
    .txtSSN.Text = ""
    .txtLast.Text = ""
    .txtFirst.Text = ""
    .cboxYear.Text = ""
    .cboxMajor.Text = ""
    .txtSSN.SetFocus
  End With

Note: The same rule can be applied to the optNew_Click and lboxStudentChange
procedures listed on page 316.

Pages 315-318, Code indentation:
        End With, End If and End Select clauses should be placed the same indentation
level as the corresponding With, If, and Select Case clauses.

Page 328, Item 1:
        Replace the “Dim response” with “Dim response As Integer” to indicate the data
type of the declared variable.
Page 342, 1st line:
        Remove the quote after the closing parentheses. The format statement should look
as follows: Format(emp.Salary, "0.00")

Pages 343-345, Code (Various procedures):
        The statement cmdEmployeesList.Value = True is correct. When looking at the
Properties Sheet in the Visual Basic Environment one might think that this statement is
incorrect because the Value property is not mentioned in the list of available properties
for the command button. However, if you search the Visual Basic Help you will find out
that the Value property “Specifies the state or content of a given control” and “For a
CommandButton, setting the Value property to True in a macro or procedure initiates
the button's Click event.”

Page 343, Item 12, Code (line 1):
        Replace the statement “set ws =ActiveWorkbook.Sheets(“Sheet5”)” with the
following: Set ws = ActiveWorkbook.Sheets("Salaries")
Note: The Figure 11-2 on page 338 shows the partially hidden sheet tab with “Sal”. The
actual sheet name is “Salaries”.

Page 344, Code (cmdUpdate_Click procedure):
       For the consistency sake, please perform the following:
Replace “optHighlighted = True” with “optHighlighted.Value = True”
Replace “optAll = True” with “optAll.Value = True”.

Page 358, Item 1:
      Remove the space in front of Count. The correct statement should read:
      ?CommandBars(1).Controls.Count

Page 366, 2 lines above item 3:
      To continue to the next line, please add the space and underscore as shown below:

       Set myControl = CommandBars("Worksheet menu bar"). _
            Controls(InputBox("Enter the menu name (Example: Format):"))

Note: The other lines of code on this page are in the Immediate window so they are
entered on one line. The line in question is part of a procedure and needs an underscore if
you want to split it into two lines for easy reading.

Page 400, Code (top line):
        Replace the designation of the disk drive A in this line of code with the name of
variable as shown below:

       Response = MsgBox("There is no disk in drive " & DriveA & Chr(13) _
         & "or disk in drive " & DriveA & " is not formatted ", _
           vbRetryCancel, "Check Disk Drive")
Note: The same correction is required in the procedure provided on the book‟s CD.


Page 402, Code (Case Else statement):
      There should be a space after colon, and not before it, as in the following:

       MsgBox "Error " & Err.Number & ": " & Error(Err.Number)

Page 410, Code (SelectionChange Event procedure):
      Add the following variable declaration line:

       Dim myRange As Range

Page 411, Code (BeforeDoubleClick Event procedure):
      Change the reference to the Target.Address as shown below:

       If Target.Address = "$C$9" Then
            MsgBox "No double-clicking, please."
            Cancel = True
       Else
            MsgBox "You may edit this cell."
       End If

Note: The procedure on the book‟s CD does not require this correction.


Page 415, Code (Activate Event procedure):
      Add a space before “sheets” as in the following:

       This Workbook.Sheets.Count & “ sheets.”

Page 416, Code (DeactivateEvent procedure):
      Add the following variable declaration line:

       Dim cell As Range

Page 446, Sub AccessViaAutomation, 5th line:
       The statement: Set objAccess = GetObject(, “Access.Application.9”) should be
replaced with: Set objAccess = GetObject(, “Access.Application.10”)

Page 447, Tip 15-3 Code, top right:

Replace the statement:

       StrDb = ”C:\Program Files\Microsoft_
       Office\” & “Office Samples\ _
       Northwind.mdb
with the following:

StrDb = ”C:\Program Files\Microsoft “ _
  & “Office\Office Samples\ “ _
  & “Northwind.mdb”


To eliminate spacing problem, enter this statement on one line as follows:

StrDb = ”C:\Program Files\Microsoft Office\Office\Office
Samples\Northwind.mdb”

Page 447-448, Code:

      db.Close
      MsgBox….
    End Sub

Modify as follows:

      db.Close
      MsgBox….
      Set db = Nothing
   End Sub

Page 448, 449 Code:

Replace vbCr with vbCrLf as in the following:

Debug.Print fld.Name & “ = ” & fld.Value & vbCrLf

Page 452-453, Declaration line before the Sub CreateAccessForm() :
    The object variable name myAccess in the declaration line: Dim myAccess as
Access.Application should be replaced with obAccess as in the following: Dim obAccess
as Access.Application

Page 453, 3rd line from end of code:
    Add a space after the colon: MsgBox Err & “ : ” & Err.Description

Page 454, 1st bullet, Running the DisplayAccessReport2 procedure from the Immediate
window:

„ Enter the following statement on one line in the Immediate window
Call DisplayAccessReport2(“C:\Program Files\Microsoft _
Office\Office\Samples\Northwind.mdb”, “Sales Totals by Amount”)
Replace with:

Enter the following statement on one line in the Immediate window.

Call DisplayAccessReport2(“C:\Program Files\Microsoft
  Office\Office\Samples\Northwind.mdb”, “Sales Totals by Amount”)

Page 456, 1st paragraph, last line:

“new database called NewDB_DA”

should read:    new database called NewDB_DAO

Page 458, Sub RunAccessParamQuery(), middle of code

cmd.Parameters(“[BeginningDate]”) = StartDate
cmd.Parameters(“[EndingDate]”) = EndDate

Remove the square brackets in the parameter names as follows:

cmd.Parameters(“BeginningDate”) = StartDate
cmd.Parameters(“EndingDate”) = EndDate

Page 459 Sub RunAccessFunction():

Dim objAccess As Object

You can also declare the object variable objAccess of a specific object type as in the
following:

Dim objAccess as Access.Application


Page 461, end of code:
The following code fragement:

  db.Close
End Sub

should be modified as follows:

  db.Close
  Set db = Nothing
  Set qdf = Nothing
  Set rst = Nothing
End Sub
Page 462, end of code:
The following code fragment:
  rst.Close
  conn.Close
End Sub

should be modified as follows:

  rst.Close
  conn.Close
  Set rst = Nothing
  Set conn = Nothing
End Sub

Page 467- 468, end of procedure code:

Add the following statements before the End Sub keywords:

rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing

Page 469, 2nd paragraph:

“…first argument—Connection. This is a required argument of the Variant data type…”

In the code declaration on page 469 the connection argument strConn has been declared
as having String data type (see the first bullet below). According to the Microsoft Visual
Basic on-line help the data source for the query can be one of the following:

      A string containing an OLE DB or ODBC connection string. The ODBC
       connection string has the form "ODBC;<connection string>".
      A QueryTable object from which the query information is initially copied,
       including the connection string and the SQL text, but not including the
       Destination range. Specifying a QueryTable object causes the Sql argument to be
       ignored.
      An ADO or DAO Recordset object. Data is read from the ADO or DAO recordset.
       Microsoft Excel retains the recordset until the query table is deleted or the
       connection is changed. The resulting query table cannot be edited.
      A Web query. A string in the form “URL;<url>“, where “URL;” is required but
       not localized and the rest of the string is used for the URL of the Web query.
      Data Finder. A string in the form “FINDER;<data finder file path>“ where
       “FINDER;” is required but not localized. The rest of the string is the path and
       file name of a Data Finder file (*.dqy or *.iqy). The file is read when the Add
       method is run; subsequent calls to the Connection property of the query table will
       return strings beginning with “ODBC;” or “URL;” as appropriate.
      A text file. A string in the form "TEXT;<text file path and name>", where TEXT is
       required but not localized.


Page 469, 2nd paragraph:

“…third argument—SQL. This is a required argument of the String data type…”

According to the Microsoft Visual Basic on-line help the SQL argument is an Optional
Variant:
     The SQL query string to be run on the ODBC data source. This argument is optional
when you’re using an ODBC data source (if you don’t specify it here, you should set it by
using the Sql property of the query table before the table is refreshed). You cannot use
this argument when a QueryTable object, text file, or ADO or DAO Recordset object is
specified as the data source.

The procedure on page 469 could also be written like this:

Sub CreateQueryTable()
  Dim myQryTable As Object
  Dim myDb As String
  Dim strConn As String
  Dim Dest As Range
  Dim strSQL As String

  myDb = "C:\Program Files\Microsoft Office\Office10\" _
              & "Samples\Northwind.mdb"
  strConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" _
     & "Data Source=" & myDb & ";"
  Set Dest = Worksheets(1).Range("A1")
  strSQL = "SELECT * FROM Products WHERE UnitPrice>20"
  Set myQryTable = ActiveSheet.QueryTables.Add(strConn, Dest)
 With myQryTable
     .Sql = strSQL
     .RefreshStyle = xlInsertEntireRows
     .Refresh False
  End With
End Sub

Page 471, code:
You should add few statements before the End Sub keywords to close the objects and
release the memory used by the object variables by setting them to Nothing.

Page 475, code conn.Open:
"Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = _
C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;"

replace with the following:

"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " _
& “ C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;"

Page 501-502, First ASP script:
There is more than one way of coding ASP scripts. For example, to make your code
easier to understand you could rewrite the AccessTbl.asp script as follows:

<HTML>
<BODY>

<%
' Open Excel in a browser
Response.ContentType = "Application/vnd.ms-excel"

' declare variables
Dim accessDB
Dim conn
Dim rst
Dim sql

' name of the database
accessDB="Northwind"

' establish connection to the database
conn="DRIVER={Microsoft Access Driver (*.mdb)};"
conn=conn & "DBQ=" & Server.Mappath(accessdb)

' Create a Recordset
Set rst = Server.CreateObject("ADODB.Recordset")

' select all records from Shippers table
sql = "SELECT * FROM Shippers"

' Open Recordset (and execute SQL statement above)
' using the open connection
rst.Open sql, conn
rst.MoveFirst
' Display the recordset data in a table
Response.Write "<TABLE Border=1><TR>"
     For Each fld in rst.Fields
       Response.Write "<TH>" & fld.Name & "</TH>"
    Next
    Do While Not rst.EOF
       Response.Write "<TR>"
       For Each fld in rst.Fields
    Response.Write "<TD>" & fld.Value & "&nbsp; </TD>"
      Next
      rst.MoveNext
      Response.Write "</TR>"
   Loop
   Response.Write "</TABLE>"
   rst.Close
   Set rst = Nothing
%>
</BODY>
</HTML>

Notice that in the script above the HTML tags required to build the table are preceded
with the Response.Write statement and enclosed in quotes.

Page 503, code after 3rd paragraph:

conn.Open “Provider=”SQLOLEDB;” & _

remove the quotes in front of SQLOLEDB.

Page 516, Code, declaration line:

The variable name in the declaration does not match the variable name used in code.
Change the name of the variable from GoAhead to goAhead in the declaration line
(second line on this page).
his page).

								
To top