Office 2010 Code (VBA) Compatability Inspector

To check whether existing VBA codes supported by Office 2010 or not,
instead of re-compiling the VBA codes, Microsoft now offering a new inspector for you.

Gray has written a short guide about on how to use the inspector.
If your macros are password protected, remember to unlock it before launching the inspector.

However, this tools is still in beta. I have encountered serveral false alarm about "Range" objects.

As the tools are in beta, you can send any feedback to the development team.
There is also a sweeptakes on bug reporting.

Excel 2010 Object Model Changes

Seems the VBA changes on Excel 2010 has been fixed.
A official comparsion to 2007/2003 could be found here


Luckly, there are no critical changes like FileFind in 2007.
Most of them are related to conditional formatings.

Excel VBA Stub: Rearranging Sheets

Codes for re-ordering worksheet
subSplitAndHideSheets: split and hide sheets that not appeared on list: strSheetsAll

------------------

Sub subLaunch
    dim strSheetsAll as string
    strSheetsAll = "Sheet1;Sheet3;Sheet2"
    call subSplitAndHideSheets(strSheetsAll)
end sub


Sub subSplitAndHideSheets(wbCurr As Workbook, strSheetsAll as string)

    Dim strSheets() As String
    Dim strSheetsSD
    Dim intSheetsCounter As Integer
    Dim intSheetsCount As Integer
    Dim intCurrSheet As Integer


    If strSheetsAll = "" Then
        Exit Sub 'Exit sub if not given
    End If

    Set strSheetSD = CreateObject("Scripting.Dictionary")

    strSheets = Split(strSheetsAll, ";")
    intCurrSheet = 1
    intSheetsCount = UBound(strSheets)
    For intSheetsCounter = 0 To UBound(strSheets)
        If funcChkSheetExist(wbCurr, strSheets(intSheetsCounter)) Then
            strSheetSD.Add (UCase(strSheets(intSheetsCounter))), Nothing
            wbCurr.Sheets(strSheets(intSheetsCounter)).Move before:=Sheets(intCurrSheet)
            intCurrSheet = intCurrSheet + 1
        End If
    Next

    intSheetsCount = wbCurr.Worksheets.Count
    If intSheetsCount <= 1 Then
        Exit Sub
    End If
    For intSheetsCounter = 1 To wbCurr.Worksheets.Count
        If wbCurr.Sheets(intSheetsCounter).Visible = xlSheetVisible Then
        'set as hidden: if Sheet not exist in given string array and name is  not parameters
            If Not strSheetSD.exists(UCase(wbCurr.Sheets(intSheetsCounter).Name)) And _
                UCase(wbCurr.Sheets(intSheetsCounter).Name) <> STRSHEETNAME_PARAMETERS Then
                wbCurr.Sheets(intSheetsCounter).Visible = xlSheetHidden
            End If
        End If
    Next
End Sub

Function funcChkSheetExist(wbCurr As Workbook, strSheet As String)
On Error GoTo errHandling
    Dim tmpVal
    tmpVal = wbCurr.Sheets(strSheet).Range("A1")
    funcChkSheetExist = True
    Exit Function
errHandling:
    funcChkSheetExist = False
End Function

Learning VBA

A few years ago, I have raised the use of VBA in the company.
Now it is the time for my colleague to teach the new comers about it.

Here is a list of stuffs that where is the start to learn and where is the level becoming master.
1. Studying Excel basic functions:
a. Formulas:
i. address related: index, indirect if, iserror, array formula
ii. calculation related: sumif, subtotal
iii. resolving address: row,col, address, indirect
iv. condition: if(and/or/not), iserror
v. array formula
take a little practice also with formula auditing
b. Features :
pivot table, consolidation, import text /web
c. Charting:
be familiar with data range/axis/legend
d. Formating:
Cell formating: Custom formating, conditional formating,
Data arrangement: filter (advanced filter)/sort page
Printing: page break view/ printing options
operation: paste special,
Input form solution: window freeze(ctrl+home), protect(Tab for navigation), validation,
Position:R1C1(R1C1 vs R1C[-1]), Naming, copy and paste with formulas reference ($A$1 vs $A1)

Actually most of the above function could be replaced by some VBA code,
However, it is recommended to employ the original Excel function as they are much efficient and "bug free"

2.VBA
a. Most importantly: Record macro and edit it.
b. Basics: variable, dynaming array, looping, sub and function (By ref/By val), global/public/private, msgbox
c. Excel Related:
i. application.workbook.worksheet.range("Range Name")
ii. selection.copy/activesheet.paste
iii. Calculation, screenupdating
iv. try to launch the code in legacy version of excel (2000/XP/2003) if needed,
some option are not be available in the those legacy version, thus causing error.
d. with other application:
Files(FileSystemObject),API,Data Access(ODBC/JET), Inter office component
e. Excel as input form: Form show/Hide, sheet controls
f. programming practice: modulisation,naming convention

If you also working on other Languages too,
you should also familar with how to connect to excel too.
E.g. using com object in pre-.net language.
and using interop in .net languages.

VBA Challenge

Whew, quite busy this week. Just keep posting on...

http://blogs.msdn.com/officepalooza/

A nice exercise on VBA.
I seldom use Powerpoint/Access VBA actually,
let's have a review lesson with it.

"Thrid Place" in Novice Challenge:

Novice Challenge Summary:

1.Excel: Range / Formating
2.Word: Edit:Replace
3.Excel: Editing(Formula,Range )
4.Word: Formating(FontName, Style)
5.Excel: Formating(Sheet Name)
6.PowerPoint: Slides(Edit Content0/Shapes
7.Excel: Edit(Paste Special)
8.Word:Reference:Insert from another word
9.Word:Editing(Page Break)/ statistics(Page Count)
10.PowerPoint:Custom Slide show

Experienced Challenge summary:
1.PowerPoint:Shapes
2.Word:Edit(Word)FileSystemObj(Or Read multiple doc)
3.PowerPoint:Slide(Content),Control Objs
4.Excel:Edit(Formula),Charts
5.Access:Form,DAO
6.Word,Access:Edit(Word),use DAO in Word
7.Access:DAO
8.Excel,PowerPoint: application.ontime, Lanuch powerpoint
9.PowerPoint:Random,Shapes,WAIT
10.Excel:Edit(Range,Sorting),Random

Excel VBA performance

A nice guidelines on VBA performance
http://blogs.msdn.com/excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx#comments

Just would like add one more point:
Instead of re-inventing the wheel, Make use of the built in function.
For example, making use of pivot table is much faster than writing aggregation by mutliple for-loops.
Application.WorksheetFunction does good performance over looping too.