A couple of weeks ago we were preparing for a presentation. Much of the presentation is constructed from linked Excel worksheets into PowerPoint. This technique is great because the presentation automatically updates each time the Excel worksheets change. In the presentation, there are also some PowerPoint tables in the default blue striped layout. Then came … 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
Have you ever wanted to display a character in Excel which isn’t on the keyboard? Maybe you’ve wanted to use a ÷ or maybe an ü, but you just weren’t able to find it. A standard keyboard can produce 96 characters, but lots fonts include up to 255 characters. Most countries have different keyboard layouts to … Read more
Working with workbooks is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Contents: Referencing workbooks by name Referencing workbooks by their status Referencing workbooks by when they were opened Assign a workbook to a variable Create new workbooks Opening workbooks Counting … Read more
Working with worksheets is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Referencing worksheets from the workbook ‘Reference a worksheet in another workbook Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet in the same workbook as the VBA code ThisWorkbook.Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet … Read more
PDF files are pretty much the norm for distributing reports these days. They provide a nice easy way to collate documents from different sources for distribution. However, once a document is in a PDF format getting that information back into a usable form is a nightmare. If we try to copy and paste a data table … Read more
Looping through every worksheet or every workbook is a very common action. One which you may be doing on a regular basis. The code below should set you on the right path. Loop through every worksheet Sub LoopThroughWorksheets() ‘Create a variable to hold the worksheet and also the output message Dim Ws As Worksheet Dim … Read more
At some point in your VBA life it will be necessary to display or re-order an array/list alphabetically (or reverse alphabetically). Unfortunately, VBA doesn’t have a function for sorting arrays automatically, but with a little bit of coding we can create a reusable function which would achieve something similar. Setting up the example This first VBA … Read more
Hey! It’s Spreadsheet Day! Woop Woop! I’m not even kidding, this is a real thing. Just google it and you will see. On 17 October 1979 the first spreadsheet program, Visicalc, became available for on the Apple II computer. This is what it looked like back then (image from Wikipedia) Whilst a lot has changed … Read more
The camera tool is probably one of the most underutilized tools in Excel. The reason it is underutilized is because it is hidden. This simple tool will take a picture of a range of cells and place the picture of those cells on another part of the workbook. You can move, re-size and add picture formatting options the picture. … Read more