VBA – Display a File Open Dialog Box For the User to Select a File – Excel functions

Below is a demonstration of using Application.FileDialog to give the user an option to select a file. The code is well commented and should be self explanatory.

The full file path will be stored in fullpath variable, which can be used later in the code.

An example using the code to prompt the user to select an Excel file an open it in Excel can be found here http://www.chicagocomputerclasses.com/excel-vba-display-a-file-open-dialog-and-open-the-file-excel-functions/

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

Excel VBA Function to SUM Unique Numbers Only – Unique SUMIF Function

First add this code to your VBA Function

 

 

Then use it in your worksheet like this

A B C D E
1 CA 11 CA =SUMIFUNIQUENUMS($B$1:$B$7,$A$1:$A$7,D1)
2 NY 14 NY 29
3 NY 15 IN 0
4 CA 11
5 CA 11
6 CA 14
7 CA 11
Sheet1

 

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Check Regular & Overtime Hours – Excel functions

Below are 2 Excel VBA UDFs to check and divide regular and overtime hours based on 40 hour week, Monday through Friday Payroll.

Date Hours Worked Regular Overtime
12/15/15 14 14 0
12/16/15 9 9 0
12/17/15 11 11 0
12/18/15 9 6 3
12/19/15 7 0 7
12/20/15 8 0 8
12/21/15 5 5 0
12/22/15 2 2 0
12/23/15 1 1 0
12/24/15 1 1 0
12/25/15 6 6 0
12/26/15 7 7 0
12/27/15 14 14 0
12/28/15 9 9 0
12/29/15 9 9 0
12/30/15 9 9 0
12/31/15 9 9 0
1/1/16 8 4 4
1/2/16 9 0 9
1/3/16 13 0 13
1/4/16 15 15 0
1/5/16 10 10 0

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Unique List of Data That Appears At Least Once in All Columns – Excel Macro

Use this VBA code to extract a unique list of items that appear inside of multi-column data. The criteria is that the data needs to appear at least once in each column. This one has user friendly interface to run on any size data and get the results wherever user picks in the worksheet.

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Loop Through All Textboxes in a Form – Excel Macro

This VBA code will loop though all textboxes in your userform that are following the naming convention textBox[x] (ex. textBox1, textBox2, textBox3 ….. textBox255)

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Loop Through All (.txt) Files in a User Specified Directory – Excel Macro

This VBA code will loop though all the text files in a directory that user picks from an application box and print a list of them. This code can be modified to be used in many scenarios for any file type.

 

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Get Information From a Web Page – Excel Macro – Amazon Example

This is a basic VBA code to get started with InternetExplorer.Application object and work with web browsers and data extraction. This code is written strictly for learning proposes and should be used as such. Everything is well commenced out so it should be really easy to understand. In this example we use Internet Explorer to open a product page on Amazon and we extract the page title & product price and add the data to our spreadsheet.

 

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Get All Numbers in a Text String & Incresae by One – Excel Macro

The following procedure will take any numbers within text and increase them by 1.

For Example:
For20Example51Text will transform into For21Example52Text

 

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Get First Number & Then Text & Following Number – Excel Macro

This Excel Macro will transform the text data placed in column A and Break it down into 3 Columns.

Data Format Required:
15654 Text goes Here 16 2 6 1595
1 Different Text 1 6
number (space) text of any lenght (space) number (space) other characters

Output will return:

  1. First column: full first number
  2. Second column: full text string in the middle of surrounded numbers
  3. Third Column: first full number after text

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Delete Every Other Row – Odd or Even – Excel Macro

This Excel Macro will go through all the cells in column A, find the last cell that has data in it, and then delete every other row in that data range.

To Delete Odd Rows

To Delete Even Rows

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/