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
So far in this series about using VBA to control other Office applications, we have looked at the basics of controlling other office applications and specifically how to create macros to control Word from within Excel. Our approach for creating macros to control Word was using the Macro Recorder. We record very short actions, copy … Read more
When looking at other people’s VBA code in books or on the internet we often see the Dim statement being used to declare variables. It is possible to write valid VBA code without declaring variables, so why bother? The default settings for variables When variables are declared, they can be given a type. Types include … Read more
This post is the second in a series about controlling other applications from Excel using VBA. In the first part we looked at the basics of how to reference other applications using Early Binding or Late Binding. In this post, we will look at how we can automate Word from Excel even though we don’t … Read more
Whilst this site is primarily about using Excel, there are times when Excel is not the best tool for the job. Each Office application is designed for different tasks. Tables and charts may be easily prepared in Excel, but they are often exported to PowerPoint, Word or Outlook for the purposes of presentation and distribution. … 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
Last week we looked at linking Excel directly to Word documents. This can create significant time saving as the Word document updates automatically whenever the Excel file changes. The problem comes when we want to change the file path of the linked document, as it is necessary to change the links one by one. But, … Read more
If there is one thing I am certain of, it is this: There is always something new to learn in Excel. Some of those things will be small little tips and tricks, which might help you a bit, but other things you learn will be game changers. These game changers are the things which save … Read more
A few weeks ago, I wrote a post about linking Excel files into PowerPoint. Whilst PowerPoint is probably the application I use most (after Excel of course), in third place would be Word. If, like me, you are regularly creating Word documents, or using standard templates, then learning how to link Excel to Word can … Read more
Note: This method should only be used for Excel 2013 and Prior. There is a new method using the FORECAST.ETS function. Check it out here. In my opinion, one of the best functions in Excel is FORECAST. We can feed this function values from the past and it will use linear regression to forecast a … Read more