VBA macros

xlsgen > overview > VBA macros

Editing macros in Excel

xlsgen can read and extract in plain-text any VBA macro stored in an Excel spreadsheet, provided the spreadsheet is not password-protected.

VBA macros are stored at several levels :

workbook level (event handlers)
worksheet level (event handlers)
module level (recorded macros, third-party macros)

The following source code extracts VBA macros from each of the mentioned levels :

Java code

XlsWorkbook wbk = engine.Open( "Book1_testmacros.xls", "" );

XlsWorksheet wksht = wbk.getWorksheetByIndex(1);

//read the workbook macros

String sWbkMacros = wbk.getVBAMacros();

//==> will return this :
//
//Private Sub Workbook_Open()
//   MsgBox "Workbook_Open"
//End Sub


//read the worksheet 1 macros

String sWkshtMacros = wksht.getVBAMacros();

//==> will return this :
//
//Private Sub Worksheet_SelectionChange(ByVal Target As Range)
//   Target.Interior.ColorIndex = 5
//End Sub


//read macros for each module

int nbModules = wbk.getVBAModules().getCount();
for (int i = 1; i <= nbModules; i++)
{
    String sModuleMacros = wbk.getVBAModules().getItem(i).getVBAMacros();
}

//==> will return this (Module1) :
//
//Public Function MySum(a, b) As Integer
//   MySum = a + b - a * b
//End Function

//==> and this (Module2) :
//
//Sub Macro1()
//'
//' Macro1 Macro
//' Macro recorded 2 by xyz
//'
//
//'
//   Range("D5:D9").Select
//   With Selection.Interior
//       .ColorIndex = 6
//       .Pattern = xlSolid
//   End With
//   Range("E5:E9").Select
//   Selection.Font.ColorIndex = 3
//   With Selection.Interior
//       .ColorIndex = 6
//       .Pattern = xlSolid
//   End With
//End Sub

VB code

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("Book1_testmacros.xls", "")

Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)

' read the workbook macros

Dim sWbk As String 
sWbk = wbk.VBAMacros

' ==> will return this :
'
'Private Sub Workbook_Open()
'    MsgBox "Workbook_Open"
'End Sub


' read the worksheet 1 macros

Dim sWksht As String 
sWksht = wksht.VBAMacros

' ==> will return this :
'
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'    Target.Interior.ColorIndex = 5
'End Sub


' read macros for each module

Dim nbModules As Integer 
nbModules = wbk.VBAModules.Count

Dim i As Integer

For i = 1 To nbModules
    Dim sModule As String 
    sModule = wbk.VBAModules.Item(i).VBAMacros
Next

' ==> will return this (Module1) :
'
'Public Function MySum(a, b) As Integer
'    MySum = a + b - a * b
'End Function

' ==> and this (Module2) :
'
'Sub Macro1()
''
'' Macro1 Macro
'' Macro recorded 2 by xyz
''
'
''
'    Range("D5:D9").Select
'    With Selection.Interior
'        .ColorIndex = 6
'        .Pattern = xlSolid
'    End With
'    Range("E5:E9").Select
'    Selection.Font.ColorIndex = 3
'    With Selection.Interior
'        .ColorIndex = 6
'        .Pattern = xlSolid
'    End With
'End Sub

C# code

IXlsWorkbook wbk = engine.Open( "Book1_testmacros.xls", "" );

IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);


//read the workbook macros

String sWbk = wbk.VBAMacros;

//==> will return this :
//
//Private Sub Workbook_Open()
//   MsgBox "Workbook_Open"
//End Sub


//read the worksheet 1 macros

String sWksht = wksht.VBAMacros;

//==> will return this :
//
//Private Sub Worksheet_SelectionChange(ByVal Target As Range)
//   Target.Interior.ColorIndex = 5
//End Sub


//read macros for each module

int nbModules = wbk.VBAModules.Count;
for (int i = 1; i <= nbModules; i++)
{
    String sModule = wbk.VBAModules.get_Item(i).VBAMacros;
}

//==> will return this (Module1) :
//
//Public Function MySum(a, b) As Integer
//   MySum = a + b - a * b
//End Function

//==> and this (Module2) :
//
//Sub Macro1()
//'
//' Macro1 Macro
//' Macro recorded 2 by xyz
//'
//
//'
//   Range("D5:D9").Select
//   With Selection.Interior
//       .ColorIndex = 6
//       .Pattern = xlSolid
//   End With
//   Range("E5:E9").Select
//   Selection.Font.ColorIndex = 3
//   With Selection.Interior
//       .ColorIndex = 6
//       .Pattern = xlSolid
//   End With
//End Sub

C++ code

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"Book1_testmacros.xls", L"" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

// read the workbook macros

WriteFile(L"Workbook.txt", wbk->VBAMacros);

// ==> will return this :
//
//Private Sub Workbook_Open()
//    MsgBox "Workbook_Open"
//End Sub


// read the worksheet 1 macros

WriteFile(L"Worksheet1.txt", wksht->VBAMacros);

// ==> will return this :
//
//Private Sub Worksheet_SelectionChange(ByVal Target As Range)
//    Target.Interior.ColorIndex = 5
//End Sub


// read macros for each module

int nbModules = wbk->VBAModules->Count;
for (int i = 1; i <= nbModules; i++)
{
    WCHAR s[128];
    swprintf(s, L"AdditionalModule%d.txt", i);

    WriteFile(s, wbk->VBAModules->Item[i]->VBAMacros);
}

// ==> will return this (Module1) :
//
//Public Function MySum(a, b) As Integer
//    MySum = a + b - a * b
//End Function

// ==> and this (Module2) :
//
//Sub Macro1()
//'
//' Macro1 Macro
//' Macro recorded 2 by xyz
//'
//
//'
//    Range("D5:D9").Select
//    With Selection.Interior
//        .ColorIndex = 6
//        .Pattern = xlSolid
//    End With
//    Range("E5:E9").Select
//    Selection.Font.ColorIndex = 3
//    With Selection.Interior
//        .ColorIndex = 6
//        .Pattern = xlSolid
//    End With
//End Sub


void WriteFile(LPWSTR filename, BSTR contents)
{
    FILE* f = _wfopen(filename, L"w+b");
    if (f)
    {
        fwrite((void*)contents, ::SysStringLen(contents) * 2, 1, f);
        fclose(f);
    }
}