Go back to Alan's home page

  Bash Script  MetaPost Excel VBA  Java Swing  Linux  

  1. How to learn

    • Record macros and then look into the code in VB Editor.
    • Look up syntax and usage.
    • Search for tutorials/tips online.

  2. Range, Cells, Rows/Columns

    Returns Example
    Range a set of cells, rows, columns, or mixed (by Union). Range("A1")
    Range("A1:B2")
    Range("A1:B2,C3:D4")
    Range("A:C")
    Range("A:A")
    Range("1:3")
    Range("1:1")
    Range("A") 'syntax error
    Range("1") 'syntax error
    Cells a cell by row idnex and column idnex. Cells(1,2)
    Cells 'all cells
    Rows/Columns rows/columns Rows 'return all rows
    Rows(1)
    Rows("1:3")
    Columns(1)
    Columns("A:C")
    Rows("1") 'ok
    Columns("A") 'ok

    By default, they apply to Activesheet if no quantifier is given. They can also apply to other objects, particularly to each other. For example,

    Range("A1:C2").Columns

    returns all columns in the range A1:C2.

    However, if a Range contains multiple contiguous blocks (Areas), Range.Columns only applies to the first block.

  3. Rows vs. EntireRow (Columns vs. EntireColumn)

    Rows   returns rows in the range;
    EntireRow   returns row that covers the range.

    The following example illustrates the difference:

    MsgBox Range("A1:C2").Rows.Cells.Count
    MsgBox Range("A1:C2").EntireRow.Cells.Count

  4. RowHeight vs. Height (ColumnWidth vs. Width)

    RowHeight  unit is character, read/write;
    Height  unit is point, read-only.

  5. Methods vs. Properties
    6. Methods are procedures that can be called, and properties are values that can be read/written. For example, the following code is incorrect:

    Rows(1).AutoFit = True ' error

    AutoFit is a method, not a property.

  6. Read/Write text files

    The following code reads all text from a file at once:

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.OpenTextFile("filename.txt", 1)
    allText =f.ReadAll
    f.Close

    Here fso is a FileSystemObject object, and f is a TextStream object. To create TextStream objects, use

    Set f = fso.CreateTextFile(filename[, overwrite[, unicode]])
    Set f = fso.OpenTextFile(filename[,iomode[,create[,format]]])

    To read from or write to a TextStream, use the following TextStream methods:

    f.Read(numberOfCharacters)
    f.ReadAll
    f.Write(string)