Office 2016 Integrated Solution Development
0.0 (0 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Office 2016 Integrated Solution Development to your Wishlist.

Add to Wishlist

Office 2016 Integrated Solution Development

Learn how to develop solutions in Office 2016 using VBA - without learning VBA! Let the programs write the code.
0.0 (0 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2 students enrolled
Last updated 9/2017
English [Auto-generated]
Price: $60
30-Day Money-Back Guarantee
  • 7 hours on-demand video
  • 80 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Develop integrated solutions using Excel and Word.
  • Tweak the VBA code written by the macro recorder, adding variables and arguments.
  • Create compound macros, which themselves call other macros, often macros in other files or programs.
  • Develop VBA userforms to solicit and validate data from users.
  • Build solutions that pass data from Excel to macro-enabled templates and trigger file-handling macros in other programs.
View Curriculum
  • You should have a basic familiarity with creating and using macros in Excel and Word.
  • You need to have a version of Office installed on your computer. The online version won't suffice.

This course will teach students how to develop integrated Office solutions using VBA.  Solutions will use macros and macro-enabled templates to pass data back and forth among different software applications within Microsoft Office, collecting data and soliciting input from other files as well as from users.  Students will not need to know how to code in VBA; the curriculum will teach them how to customize the code written during macro creation. 

Solutions can be adapted and applied to real world situations, including user-driven surveys in Word, data-driven score sheet production in Excel, generating letters in Word using data from Excel (and saving them as Word or pdf files), and using Excel data selections to drive dynamic input of text and images into existing Word documents.

Who is the target audience?
  • Any Office user who wants to learn to develop solutions without having to learn VBA first.
Compare to Other Microsoft Office Courses
Curriculum For This Course
99 Lectures
Start Here
1 Lecture 05:37
Creating macros
9 Lectures 23:49

In Office applications, the Developer menu on the ribbon is not enabled by default.  You will need to enable this before you can begin recording macros.

Preview 02:31

Be careful when creating macros in Word.  The default location where they're set to be saved is in the Normal.dotm template.  This is the "blank document" template.  As a general rule, we will save all macros in the file for which we are creating them.

Creating Macros in Microsoft Word

Because of the automation power that macros represent, care must be taken with regards to security.  We will not change the default settings (Disabled with notification), which means we will have to put up with the small extra step of enabling macros each time.  In addition, we will not add every folder in which we're working (and there are a lot of them) as a Trusted Location.

Once a solution has been developed and is ready for use, then its folder may be added as a Trusted Location.

Preview 02:08

In Word, we don't get to assign macros to shapes.  So, to make it easy for our users to run the macros, we will sometimes add keyboard shortcuts.  Keep in mind, thought, that, like macros themselves, keyboard shortcuts want to save in the Normal.dotm (blank document) file.  Always save keyboard shortcuts in the file for which they're being created.

Assigning keyboard shortcuts to Word macros

In Office applications, the Developer menu on the ribbon is not enabled by default.  You will need to enable this before you can begin recording macros.

Enabling the Developer Tab in Excel

Creating macros in Excel is not very different from the process in Word.  The actions you can record are, of course, different because the programs do different things.  Excel does have an extra component, which will be discussed in the next video.

Creating macros in Excel

Relative Reference in Excel macros pertains to the position and movement during the macro recording process relative to where you start recording. There is not really such a thing as "Absolute Reference".  This is simply the absence of Relative Reference, and is the default setting.  

Relative vs absolute reference in Excel macros

Unlike Word, in Excel we can assign macros to shapes or command buttons that are available on worksheets.  We can, of course, also assign keyboard shortcuts, but it is usually much easier for users to click a strategically placed button than to remember the correct sequence of keystrokes when it comes time to run a macro.

Preview 02:22

Ribbon Tabs, Groups, and ribbon buttons are all elements of an individual's installation of Office.  You can configure your installation of Excel to have the convenience of these menu customizations, but if others are going to be using the solution, you can't enforce their software's settings to yours.

Creating Ribbon Tabs, Groups, and Buttons
Navigational macros
4 Lectures 24:56

While Word lacks the accuracy of cell reference, like Excel does, it is possible to use arrow keys, along with Ctrl and/or Shift keys to navigate precisely around a document.  We want to capture these movements as individually named macros because we can then call those macros by name in the context of constructing compound macros.

Navigational Macros in Microsoft Word

The easiest way to move to a specific place in a Word document is to create a bookmark.  These can go anywhere in the document you want to specifically go to for the purpose of entering text or data.  You can then create a macro to go to that bookmark, thereby capturing in code the act of navigating to a specific place in the document.

Bookmarks and Navigational Macros in Word

Cell addresses in Excel make it easy to navigate to specific locations in workbooks.  There are also movement combinations involving the Ctrl and Shift buttons.  Excel also has the notion of Relative Reference, which measures the difference and direction of movement relative to where you started recording, instead of indicating navigation to specific cells.

Navigational Macros in Excel

This video shows a useful technique for creating macros that find the next available free row at the bottom of a range of data.  When we are developing a solution in which users will be constantly entering data, we need to have a way to navigate to the next available free row.  As the data table grows, that next free row will be further down on the sheet.  

Internal navigation in Excel
Text and data input macros
10 Lectures 22:09

In this video, we look at a simple text entry macro in Word, but show that we can change the text in VBA, so that what gets "typed" by the macro is whatever is in the quotation marks in VBA.  Later, we'll replace the quotation marks and text with string variables that we populate with different types of input.

Word Text Input Macros

This video simply demonstrates that text formatting can be changed during the macro recording process.

Word Formatting macro

This video demonstrates how to record a macro to find and replace all instances of one word or phrase with another word or phrase.  As with simple text entry, we can use this technique to replace all occurrences of some filler phrase with its "real" replacement, which we identified via input or data declaration.

Preview 01:28

When we record a macro in which we insert an image, what gets captured as code is that image's file path.  We can change that code to use a path to a different file, which we could define elsewhere in data.

Word Image Insert Macros

Inserting a table with a macro is useful if you want to have a quick way to insert a specifically configured table elsewhere in a document simply by calling the macro.

Word Insert Table macro

This exercise combines several actions together in one macro.  It is created on one line of text, but demonstrates how the same actions could be run on a different line of text in the document, without having to designate any navigation.

Word Multi-Action macro

Text or data input code in Excel is different from that of Word because it always occurs inside a cell.  We try to keep navigational macros (for cell selection) separate from text input macros.

Data Input Macros in Excel

Besides simple text or numbers, it is possible to create macros to "insert" formulas.  This technique could be used to build a sort of calculator that would insert different formulas to process the same set of data.

Creating Macros to Insert Formulas in Excel

In addition to keyboard shortcuts, Excel lets us draw out shapes and command buttons and assign a macro to it.  Having a button prevents users from having to open the Macros menu and finding a macro to run manually.

Preview 04:30

Declaring a string variable (for text entry) inside a text entry macro, populating that variable with input from the user (by way of an Input box), then passing that variable to the text entry macro.

Preview 04:30
Calling macros internally
7 Lectures 34:53

A complex or compound macro is one that calls other macros that have been recorded.  We don't have to preface the call with any verbs; we simply type the name of the macro.  Each macro that is called will finish running before the next one is called.

Creating Compound Macros in Word

The Madlib file in this exercise already has bookmarks inserted in the correct spots, but not macros to navigate to them.  Since the numbering convention for the bookmarks is standard, we can create one navigational macro and then copy, paste, and edit the code as many times as needed.

At each bookmark, a specific word type is needed.  In order to solicit the correct word type from the user, we want to use Input Boxes.  There is one macro already created to solicit a noun.  Copy, paste, and edit the copies as needed to create macros that solicit different word types.

Create a Madlib using a partially completed document

This exercise is a continuation of what we learned previously, except that now there are no bookmarks or macros at all, only the starting document with [filler words] where the input is supposed to go.

How to Call Macros Within Other Macros

Create separate macros, which perform small actions only, sometimes with Relative Reference turned on, sometimes without.  These macros will become the "building blocks" of the compound macro we will construct.

Compound macros in Excel

Just as we can customize a text or data input macro, we can edit a navigational macro to ask the user what cell or named range they want to go to.  This exercise makes several iterative modifications to the first navigational macro we created previously.

Customize an Excel navigational macro for user input

Create a compound macro that runs all the previous macros in order.

Preview 01:46

The last navigational macro returns the user to a specific cell (A7).  Insert a shape above or near A7 and assign the compound macro to it.  This will allow the user to click the shape, run the macro on the next target row, and then simply repeat that process for the rest of the month's data.

Assign the compound macro to a shape
VBA Userforms
11 Lectures 47:01

This is a quick introduction to userforms in Excel.  In this first exercise, there is not an existing file to start with.   Create a new workbook, save it as a macro-enabled workbook, and begin working in the VBA Editor.

VBA UserForms and Form Properties

The Form Editor of the VBA Editor has a lot of moving parts.  There are a lot of properties associated with each control type, including the form itself.  This exercise focuses on only a few of the control properties that we mostly commonly configure.

UserForm Layout Controls and Control Properties

This section deals with validating the correct entry of data into the form by means of a series of "If...Then" statements.

UserForm Data and Selection Controls

After the data has been validated, we need the OK button to pass the data as a new row on one of the worksheets.  Because this data table will grow, we first need to tell it how to find the next available row.  So we first need to create navigational macros, then call them as needed as part of the OK button's function.   

Form Command Buttons

The "Clear" button on a userform is supposed to clear out any responses put in by the user but not close the form itself.  We can do this by setting each of the control's values to "" (nothing).

Programming a Userform Clear Button

Adding a button to a worksheet to open a form

Creating a responsive userform

Create and use navigational macros

Program the userform OK button

Programming the Clear and Cancel button
Declaring and Using Public Variables
4 Lectures 12:13
Introduction to Public Variables

Intro to Public Variables

Public Variables in Word

Create a macro to call up the userform
Macros With Arguments
4 Lectures 19:43
Intro to Arguments and Parameters

Methods for passing parameters

Word macros with parameters

Using Parameters with Word Macros
Macro-enabled Templates
5 Lectures 09:46
Saving a file as a template

Saving a macro-enabled template

Excel Cell Locking and Sheet Protection

Editing a Macro-enabled Template

Word macro-enabled template
Introduction to Document Events
2 Lectures 05:48
Document events in Word

Workbook events in Excel
10 More Sections
About the Instructor
Mr. Russell Mangum
4.0 Average rating
60 Reviews
2,153 Students
5 Courses
Experienced developer and tech instructor

I have been a technology instructor for over 20 years, teaching enterprise level hardware, networking, operating systems, virtualization, applications, solutions development, systems management, and security. 

Having worked with every version of SharePoint since its beginning (including the Beta version (Tahoe)), I plan to offer advanced courses in SharePoint and InfoPath solution development.  I also have outlines for Office 365 solutions development.