105 Excel VBA Functions Explained

by mor | Sep 16, 2021

105 Excel VBA Functions Explained

Current Status

Not Enrolled

Price

37

Get Started

Take this Course

Course Content

Introduction

About the course, who is it for, what to expect

Course structure and contents Sample Lesson

Arrays Functions

Section Intro: what is an array?

Check if array is dimentioned Sample Lesson

Match a value for its index along a 2-D array column or row

Match a value for its index along a 1-D array column or row

Transpose a 2-D array

Sum numeric values in a column of a 2-D array

Application Control Functions

Section Intro: controlling the application

Check if your program is in Production mode

Restore the State of the application to normal

Data Export/Import Functions

Section Intro: text files

Export an Excel Table as a csv file

Export a Worksheet as a csv file

Export an array as a csv file

Import a csv file into an Excel Table

Import a csv file into a Worksheet

System Input/Output

Section intro: Input/Output

Extract the file name out of a full path

Convert a file name to a Windows legal file name

Delete old files in a folder

Get the storage drives list from Windows

Check if a file exists

Notification

Section Intro: notification

Present a message to the user in the application Status Bar

Clear the application Status Bar

Printing

Section Intro: printing

Check if the printer is turned on and ready

Print out a Worksheet

Sets rows as repeating rows in PageSetup for printing

Protection

Section Intro: protection

Protect a Workbook

Unprotect the Workbook

Protect a single Worksheet

Unprotect a single Worksheet

Protect all Worksheets in a Workbook

Unprotect all Worksheets in a Workbook

Reports

Section Intro: reports

Formats a column of cells

Formats a column of cells in an Excel Table

Apply (or remove) WrapText to a range of cells

Draw borders around a range: sides, color, weight

Apply formatting to a row of a report

Appliy fill and/or font color to a column(s) of cells

Apply fill and/or font color to a column(s) of cells as a conditional formatting formula

Clears content and formatting from all cells of a report

Search & Navigation

Section Intro: search & navigation

Find a text string in a Worksheet

Get the column letter by its index

Activate the startup Worksheet specified by the user

Worksheets & Workbooks

Section Intro: Worksheets & Workbooks

Check if a Worksheet-scope range name is defined

Hide all Worksheets flagged with a Worksheet-scope range name

Hide a single Worksheet

Unhide a single Worksheet

Check if a Worksheet is currently hidden Sample Lesson

Check if a Worksheet exists and visible

Copy a Worksheet to a newly created Workbook

Open an Excel file if not already open

Check if a Workbook is currently open

Create a new Worksheet

Close a Workbook

Windows System

Section Intro: Windows system

Get the logged-in Windows user name

Get the computer name running Excel

Get the local PC country code

Get the full path to the Windows temporary folder

Pause execution of the program

Excel Tables

Section Intro: Excel Tables Sample Lesson

Add a new row to a table

Delete row of current cell within a table

Add a new row at the end of the table with values

Fills a row of Fields in a Worksheet with values

Delete a complete Worksheet row by its index

Delete a row from a Worksheet matched to a Value in a Column

Remove last row of a table

Update column value of rows matching a column value

Get the number of data rows in a table

Get the number of columns in a table

Get the Worksheet row of record matched to a key

Get the Worksheet row of a newly added row

Get the Worksheet row of table record matched to a key

Get the Worksheet column in which a column header is found in a table

Delete table rows except first rows

Clear a table’s non-formulas cells and delete rows

Get a table cell value matched by row index and column name

Vlookup implementation on a table

Vlookup with cell update on a table

Load all rows of a table column into an array

Find & Replace in cells of a table column

Apply an array to a table

Get the table index row of the active Worksheet cell

Reset all filters of a table

User Interaction

Section Intro: user interaction Sample Lesson

Pop up a login dialog box to the user

Pop up a message box with a single “Close” button and a warning icon

Pop up a dialog requesting some input from the user

Pop up a message box with a “Yes” and “No” buttons

Pop up a message box with a single “Close” button and a confirmation icon

Control user input length in a text control on a user form

Get Worksheet name of active cell

Get value of active cell

Get Worksheet row of active cell

Worksheet column index of active cell

Get the value of intersection of current active cell row and a Worksheet column

Get the value of intersection of current active cell column and a Worksheet row

Expand Excel data area to full screen Sample Lesson

Restore Excel data area to regular size

Show or hide Worksheet rows and columns headings

Close the Excel application or just the active Workbook