Be an Excel “MacGyver”

Be an Excel “MacGyver”
Featured

Be an Excel “MacGyver”

Download Brochure Register Check Scheduled Training

 

Overview

Have you frustrated on some tasks in Excel (e.g.: unhide sheets one by one)? Do you want create handy tools to speed up your works in Excel? Have you watch a TV series named “MacGyver”, whom is a smart guy that create simple tools with common ingredients?

Yes! You can be an Excel “MacGyver” now.

This course is designed especially for someone like you. At the end of this course, you will take away more than 10 handy tools created by yourself. In no time, you are an Excel MacGyver among your friends and colleagues.

When you back to workplace, you can immediately enjoy the harvest and showcase your handy tools. With these skills and knowledge you learned, you can continue to improve these tools. You are invited to join our next episode of Excel MacGyver course to gain more ideas and knowledge.

Who Must Attend
Regular Excel users that want to create handy tools to speed up their tasks. These handy tools are useful and helpful to others too.

OUTCOME

At the end of this course you will be able to:

  • Getting familiar with VBA
  • Getting familiar with User Form
  • Manage and share Macro

PREREQUISITES

To ensure your success, we recommend the delegates equip with the following or equivalent knowledge:

  • A working knowledge of Microsoft Excel
  • A working knowledge of Microsoft Windows
  • A working knowledge of computer, keyboard and mouse
  • A basic understanding on programming is a plus

Please Note: If you attend a course and do not meet the prerequisites you may be asked to switch to appropriate course or to excuse yourself.

DURATION

2 Days (9 am - 5 pm)

METHODOLOGY

Face-to-face learning with demonstrations and practical hands-on.

Training Outline

Day 1

1. Setting the Stage

  • Showing Developer Ribbon
  • Macro Security
  • Save File as Macro-Enabled Workbook
  • Saving Macro-Enabled Workbook

 

2. Visual Basic for Application (VBA)

  • Working in Basic Editor (VBE)
  • General Structure (Syntax) of VBA
  • Adding Comment
  • Creating Sub Procedure

 

3. Running the Macro

  • via Macro Menu
  • via Shortcut Key
  • via Quick Access Toolbar
  • via Shape/ Image
  • via Button

 

4. Running the Macro in Slow Motion (Debugging)

  • Stepping Through
  • Breakpoint
  • Immediate Window

5. Handy Tools for Worksheet

  • Work Area Size (With one click, you can enlarge your working area in Excel without turning them (ribbon, status bar, formula bar) off one by one.)
  • Show Columns (One click to unhide all hidden columns, click again will set all those columns back to invisible.)
  • Show Rows (One click to unhide all hidden rows, click again will make these rows back to invisible.)
  • Show Worksheets (One click show all hidden sheets, another click again make those sheets back to invisible.)
  • Unhide All Worksheets (One click to unhide all hidden sheets in current workbook. Normally it only can be done by unhide them one by one.)
  • Set Scroll Area (One click to hide all rows and columns outside of the scroll area range you specify. This reduces the unnecessary scrolling.)
  • Create List of Sheet Names (When working with workbook with many worksheets, it is more productive when we can quickly navigate to a specific worksheet. This tool quickly create a list of all worksheet names with hyperlink. Click on the hyperlink brings you instantly go to specific worksheet.)

 

Day 2

6. Handy Tools for Ranges/ Cells

  • Merge Same Cells (With one click, merge adjacent cells of selected range that contain the same value.)
  • Unmerge Cell (One click to unmerge merged cells in a range, and fill each unmerge cell with the content from the original merged cell.)
  • Fill Blank Cells (Quickly fill blank cells of selected range with one click. You can decide to fill the bank cells with, 1, values above, below, left or right; 2. Fixed values, such as 0.)

 

7. Handy Tools for Workbook

  • Open Containing Folder (With one click, you can quickly open the folder which the current workbook is saved in. Usually it takes a while with file browser.)
  • Hide Other Windows (Your focus could be better when you can hide other opened workbooks. One click to hide all workbooks except the one you are working on.)
  • Unhide All Windows (One click to unhide all hidden workbooks. Normally it can be done by unhide each hidden workbook one by one.)

 

8. Handy Tools for Security

  • Encrypt Cells (You can encrypt selected cells with password you specified, excluding formula cells.)
  • Decrypt Cells (You can decrypt selected cells with password you specified. This only works with the tools you created.)

 

9. Manage and Sharing Macro

  • Transfer Macro Between Files
  • Transfer Macro Between Computers
  • Protect Macro with Password

 

Hardware & Software
  • A laptop is required for this course (PCs not provided)
  • Excel 2007 or later is installed

Interested to Register?

Let's have one of our friendly consultants to guide you through the registration process.

Looking for Something Unique or In-house Training?

Let's have one of our friendly consultants talk to you...

Skip to toolbar