Excel has zoom settings for worksheets, UserForms and print settings. Each of these serves different purposes and used at different points, but they can all be controlled by VBA. Worksheet zoom settings The worksheet zoom setting in Excel will always be an integer. If the value set by VBA contains decimal places it will be … Read more
Often we want to determine what type of object has been selected in Excel. It helps with error checking prior to running a piece of code. Basic code This action is just a just a single line of code: Sub testSelection() MsgBox TypeName(Selection) End Sub Incorporate the check into a Select statement If you want … Read more
If you speak to anybody who works in the accountancy field, they will always be talking about the “month end”. In all medium and large companies there will be a process at each month end which requires statements to be issued and reports to be distributed. This monthly process fills most finance people with a … Read more
It is frustrating that Excel, PowerPoint and Word work natively using a measurement known as points. However, measurements within the standard application menus are set in centimeters or inches. This can be seen when setting the position and size of a shape in PowerPoint through the standard menus. Yet, when using a macro to retrieve … Read more
Password protecting an Excel file achieves two main objectives (1) prevents unauthorized access to the Excel file (2) prevents unauthorized modification of the Excel file. File-level protection is not the same as worksheet protection or workbook protection. Worksheet protection controls what a user can or cannot do on a worksheet, such as inserting rows or … Read more
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
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