What do you find under my projects section?
I add to this section from time to time various Excel files to illustrate the type of work that I do using Excel as part of an integrated Microsoft development platform for custom spreadsheet and tool design and process automation.  The content here will be growing over time with relevant examples for VBA macros, Excel functions and similar items illustrating processes that needs to be tackled with during a typical Excel based reporting project. Give it a try by clicking on the Sample Excel macros and Misc Files links located on the left navigation bar or click on below images.

This page provides sample Excel macros, this is a growing list.  I place macros that in my opinion makes the life of the person who has to work with Excel and need to find some code solutions to make more useful and powerful spreadsheet applications.

learn more

On this page I simply added some personal stuff where I used Excel to support my hobbies or interests while having a little fun with Excel.

learn more

EXCEL VBA/MACRO SAMPLE FILES

On this page one can find randomly selected Excel macros to automate spreadsheet tasks, file operations, calculation logic and data flow control etc.  I pick these files from my work projects, tasks and assignments where I had to use them when I was wrestling with challenging data problems.  I place either directly from my work files as a scaled down version to illustrate the function or formula or place here a re-created version of the topic to explain the concept on a working file. If the sample file is created originally by another site, person or company on title section this situation is clearly indicated and referenced.

XLOOKUP Function
Samples provided on each tab showing different ways of using the new XLOOKUP function. This function became available during the summer of 2019 and provides a whole lot more versatility than the VLOOKUP.

learn more

Active Cell Coloring
Macro changes the active cell color to yellow and when left it reverts back to its original color.



learn more

Discount Function
User defined discount functions applied as a regular built-in spreadsheet function to calculate the discounted amount.



learn more

TVM - Time Value of Money Calcs
Some basic financial calculations are provided on this example.

learn more

INDEX MATCH Example
INDEX MATCH is a lot more powerful function than VLOOKUP.

learn more

Excel Tips and Tricks
Many practical and useful Excel tips and tricks can be found on this document.

learn more

Absolute to Relative Value Conversion - From OzGrid
This macro was created by OzGrid for business applications and could be highly useful on a huge page of data that needs to be converted between absolute to relative addresses depending on the types of operations that need to be applied on the same range of data.

learn more

Array Formula Example
Array functions are entered as [Ctrl+Shift+Enter] key combination into a cell not only by hitting the Enter key alone. Their power is looking into a range defined in the formula for the given criteria to perform the desired operation.

learn more

Display Respective Values
VBA on this file looks at the values in the range and show their corresponding values on the target or destination tab.

learn more

Indirect Function
Change the value in the yellow cell and see how the column values respond accordingly with the use of INDIRECT function.

learn more

Extract and Display File Names
Macro on this file gets and displays he file names in the selected drive and folder.

learn more

Dynamic Range Pivot Table Update
Macro updates the pivot table range for dynamically changing ranges.

learn more

Totals by Cell Color
VBA to calculate totals by cell color.

learn more

Refresh Multiple Pivots
VBA code on this file refreshes multiple pivot tales with dynamically changing data ranges.

learn more

Compare Two Ranges for Changes
VBA in this file compares two ranges and if they are not identical creates another file indicating the cell in red where change occurred.

learn more

Email File Sender
VBA on this file emails the named files on the list from their original location to their corresponding email addresses on the list.

learn more

Create Access Table from within Excel
Click on the button on the tab and it will create an Access file and append the data on the tab to a table in this Access file.

learn more

Extract Year from Text
On this file GetYear function which is a user defined function acting like another built-in Excel function extracts the year from a text in a selected cell where ever that year is mentioned within the text. Using string functions in this case don't help unless you know where that year is located in the cell.

learn more

Combo Box Auto Feed for Selections
VBA code in this file feeds the associated items form the raw data set per the selection in the first column. On the properties of the Combo Box object, the linked range properties is left blank and it is populated dynamically after the selection is made in the first column. To make sure that code works after opening up the file make a selection in the first column in any of those boxes then check the items that get displayed on the associated drop down box positioned next to that selection. On file open when you get a prompt click yes to continue.

learn more

Delete Rows Per Citeria
VBA in this file deletes all the rows designated as Delete. This is done without manually filtering for the criteria and carefully deleting only those rows from the range and gives flexibility if this operation is to be done repeatedly after each load of new data set. 2nd tab is the original data set. On file open when you get a prompt click yes on it.

learn more

To create a personal ribbon with only the desired menu items available.

learn more

Excel XLOOKUP Example
This file has various working examples to illustrate how to use XLOOKUP function instead of VLOOKUP which offers a whole lot more power and versatility for spreadsheet based analysis.

learn more

2010 World Cup Soccer Fantasy
This file was tracking betting during the World Cup at 2010 at work amongst four people.

learn more

Euro 2012 Soccer Championship
This file was created to track the European soccer championship matches.

learn more

World Cup 2018 Russia
This file has the details of this world cup. When you get a prompt on opening the file click yes.

learn more

Netflix Viewing Tracking





learn more

www.rayerden.com