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
At the start of this year, I wrote a post entitled Turn a string into a formula with EVALUATE. This post used an Excel 4 Macro as a possible option for turning a string into a formula. These types of Macros were superseded when VBA was introduced in Excel version 5, hence why any macros … 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
When recording a macro, Excel includes all the scroll bar movement in the code. Normally, we delete this code, as we just don’t need it. But occasionally, being able to control the scroll bars and their display properties is useful. Display or Hide Scroll Bars Scroll bars can be displayed or hidden. It is an … 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
In Excel, the standard column chart will display all columns with the same width at regular intervals. However, in some circumstances, it would be better for the width of each column to be different. For example, where each column represents different ranges of data. This same principle of column width also applies to histograms. Excel … Read more
Happy Spreadsheet Day! What are your feelings about allocating days to celebration or awareness of certain things? Like International Talk Like a Pirate Day (19th September), Oatmeal Nut Waffles Day (11th March) or Bathtub Day (7th October)? Well, the 17 October is International Spreadsheet day. Normally, I would think these days are pointless, but I … Read more
I love using the double VLOOKUP trick to get some lighting fast calculation times in Excel. The drawback is that the list being looked up has to be sorted to prevent errors. If a list were not sorted, it would be great to see a warning. Then the user can take action to sort the … 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