VBA Code Snippet

In a previous VBA Code Snippet, we covered protecting and unprotecting worksheets.  In this post, we will consider protecting and unprotecting workbooks.  The two topics are closely related, so if this post does not provide the information you need, then read that post too. Before getting started, there is an important change in Excel 2013 … Read more

Recently, I have been working on a project which requires the zipping and unzipping files and folders.  Zip files are now a common method of compressing files and folders for sharing.  As software becomes more complex, file sizes increase, however there is often a limit to the file size an e-mail provider will allow.  For … Read more

Creating, deleting and renaming folders is a common requirement when automating processes with VBA.  The code snippets below should be sufficient to complete the most common folder tasks. All the code examples below use the built-in Dir() function and its derivatives.  The File System Object methods are not covered in this post but will be … Read more

If you work in a team, there are times when more than one person tries to use a file.  Maybe your line manager is checking a piece of information in a workbook, whilst at the same time you are trying to rename the file.  When you’re running files manually, it normally provides a reasonable warning … Read more

In Excel it is possible to create Custom Lists which are available for use in the spreadsheet or VBA application.  The default lists provided by Microsoft are the days of the week Monday – Sunday (or the shorter version of Mon-Sun) and the months of the year January – December (or the shorter version Jan-Dec). … Read more

In the Windows Explorer it is possible to view a document’s properties.  Right-click on the file and select Properties. There is useful information in here, such as author, creation date, last saved date etc.  The good news is, we can access this information using VBA with  the BuiltinDocumentProperties Property. Not all document properties are populated … Read more

In a Macro or Add-in there may be times when you require the user to select a file.  It is possible to use the standard FileDialog box for this purpose.  The following code is intended to provide the most common settings which you can amend to meet your needs. Open the file DialogBox for selecting … Read more

Named Ranges are a very useful feature of Excel, but a lot of users do not know why or how to use them.  This post is not about how to use Named Ranges, but specifically how to create, delete and manage Named Ranges automatically with VBA. Create Named Ranges Named Ranges can be created at … Read more

Manipulating ranges and cells is one of the most common actions in VBA.   You can use the following to either learn from or just to copy and paste into your own code. Referencing ranges & cells from the worksheet ‘Reference range by address Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).Range(“A1:D4”).[Other properties and actions] ‘Reference cells by row and column – Cells(Row,Column) … Read more