Recursive Creativity LLC

RECURSIVE CREATIVITY LLC

Microsoft EXCEL

Content listed below are those items I have developed, use, or have permission to use...all related to Microsoft Excel.

PLEASE NOTE:
A lot of the links to add-ins below are dead links. They will be unavailable until I have time to rewrite them for compatibility with newer versions of Excel and the Ribbon.


Excel Add-ins

I am currently rewriting my add-ins to use the Ribbon where possible.


Top of Page


Power Query, Power BI, DAX


Power Query

File per link below is a sample Power Query template I use for projects and training. It is always evolving as my Power Query knowledge increases.

One benefit of this template is the sample UDFs (User Defined Functions) available. The template is designed to be portable. It can be copied or moved anywhere. If the files and folder structure are maintained, updating the master file will continue to work. One caveat is that the path to any given folder cannot exceed 255 characters. If path exceeds 255 characters, Power Query refresh will error out.

Examples exist for using Power Query to pull data from a single file and one folder. Numerous sample files for 2021 are provided and more can be easily created.

Directions:
  1. Download the .zip file
  2. Open .zip file
  3. Copy root folder, Power Query v2, to your Desktop, Documents folder, or other convenient path
  4. Peruse README files

Contents (files):
  1. Power Query Template v2.xlsb

    This is blank template used for training. It contains no reports \ Power Query and no PivotTable(s).

  2. Power Query Template v2 - Solution.xlsb

    This includes all reports added as part of VBA overview and Power Query training session.

  3. Power Query Template v2 - Solution2.xlsb

    This same as above "Solution" file with additional examples of dynamic array and CUBE functions.

  4. Power Query Template v2 - Functions.xlsb

    This is older version of the template with all Power Query items removed. It includes numerous samples of many new Excel functions starting with Excel 2016.

  5. New Excel Functions - after Excel 2010.xlsx

    This file contains list of all Excel Functions available after Excel 2010. It has been filtered to display those functions that have examples shown in the file, Power Query Template v2 - Functions.xlsb. The tab is listed where the examples can be found. I might have added more examples after the original file was created...so it might have changed.

Folders:
  • Date Stamp - Every time the Refresh button is clicked, a date stamped version of the master file is saved to this folder.
  • MAP - This contains files that maintain details related to Geography: Hemisphere, Region, Country, Sub-Country.
  • PDF - My PDF framework (VBA) is included but not used in this file. It gets used when I teach VBA portion of related classes.
  • Source Files - This folder contains all sample headcount files...in date stamped folders (YYYYMM). All files are duplicates. To create new folders, just copy existing folder and rename it to what year and month (YYYYMM) is most current. Then rename files in copied folder accordingly.
  • Values - My Values framework (VBA) has been modified for this file. It excludes code related to removal of HFM metadata (HFM and Essbase). The Values framework saves the file as .xlsx file type (removes VBA) and converts all equations to text. I need to add removal of any links to other files.
FYI:
  1. If you will notice on macros tab (hidden), I use a function to get path of PQ template file. This enables the main file and all related folders\files to be copied anywhere (except SharePoint) and still work. If you move to new job, your replacement can copy to their desktop and no code modification is necessary. I never copy such projects to network drive unless performance does not impede performance.
  2. Please note that wherever the root folder, or any Power Query (PQ) project, is copied to, the character limit for PQ is 255 characters. Thus, if the path of the master\template file or any source files is greater than 255 characters you will get error messages and the data will not refresh\update.
  3. As you will see from named ranges and VBA code, everything is dependent upon the folder structure and named files being in certain specific folders. And in the case of the MAP files, the date stamped file name is important.
  4. Some sheets are protected but no passwords exist.
  5. There are numerous files in MAP folder. The date stamp (e.g. 202111, 202112) on these files indicated selections for Year and Month in PQ template for which corresponding data files exist and queries can be run\updated. The two data sets are identical. The Source Files can be copied to other date stamped folders and the MAP file can be copied and renamed to create additional identical data sets if needed.


File per link below has all Power Query M functions and provides link to Microsoft source web page.

Last Updated: January 23, 2023 Top of Page



DAX

Files per links below provide all DAX functions. Links to Microsoft and DAX.guide web pages for each DAX function are in ech file. Links to all function categories are also provided.


File per below link provides comparison of Microsoft DAX functions and those listed on DAX.guide (sqlbi.com).

Last Updated: January 23, 2023 Top of Page



Functions Bundle

File per above link provides all files related to lists of Functions found on this web site. Most files contain links to the source of the keyboard shortcuts.

My reasoning for KEYBOARD SHORTCUT files is also why I created the files for functions. I like to keep track of my learning curve, take notes, and develop learning plans. I encourage everyone to at least read \ peruse the functions to know what is possible.

Last Updated: January 23, 2023 Top of Page


Excel Files

Many of the items below are Freeware tools I have developed to automate certain tasks.

I have given credit to authors whose code or files I have used or modified. I have left untouched much of the code I have not authored. Permission has been obtained for posting files that are modified versions from other authors.

If there are any objections to my posting any links or other content, please e-mail me with any related requests and, within reason, I will oblige.


Excel Functions, Keyboard Shortcuts

I have an old large file (many tabs) I used to use for training. However, due to adding more content on existing tabs and adding new tabs, I have decided to break the larger file down into smaller, more content specific, files.


Excel Functions

This is Power (web) Query of all Excel functions per Microsoft's web site. While all (most) my function and keyboard shortcut files have refresh button, content will be occasionally updated for new items and structural changes such as new categories.

Functions-Excel.xlsb

Above file is also included in Functions Bundle above.

Last Updated: January 23, 2023 Top of Page



Excel Keyboard Shortcuts

This is Power (web) Query of all Excel keyboard shortcuts per Microsoft's web site. While all (most) my function and keyboard shortcut files have refresh button, content will be occasionally updated for new items and structural changes such as new categories.

Microsoft Excel Keyboard Shortcuts

Above file is also included in Keyboard Shortcuts Bundle above.

All KEYBOARD SHORTCUTS.

Last Updated: January 23, 2023 Top of Page




Training File(s)

I have an old large file (many tabs) I used to use for training. However, due to adding more content on existing tabs and adding new tabs, I have decided to break the larger file down into smaller, more content specific, files.


Date, Time, Holidays, Events

Date,Time,Holidays,Events-Excel,PQ.xlsb

This is continuation of breaking down a larger file I used for training. This file has new Excel content and has been updated to include Power Query and some M language content. Please note, this barely scratches the surface for date or time content but does provide some Excel equivalent Power Query functions or M code.

I have done daily, weekly, monthly, and quarterly reporting so I have been interested in exploring date options in Power Query and hope to participate in future Maven Analytics challenges (and other) as my M, DAX and Power BI Desktop skills improve.

You will need to Enable Content upon opening file. Amongst the VBA included, I have incorporated most of the details from an Excel add-in I use in most places of employment for holidays and events.
If you start typing =HOLIDAYS( or =EVENTS(, upon entering \ you will get list of available choices for each.

The Birthdays tab can be used to see any event for any number of years. Its main purpose is to allow user to see dynamic table created real-time.

KUDOS to Craig Hatmaker, Beyond Excel for the code used for dynamic table and other tips and ideas I have gotten from his examples.

Last Updated: August 16, 2021 Top of Page



HYPERLINK

I cannot make one file that does the HYPERLINK function justice.



I originally got the HYPERLINK mouse over idea from Chandoo.org. I made numerous changes including adding a tab with examples using HYPERLINK for dashboards and targeting tables. If you do not use tables yet, you should take the time to learn how to use them.


HYPERLINK-MDN.xlsb

This is my enhanced version of the original modified version.


Smart View VBA Functions

This was also posted in Hyperion Group on LinkedIn. However, this file has been updated with most recent Oracle content. It is a different use of the HYPERLINK with mouse over event (single).

The first time you click Update button, it takes a little longer. It adds Total line at bottom and spreads the ALL functions across respective products. By default, if you originally filtered on a given product, the ALL functions were not included. I rectified that.

The filters are applied using the mouse over event on row one of each column and then clicking on the Filter button. Columns can be unfiltered by clicking on the Update button, which preserves the filter last chosen, or by using mouse over on first or last columns and pressing the Filter button.

The Favorites table allows you to hand pick products that are your favorites or specific to your company. Again, it would be helpful to know or learn tables.


Hyperlink-DIMO.xlsb

This another use of the HYPERLINK mouse over event. DIMO = Double Impact Mouse Over event.

Moving the mouse over outer columns of the first table selects the Region and highlights Countries in second table applicable to that Region. Moving the mouse over the respective columns in the second table, first selects the Country but also selects the Region applicable to that Country. The other Countries specific to that Region are also highlighted.

The two tables used for filtering or mouse over events are Power Queries that automatically update when using Refresh All. This example is not tied to any data currently. It is tab from Power Query template I developed for training.

The button is a Toggle button. It always shows the next action to be performed. It will rotate between options: Filter Country, Filter Region and Unfilter.

And yes, Slicers could be used in place of given solution. Slicers would also provide more than two options.

Last Updated: August 16, 2021 Top of Page





Excel Ribbon: Toggle Button

FYI, I work with Hyperion Financial Management, hence the reference to POV (Point of View) in example.

As mentioned above, I have been trying to find time to update all my add-ins to utilize the Ribbon where applicable. In doing so, I figured out way to use images (Ribbon compliant) embedded in Excel to update a toggle button in the Ribbon.

Code in example uses logged in user's temporary folder. However, when I moved to Add-in, code was changed to use ThisWorkbook.Path. In short, the images are inserted or copied to Chart object and the Chart object is exported to .png file. It also works using .bmp (again Ribbon compliant; I used 32x32) images created in Excel. File using link below has two examples on far right of Home tab.

Thanks to Ron de Bruin, Excel Automation, whose original Toggle Button 3.xlsm file I used to modify and test, and Mark Proctor, Excel Off the Grid, whose code I used (much cleaner than other code I found) for creating the images used to update the Ribbon.

Last Updated: August 16, 2021 Top of Page




Last Modified By: Excel Templates

I do a lot of Excel templates (Budgets, Forecasts, Related Parties, Lease Expenses, Legal Fees, Pensions, etc.) that I consolidate using Power Query.

As much as I can scrub them using Power Query and apply a learning curve to improve them each cycle, there are often items that need to be discussed. For example, spelling, are amounts in USD, or why are they beyond normal thresholds.

Despite maintaining distribution or contact lists, the actual data input is often not done by the person on these lists.

The file available at link below allows you to point to folder and get list of files. For each file, the date last modified, size of file and last author (Last Modified By) is obtained.

Getting the Last Modified By (last author) has significantly increased timely feedback on data being consolidated and reported.

Options exist to also obtain the same information from Word or PowerPoint files.

Last Updated: August 16, 2021 Top of Page




MP3 Manager - Windows Vista (currently testing with Windows 10)

This file was developed to provide details about .MP3 files and edit the metadata or ID3 Tag for the files. While the tool will currently list .WMA and .M4* (iTunes) file types, it will not edit the Metadata for them.

See Read Me.txt link below for more details and basic directions to edit metadata.

Read Me.txt  Last Updated: APR 23, 2012

There are two types of data associated with .MP3 (audio) files. The underlying file properties are referred to as metadata. The last 128 bytes of an .MP3 file is the ID3 Tag. The first part of the ID3 tag begins with TAG. The remainder of the tag contains Artist, Album, Song Title, Genre and Track Number (not an all-inclusive list).

The following features are built into the MP3 Manager:

  • Get list of all .MP3, .WMA and .M4* (iTunes) files in a directory or folder.
  • Option to NOT recurse directories or search subfolders. Default is to search all subfolders.
  • List of files can be placed on default sheet, or each folder can be placed on a separate sheet. Issue - duplicate folder names; no two sheets can have the same name. Duplicate sheets will be named Sheet#, whereby # is the next number starting at 1.
  • All data on active sheet can be deleted. If column headers get deleted, they will be recreated each time you "Get MP3 Files".
  • All sheets, except default sheet, can be deleted. Your mulligan!
  • Metadata for a single file can be edited.
  • Metadata for all files can be edited.
  • ID3 tags for a single file can be edited.
  • I am debating adding the functionality of editing the ID3 tags for all files. It would write the metadata for each file to the ID3 tag.
  • Files can be renamed using the metadata.
Renaming files using the metadata only allows the following fields to be used:
  • Author
  • Album
  • Song Title
  • Track Number

Functionality also exists for selecting the order of each of the above. The track number was added because I have been told that is how iPads sort files? I do not have an iPad so I could not test it.


My to do list regarding future enhancements and features:
  • Figure out the technical specifications for the file type .WMA and, if possible, incorporate them into program to allow editing .WMA metadata.
  • Figure out the technical specifications for the file type .M4* (iTunes) and, if possible, incorporate them into program to allow editing .M4* metadata.
  • Determine logic and subsequent code to enable option to update all ID3 tags using the metadata.

Top of Page




Cube Metadata Query Tool

This file was developed to provide a mechanism for querying SQL Server Analysis Services (SSAS) Cubes (also known as Unified Dimensional Models, UDM) and provide examples on how to use the =Cube() functions in Excel 2007\2010.

MDXL - Cube Query Tool - 2007\2010     Excel 2007\2010  Last Updated: APR 14, 2012
 
MDXL - Cube Query Tool - 2007, No Examples     Excel 2007\2010; This file does not contain =CUBE() examples.  Last Updated: APR 14, 2012
 
MDXL - Cube Query Tool - 2003, No Examples     Excel 2003, This file does not contain =CUBE() examples.  Last Updated: APR 14, 2012

The "Examples" provide a guide to using the =CUBE() functions and require access to an Adventure Works DW 2008 cube. Otherwise, equations will display errors.

The following metadata queries are available that provide data for components of the =CUBE() function equations listed below:

  • Dimensions
  • Measures
  • Measure Groups
  • Measure Group Dimensions
  • KPI's
For a given Dimension, queries provide metadata for:
  • Hierarchies
  • Specific Members: list of Members for selected Dimension
  • Dimension Metadata: all metadata for selected Dimension
Other Metadata queries:
  • Members: list of all Members for all Dimensions     This option not available for Excel 2003.
  • Metadata: all Metadata from a list of thirty different Metadata Tables

The Excel 2007, 2010 version of the file contains sheets with examples for each of the following =CUBE() functions:

  • CUBEKPIMEMBER
  • CUBEMEMBER
  • CUBEMEMBERPROPERTY
  • CUBERANKEDMEMBER
  • CUBESET
  • CUBESETCOUNT
  • CUBEVALUE

However, for the equations on each Example sheet to appear as values and not as errors, at least Read Access must be available for the Adventure Works DW 2008 cube. You must also create your own Connection to it from Excel.


Some other Adventure Works DW 2008 files and information are posted for a presentation I gave at the Houston SQL Server User Group.

Top of Page




Zipped Backup Lite © version 3.0x.bat

WinZip provides a command-line utility that allows you to WinZip a file, folder, or drive. I programmed, using Excel, a way to preset up to twenty-eight different paths on any drive and utilize WinZip and the WinZip command-line utility.


The difference between the stand-alone version and the version that accompanies a Batch file is the name of the Command Prompt window.

I wrote the batch file because I did not know any other way of accomplishing what it does...it names the Command Prompt window! The name of the Command Prompt window, when open and running a backup procedure, tells you where you are in the processes if you have numerous buttons named.

Prior to incorporating the batch into the process, I would never know which process was running. When minimized and a process is running (Path is getting WinZipped up), the Command Prompt window in the Taskbar would tell me how far along the processes were. More specifically, which process was running.

The Batch file must reside in the same path as the Zipped Backup Lite Excel file.

The second zipped file does not rely on the batch file and the Command Prompt window is NOT named according to the button/process running.

The cost of external hard drives makes it more cost effective to just buy one and use a Synchronization program to duplicate or backup your data files. Microsoft provides an excellent, and FREE, tool for synchronization, SyncToy.


I now use 7zip or Windows File Compression and I have never updated the VBA code for these files to use laternative options.


With .BAT File, Not Compressed
 
Zipped Backup Lite v3.07.bat.xlsm
Zipped Backup Lite v3.03.bat.xls
 
ZippedBackupLite.bat
(The batch file must reside in the same path/folder as the Excel file.)
 
Stand Alone, Not Compressed
 
Zipped Backup Lite v3.07.xlsm
Zipped Backup Lite v3.03.xls
 
.BAT Package, Compressed
 
Zipped Backup Lite v3.07.bat.zip
Zipped Backup Lite v3.03.bat.zip
 
ZippedBackupLite.zip
(The batch file must reside in the same path/folder as the Excel file.)
 
Stand Alone, Compressed
 
Zipped Backup Lite v3.07.zip
Zipped Backup Lite v3.03.zip

Top of Page




Network Info

This application returns the logged-on Username, Server, Domain, list of all Network Groups the logged-on Username belongs to and a list of all IP Addresses currently in use by PC. I have used the Network Groups (Active Directory) for security/access in various applications/spreadsheets.

Thanks to Rob Bovey, Application Professionals for permission to use his ideas and code. Rob's books are highly recommended:

Top of Page