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
Have you heard of the mysterious “array formulas”? Do you use them? Or are you like most Excel users, you’ve placed them in the “too hard” pile, never to be seen again. They always seem to crop up in forums as solutions to some tricky formula questions. In this post, I want to introduce to … Read more
Do you ever find yourself typing out the same standard lists over and over again, such as lists of products, cost centers, projects or companies? I do. It is essential these lists don’t exclude any items and are presented in the same order each time. Lists of 4 or 5 things are easy to remember, … 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
The code below is intended to be a basic reference to anybody trying to use VBA code for Powerpoint. It’s certainly not complete, but it is code I’ve used for various reasons. I will try to add to and improve this reference as I use more PowerPoint VBA code. Referencing presentations, slides and shapes ‘Set … 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
Often when creating a macro, we may need to know the save status a file we are working with. This post explores some of the options available for understanding and controlling this important aspect of file control. Using the .saved property Within the Visual Basic Editor we can view the Saved property of a workbook. … Read more
Whenever I see promotional images for expensive BI Dashboard software, they always show the same thing – a map with countries or regions highlighted in different colors. It sends the subtle message that maps are the pinnacle of Business Intelligence (whether they are or not is a different matter altogether). Until Excel 2016, these Map … Read more
There are a lot of circumstances where it is useful for a specific workbook, or workbooks to open whenever Excel starts: Save time by opening workbooks you always use To control the calculation mode To trigger VBA events when you open Excel This isn’t just something interesting you can do with Excel, it’s actually a … Read more