Microsoft training videos created in 2014 by Wise Owl (page 3 of 4)

Showing videos 41-60 (out of 62)

Excel VBA Part 24 - File Dialogs

Posted by Andrew Gould on 11 March 2014

Using FileDialogs in VBA provides a convenient method for selecting files and folders and performing useful actions on them. This video covers how to use the standard FileOpen and SaveAs dialogs, and how to perform their default actions. The video also covers the use of the FilePicker and FolderPicker dialogs and how to use FileSystemObjects to process a collection of selected files to perform actions such as copying files to another folder.

Excel VBA Part 23 - Text Files

Posted by Andrew Gould on 10 March 2014

The Scripting Runtime Object Library allows you to write VBA code to create, open, read from and write to text files using very simple methods. This video teaches you how to work with tab-delimited and comma-separated value files including how to create them, append data to them and how to read from them. The video also includes a section on using text functions for parsing the contents of a text file and the final section talks about combining these techniques with workbook events to create a simple change log.

Excel VBA Part 22 - Files and Folders

Posted by Andrew Gould on 03 March 2014

The Scripting Runtime Object Library allows you to easily write code in an Excel VBA project which can manipulate the file and folder structure of your computer. It's and incredibly useful, although potentially quite dangerous thing to be able to do and this video will show you how. You'll learn how to reference the Scripting Runtime Library, what a FileSystemObject is and how to use it and how to perform various methods such as create folders, copy and move files and even how to delete them. Towards the end of the video you'll see how to loop over a collection of files in a single folder and then, as an encore, how you can loop through the complete set of folders and subfolders from a given starting point.

Excel VBA Part 21 - User Forms

Posted by Andrew Gould on 28 February 2014

Designing a User Form in VBA is a great way to allow you users to interact with your workbook. This video teaches you how to create forms, how to add controls and manipulate their properties and how to add code the events of the form to make it respond to user actions. You'll also get an insight into how much code goes into performing even very simple validation to control what your users can do with the form.

Excel VBA Part 20 - Event Procedures

Posted by Andrew Gould on 28 February 2014

Writing Event Procedures allows you to create subroutines which run automatically in response to events in a workbook. This video teaches you how to access the events of an object and start writing event procedures for both workbooks and worksheets. You'll also see how to cancel certain events and how to disable events, as well as how to use the parameters of an event procedure.

Excel VBA Part 19 - Error Handling

Posted by Andrew Gould on 24 February 2014

Almost every procedure you write in VBA has the potential to go wrong at some point! Writing error handling code allows your procedures to fail gracefully instead of dumping the user out into the run time error dialog box. This video teaches you all about the On Error statement including how to ignore errors, how to trigger a custom error handler and how to resume running your code after an error has occurred. Towards the end of the video you'll also encounter the Err object which allows you to interrogate the error that has occurred and even raise your own custom errors.

Creating script components within data flow tasks

Posted by Andy Brown on 19 February 2014

Learn how to pass columns into script components, and how to use C# or VB code to massage data values within your script.

Writing scrpt tasks for control flow using Visual Basic

Posted by Andy Brown on 18 February 2014

Learn how to create script tasks in Integration Services to manage control flow, using VB as a programming language

Writing scrpt tasks for control flow using C#

Posted by Andy Brown on 18 February 2014

Learn how to write C# code for script tasks, to manage control flow in SSIS

Excel VBA Part 18 - Creating Functions

Posted by Andrew Gould on 17 February 2014

A VBA function is a procedure that can return a value and this video teaches you how to create them. You'll learn how to declare a function and how to add parameters, including optional parameters and default values. You'll also see several ways to call the functions you've written, including from other VBA procedures and from an Excel worksheet. The video also talks about how to rewrite existing subroutines to replace repetitive code with functions.

Excel VBA Part 17 - For Each Loops

Posted by Andrew Gould on 10 February 2014

A For Each Loop is used to loop over a collection of objects and there are lots of good reasons for wanting to do that in Excel VBA! This video takes you from the basics of the For Each Next statement and using object variables through several examples of looping over worksheets, workbooks, chartobjects and cells and, finally, into writing nested loops for even more power.

Excel VBA Part 16 - For Next Loops

Posted by Andrew Gould on 07 February 2014

You can use a For Next Loop to tell your program to repeat a set of instructions a number of times. This video teaches you how to construct a For Next statement, how to control how many times your loop will run, as well as which direction it will run in. The video also includes several practical examples of using a For Next Loop to process the items in a collection such as worksheets, workbooks and cells.

Excel VBA Part 15.1 - Do Until and Do While Loops

Posted by Andrew Gould on 06 February 2014

Do Loops in VBA allow you to carry out a set of instructions repeatedly until some kind of condition is met. This video teaches you about the basics of the Do Loop statement including how to write Do Until and Do While loops, where to place your conditional statements and how to exit from a loop. The final part of the video provides a couple of longer examples using Do Loops.

Excel VBA Part 14.1- Select Case Statements

Posted by Andrew Gould on 03 February 2014

Select Case statements in VBA are similar to If statements in that they allow to test whether conditions are met and perform different actions based on the result. This video explains how to use Select Case statements in your code, including the various ways to phrase a logical test, how to separate numbers into bands, how to test for lists of values and how to create nested Select Case statements.

Excel VBA Part 13.1 - If Statements in VBA

Posted by Andrew Gould on 31 January 2014

An IF Statement allows you to test whether a condition is met and then perform different actions based on the result. This video takes you from writing simple IF statements, through nested Ifs, ElseIfs and even how to combine logical tests using the And and Or operators.

Excel VBA Part 12 - With Statements

Posted by Andrew Gould on 30 January 2014

A With Statement in VBA is a great way to make your code a lot neater and a bit more efficient. This short video shows you how to use With statements in your code and explains why they can be so useful.

Excel VBA Part 11 - Input Boxes

Posted by Andrew Gould on 27 January 2014

In VBA an Input Box is a convenient way to ask users for information. This video shows you how to display and customise a basic inputbox, and how to capture the result using cells or variables. You'll also see what happens if a user cancels from the inputbox and how to test if that happens. The final part of the video goes into more technical detail on returning inputbox results to variables with different data types.

Excel VBA Part 10 - Message Boxes

Posted by Andrew Gould on 23 January 2014

Message boxes in VBA provide a convenient way to display information to a user. This video shows you all you need to know to get started with showing messages, customising the message box and using it to ask users for input.

Excel VBA Part 9 - Object Variables

Posted by Andrew Gould on 22 January 2014

Object variables in VBA allow you to store references to objects in memory. They're slightly more complex to use than basic data-type variables, but well worth the effort and this video explains why! You'll learn how to declare object variables and how to set references to existing objects. The video also shows you how to return references to objects using the methods of other objects with examples including generating new workbooks and worksheets, as well as using the Find method to reference cells.

Excel VBA Part 8 - Variables in VBA

Posted by Andrew Gould on 21 January 2014

Variables are essential in all sorts of programming languages, and VBA is no exception. This video takes you through the basics of working with simple data-type variables and will explain the difference between non-declared and explicitly-declared variables, including why explicitly-declared variables are worth the effort! You'll see how to choose the best data type for your variables as well as learn about the consequences if you get it wrong. Finally you'll find out what the 'scope' of a variable means and how to set the scope appropriately for the subroutine that you're writing.

This page has 0 threads Add post