Rectangle 27 13

VBA security is widely considered to be pretty poor. The VBA code isn't compiled, and the source is available in the excel file. The password protection is pretty easy to circumvent.

As I understand it, Office 2003 and earlier saves the vba code as part of the binary format of the worksheet (or document / presentation). When you fire up the VBA IDE, it simply looks to see whether the VBA code has been "protected" or not. This doesn't mean it's encrypted - just unavailable for viewing. The theory is that this stops your users from meddling with your code, but a hard-core coder would be able to get around the password.

Office 2007 does encrypt macros (don't ask me how or what algorithm). This is necessary presumably because XLSM files (or any Office 2007 file) are just zip files with a different extension. Anyone can get into those files and poke around.

To answer your last question - how does the password removal work on older Office formats, I'm not entirely sure. Different vendors will possibly approach the problem different ways, but I suspect the most common approach will be a brute-force attack on the passwords until a match is found.

The Excel VBProject object has a Protection property which will return different enumerations depending on the protection status of the macro (vbext_pp_locked if the macro is protected, for example). If you were to keep trying passwords programmatically until the vbext_pp_locked evaluated to false, you would have found your password.

I wonder if there is an option in 2007 to store only the byte code for macros within a workbook, allowing you to deliver functions without the source?

That functionality isn't (AFAIK) baked into any version of Office. Rather, the expectation is that if you specifically want automation for an Office file bundled, you'll use the Visual Studio Tools for Office (VSTO) to write your own DLL and store that as an add-in.

FYI - I created an xlsm (using the converter available for 2003). Changed the xlsm extension to xip, unzipped and looked inside. All the VBA was inside a single file 'vbaProject.bin'. In a hex editor it appeared to be build from exactly the same BIFF blocks that other excel files are made from. All strings in the macros were plainly visible. So it doesn't look as if the encryption has changed too much.

@DaveParillo - Huh. Thanks for that. I was led to believe that the code was encrypted. Should probably have checked for myself.

security - How password protection of Excel VBA code works? - Stack Ov...

security excel vba excel-vba password-protection
Rectangle 27 1

Here is a simple Python 2.7 solution I've cooked for you:

It depends only on the OleFileIO_PL module which is availble from the project page The good thing with OleFile parser is that it is not aware of the "excel-specific" contents of the file ; it only knows the higher level "OLE storage". So it is quick in analyzing the file, and there is no risk that a potentially harmful macro would execute.

import OleFileIO_PL
import argparse

if __name__=='__main__':
    parser = argparse.ArgumentParser(description='Determine if an Excel 97-2007 file contains macros.', epilog='Will exit successfully (0) only if provided file is a valid excel file containing macros.')
    parser.add_argument('filename', help="file name to analyse")
    args=parser.parse_args()

    # Test if a file is an OLE container:
    if (not OleFileIO_PL.isOleFile(args.filename)):
        exit("This document is not a valid OLE document.")

    # Open an OLE file:
    ole = OleFileIO_PL.OleFileIO(args.filename)

    # Test if known streams/storages exist:
    if (not ole.exists('workbook')):
        exit("This document is not a valid Excel 97-2007 document.")

    # Test if VBA specific streams exist:
    if (not ole.exists('_VBA_PROJECT_CUR')):
        exit("This document does not contain VBA macros.")

    print("Valid Excel 97-2007 workbook WITH macros")

I tested it on a couple of files with success. Let me know if it's suitable for you

The script works, thank you very much...

Verifiy if an xls file contains VBA macros without opening it in MS Ex...

excel vba excel-vba binary ole
Rectangle 27 4

Moderators, I'm making an effort, assuming you'll treat me with due respect in thefuture.

All local variables are stored on the stack as with all languages (and most parameters to functions). When a sub exits the stack is returned to how it was before the sub executed. So all memory is freed. Strings and objects are stored elsewhere in a object manager or string manager and the stack contains a pointer but vb looks after freeing it. Seting a vbstring (a bstr) to zero length frees all but two bytes. That's why we try to avoid global variables.

In scripting type programs, typeless programming has many advantages. Programs are short and use few variables so memory and speed don't matter - it will be fast enough. As programs get more complex it does matter. VB was designed for typeless programming as well as typed programming. For most excel macros, typeless programming is fine and is more readable. Vbscript only supports typeless programming (and you can paste it into vba/vb6).

dynamic typing vs static typing is the exact terminology. Typeless would imply that variables have no type, which is not true.

Why should I use the DIM statement in VBA or Excel? - Stack Overflow

excel vba excel-vba
Rectangle 27 6

It's VBA. VBA means Visual Basic for Applications, and it is used for macros on Office documents. It doesn't have access to VB.NET features, so it's more like a modified version of VB6, with add-ons to be able to work on the document (like Worksheet in VBA for Excel).

No, VBA is not a modified version of VB5/6 -- rather, it's a subset. Take a look at the Object Browser in the VB5/6 IDE, and you'll see the complete VBA object library there to use and abuse. That said, VBA does (typically, though not necessarily) have additional object libraries that load by default, and provide global objects by default, which relate to the host environment du jour.

vb6 - Difference between Visual Basic 6.0 and VBA - Stack Overflow

vba vb6
Rectangle 27 16

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 16

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 16

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 10

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 10

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 10

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 10

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 10

Expanding on patszim's answer for those in a rush.

  • Insert a new module (code needs to reside in the module otherwise it doesn't work).
  • In the newly inserted module,
  • add the following code: Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function

The regex pattern is placed in one of the cells and absolute referencing is used on it. Function will be tied to workbook that its created in. If there's a need for it to be used in different workbooks, store the function in Personal.XLSB

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 1

xlsm means that the Excel Workbook containts macros (VBA code)

If you want to save that workbook as an xlsx (an Excel Workbook WITHOUT macros), Excel will notice and will REMOVE all VBA code.

So, there's no point in saving as xlsm to an xlsx (as all VBA code will be removed), unless you have no VBA code in your xlsm (and then there's no point to using a xlsm in the first place).

java - Can we convert xlsm to xlsx using POI apache - Stack Overflow

java excel apache-poi xlsx xlsm
Rectangle 27 1

Actually there only a handful of solutions to track and compare changes in macro code - most of those were named here already. I have been browsing the web and came across this new tool worth mentioning:

  • version control for Excel sheets and VBA modules
  • preview and diff changes before committing a version
  • great for collaborative work of several users on the same file (track who changed what/when/comments)
  • compare versions and highlight changes in code line-by-line
  • suitable for users who are not tech-savvy, or Excel-savvy for that matter
  • version history is stored in Git-repository on your own PC - any version can be easily recovered

Best way to do Version Control for MS Excel - Stack Overflow

excel version-control
Rectangle 27 0

How do you get to the code? If through Excel by using the VBE, for instance by pressing Alt+F11, then it is VBA. If you open a project, then it is probably VB (VB6). Note that VB.NET use the .vb extension. Moreover, since VBA is a subset of VB then if you export VBA from a VBE project, the exported files default to their VB cousin's file extensions.

Like I said, VBA (6.x) is a subset of VB6. The Excel part is just an object model that uses VBA. VB.NET is different and is not the case because the file extensions are wrong.

Therefore, concentrate on learning on VBA and Excel 2007 Object Model

I open the AddIn using Visual Studio2010 and that's how work with the code,both C# and VB/VBA.

You cannot open the VB/VBA code through Visual Studio 2010 if the project is C#. Are you opening the Excel workbook through VS then getting to the code?

Also, like I said, VBA (6.x) is a subset of VB6. The Excel part is just an object model that uses VBA. VB.NET is different and is not the case because the file extensions are wrong.

its a project in C#. I open it in VS2010 and when I run it, it opens up the spreadsheet as the output. Basically, the project just creates an AddIn plugin to Excel 2007. I open the project in VS2010. It contains many folders and one of them has the VB/VBA code in it. Majority of the code is C#. VB/VBA is used only for creating workbook/worksheets.

excel vba - what is the difference between VB vs VBA? - Stack Overflow

vba excel-vba
Rectangle 27 0

If you want to use Python: Take a look at these packages: xlwt (writing to Excel), xlrd (read-only) and csv to work with CSV files (built-in to Python).

  • Open and Read from each CSV file
  • Open and Write to the first sheet of the Excel

You may want to first create a version of the Excel file with only the first sheet blank. Before reading each CSV, keep copying the XLS to a new name (1.xls, 2.xls, etc.) and modify that. Note that with xlwt, the supported Excel versions are: Excel 97/2000/XP/2003. I don't think that includes xlsx format. So save/convert your current excel file to xls if you aren't using any major xlsx features.

If you wan to do it in Excel with VBA:

First off, thank you for getting my first SO experience off to a pleasant start. I now have an additional question regarding the use of xlwt/xlrd. I was successful in copying my template file and saving it (lets say 100 times), but I lose all cell formatting. Is there a simple fix to this? Sorry if this is too basic of a question.

Well I suppose my real issue is the fact that xlrd only extracts the cell values and not the formulas/equations....

@Cole Each time you write to a cell, xlwt removes all formatting. Probably because it expects us to provide all formatting data as well. Check if you can read values (or formulae) and write just formulae or text - like Paste special --> Values. Or you'll have to insert formatting along with the value for every cell, which makes the sheet size very large.

python - Import CSV file into an existing Excel spreadsheet, save that...

python excel vba excel-vba csv
Rectangle 27 0

If you want to use Python: Take a look at these packages: xlwt (writing to Excel), xlrd (read-only) and csv to work with CSV files (built-in to Python).

  • Open and Read from each CSV file
  • Open and Write to the first sheet of the Excel

You may want to first create a version of the Excel file with only the first sheet blank. Before reading each CSV, keep copying the XLS to a new name (1.xls, 2.xls, etc.) and modify that. Note that with xlwt, the supported Excel versions are: Excel 97/2000/XP/2003. I don't think that includes xlsx format. So save/convert your current excel file to xls if you aren't using any major xlsx features.

If you wan to do it in Excel with VBA:

First off, thank you for getting my first SO experience off to a pleasant start. I now have an additional question regarding the use of xlwt/xlrd. I was successful in copying my template file and saving it (lets say 100 times), but I lose all cell formatting. Is there a simple fix to this? Sorry if this is too basic of a question.

Well I suppose my real issue is the fact that xlrd only extracts the cell values and not the formulas/equations....

@Cole Each time you write to a cell, xlwt removes all formatting. Probably because it expects us to provide all formatting data as well. Check if you can read values (or formulae) and write just formulae or text - like Paste special --> Values. Or you'll have to insert formatting along with the value for every cell, which makes the sheet size very large.

python - Import CSV file into an existing Excel spreadsheet, save that...

python excel vba excel-vba csv
Rectangle 27 0

There is no such document. The macros that Excel creates natively are in VBA using COM API, which is same API as used in Python. So the closest you can get is to record a macro using Excel, then look at the VBA code and convert it to the equivalent Python code that uses win32com. You can also search the MSDN docs for the Excel Interop; the functions, classes etc are all identical in win32com. The only tricky aspect of using win32com is the constants; to use those, you have to first generate the Python type library for Excel, using EnsureDispatch or gencache (see Accessing enumaration constants in Excel COM using Python and win32com and error xlPrimary not defined in Python win32com for more info).

Thanks a lot!!!

If one of the answers answered your question please accept it, or add comment for what is missing, or consider deleting your question.

python 2.7 - is there any document to learn coding excel macros using ...

python-2.7 win32com