What is the syntax to declare a worksheet object?

Dim <object name> As Worksheet
Set <object name> = Worksheets("<sheet name>")
'example of simplification with the worksheet object

'Original code
Worksheets("exampleSheet").Range("A1") = 6
Worksheets("exampleSheet").Range("B2:B9").Font.Italic = True
Worksheets("exampleSheet").Range("B2:B9").Interior.Color = rgbRed

'Simplified code
Dim sht As Worksheet
Set sht = Worksheets("exampleSheet")

sht.Range("A1") = 6
sht.Range("B2:B9").Font.Italic = True
sht.Range("B2:B9").Interior.Color = rgbRed



Introduction to XML

Introduction to XML
XML stands for eXtensible Markup Language, and has generated significant interest ever since Microsoft’s decision to add XML support to Office applications. While HTML’s purpose is for displaying data, XML’s more relates to carrying data. Unlike the pre-defined tags in HTML, the user defines the tags for HTML and source files are stored in text files.

*Note that tag and element are the same thing

XML & Excel

Users can save a spreadsheet as an XML document, as well as open XML documents as new spreadsheets. Moreover, data can also be imported into a worksheet from an XML document. The majority of cross XML-Excel, XML-VBA applications are only supported from Excel 2003/2007 onwards.

Requirements of an XML File

  1. XML declaration – defines XML version and character encoding of the document. Always the first line of code, or else Excel won’t be able to recognize the file as XML (will see it as a text file instead)
  2. Root element – pair of tags that all other tags are nested within (aka. child elements)
  3. All tags must be in pairs (ie) have an accompanying closing tag
  4. Tags are case-sensitive (ex) <TEST> </test> do not work but <TEST> </TEST> work. It is XML convention to name tags using lowercases and underscores
  5. Attributes must be in quotation marks – attributes are names that can be assigned to pairs. Try using child elements over attributes
<?xml version="1.0" encoding="UTF-8"> 'xml declaration
<test> 'root element
    <problem> 'child element
     <img src="questionmark.jpg" /> 'closing slash, even though HTML-version has no closing tag
     <problem problemID="Hard Level"> 'tag with name
     </problem> 'child element
</test> 'root element

What are the data types?

There are two categories of data types: numeric and non-numeric.

Numeric types

  • Byte
    • whole numbers
    • 0 to 255
  • Integer
    • whole numbers
    • -32768 to 32767
  • Long
    • whole numbers
    • +/-2,147,483,648
  • Single
    • decimals
    • +/-3.402823E+38
  • Double
    • decimals
    • +/-4.94065645841247E-324
  • Currency
    • decimals
    • +/-922,337,203,685,477.5808
  • Decimal
    • decimals
    • +/- 79,228,162,514,264,337,593,543,950,335

Non-numeric types

  • Stringfixedlength
    • 1-65 000 characters
  • Stringvariablelength
    • 0-2billion characters
  • Date
    • 01/01/100 to 12/31/9999
  • Boolean
    • true or false
  • Object
  • Variantnumeric
    • any value as large as Double
  • Varianttext
    • same as variable-length String


What is the syntax to declare variables?

'to declare procedure variables
Dim <variable name> As <variable type>

‘to declare procedure variables that retains value
Static <variable name> As <variable type>

‘module variable
Private <variable name> As <variable type> 

‘global variable
Public <variable name> As <variable type>