Module Name
Description
ProjectMacroCode_20170111.zip

Formatter for MS Project Export (WBS) - Updated

This is an updated version of prior code. With this, I have improved on several aspects:
Note, as always, use at your own risk, and make sure you review the code before using it.
ProjectMacroCode.xlsm

Formatter for MS Project Export (WBS)

Although I am just starting to work more in MS Project, hence will likely develop code to enhance my productivity in a PM role, this is the first attempt.
Note, as always, use at your own risk, and make sure you review the code before using it.
FixBadColumnNames.sql

SQL for Bad Column Names

As part of a project, I imported a user's worksheets into SQL Server, before realizing that the column names were terrible, and very non-standard. This scripts generates the SQL to correct 3 column name errors, using a space, a hyphen, and a tilde.
Gantt Charting Template.xlsx

Gantt Charting Template

A simple template for Gantt charting, with two examples:
OfficeJavascriptLibrary.png

JavaScript API for Office (PNG)

The JavaScript API for Office includes objects, methods, properties, events, and enumerations that you can use in your apps for Office code.

The Microsoft.Office.WebExtension namespace (which by default is referenced using the alias Office in code) contains objects you can use to write script that interacts with content in Office documents, worksheets, presentations, mail items, and projects from your apps for Office.
SecondaryAxisAlignment_VB.txt

Align Secondary Axis (VB.NET)

The general algorithm used for aligning a chart's secondary axis with the primary axis:
  1. Get primary divisors
  2. Get upper and lower bounds of secondary
  3. Get larger ABS(max) or ABS(min)
  4. Multiply (max/min) divisor by numbers (1,2,5,10,20,25,30) to find the first multiplier larger than current
  5. Apply multiplier to major unit
  6. Apply min to min (min divisors x minor unit)
  7. Apply max to max (max divisors x major unit)
SecondaryAxisAlignment_C#.txt

Align Secondary Axis (C#)

The general algorithm used for aligning a chart's secondary axis with the primary axis:
  1. Get primary divisors
  2. Get upper and lower bounds of secondary
  3. Get larger ABS(max) or ABS(min)
  4. Multiply (max/min) divisor by numbers (1,2,5,10,20,25,30) to find the first multiplier larger than current
  5. Apply multiplier to major unit
  6. Apply min to min (min divisors x minor unit)
  7. Apply max to max (max divisors x major unit)
SecondaryAxisAlignment_VBA.txt

Align Secondary Axis (VBA)

The general algorithm used for aligning the secondary axis with the primary axis:
  1. Get primary divisors
  2. Get upper and lower bounds of secondary
  3. Get larger ABS(max) or ABS(min)
  4. Multiply (max/min) divisor by numbers (1,2,5,10,20,25,30) to find the first multiplier larger than current
  5. Apply multiplier to major unit
  6. Apply min to min (min divisors x minor unit)
  7. Apply max to max (max divisors x major unit)
VBA_AdobeJavaScriptAutomation.txt

Adobe JavaScript Automation in VBA

Example VBA code for working with Adobe Acrobat in VBA, with some of the following features:

SQLExecution_20140522-01.zip

SQL Execution Add-In for Excel (C# + Facade for VBA)

A C# library that simplifies some aspects of connecting to Db's and running SQL statements in a threaded manner. The C# code can also be exposed to Excel VBA, enabling it to simultaneously execute numerous SQL statements, something that cannot normally be done in VBA.
VSTO.cs

Workbook Close Event Capture

For anyone that has had to capture the WorkbookBeforeClose event and struggled with the imperfectness of the process, this is C# code that fires only if the workbook is completely closed.

Kudos to the developer; Wordpress entry is here.
CreateNamedRange.txt

Create Named Range (Excel / C#)

When creating pivot tables and charts, Excel's default is to base it on a fixed range, not on one that expands. This code generates a named range that is dynamically expanding in both the row and column dimensions, using the Offset function, useful in pivot tables and charts where the data will be updated and will likely expand beyond the original size.
System_globals.txt

Excel Constants for Access

A module of global constants as long values with descriptive name for use in late-binding Excel, either from Access, or internally.
CustomXmlHandler_20131024-01.zip

Working with CustomXmlPart (C#)

This C# code shows the basics of working with CustomXmlParts of the Office 2007 and greater environment. It is a way to store complex information within workbooks. In this example, I create a type, and the create XML to stores the type, and then either set, or retrieve the XML.
CustomXml.txt

Working with CustomXmlPart (VBA)

This code shows the basics of working with CustomXmlPart of the Office 2007 and greater environment. It is a way to store complex information within workbooks. In this example, I create a type, and the create XML to stores the type, and then either set, or retrieve the XML.
ControlLimits.xla.zip

XLA for Operations Management Statistical Functions

An XLA that adds additional Operations Management statistical functions to Excel: Built several years ago while I was an MBA student, it might lack some controls, i.e., validation.
CPortfolioValues.zip

Worksheet Wrapper Classes

This is an idea I developed to avoid using arrays while refactoring spaghetti code. The user had used numerous arrays across many sheets with many columns, and for some refactored features I decided to create a parameter class for the rows of select sheets, and then a class that would hold a collection of parameter classes, providing type ahead and methods to get values, as well as provide basic properties like Count and Item.

Note: Requires an additional helper function contained in System_CommonArrayFunctions
System_CommonArrayFunctions.txt

Array and Collection functions (VBA)

The collections functions used worksheets like object collections, providing persistence. Some similar code used classes to wrap worksheet information to provide type-ahead, instead of using arrays via numbers.

Collection Functions Array Functions
PowerRelatedCode.txt

Power Code - Handle Sleep & Suspension

Excel C# code to reload an Excel add-in, although this should work for any office product, upon waking from suspend on Windows 7
SendImageAsEmail.txt

Send Excel Range as Image (VBA)

VBA code to send a selected Excel range as an image via Outlook email
ExcelVersion_2010Update.txt

Excel Version Check (VBA)

VBA code for returning the Excel application version, updated to include Excel 2010
CompetencyMatrix.xlsx

Competency Matrix for VBA

A developer, Sijin Joseph, reachable @ http://www.indiangeek.net/, has an online Programmer Competency Matrix, which I have taken and converted to Excel.
Excel_VBA_GetSource.txt

Get Pivot Chart Series Source

A sample VBA method that extracts and selects the source data range pivot for pivot charts.
COMReusable_20101111-01.zip

C# Add-in for VBA via COM

A C# project that can be used to expose methods to VBA via COM automation:

The project includes multiple methods:

C-Sharp form, printing and formatting options C-Sharp OLAP connection-related methods C-Sharp refresh pivot methods The project implements one form for printing, but otherwise has no UI. Included is a text file showing how to access and execute the methods via VBA.

This is an update C# add-in that exposes static methods using Excel-dna.
VBA_CopyAndRepoint.txt

Copy Pivot and Charts, Modify Source (VBA)

In Excel 2007, it is not directly possible to change the data source for a pivot chart, but this code that shows how to copy a worksheet while repointing the pivot charts to the copied pivot table, rather than the having them reference the source pivot table.
VBA_ArraySummation_Examples.zip

Summation Examples (VBA)

I was asked by a student to assist writing some VBA code. The assignment, as per the student: There are 2 ways to solve this, the usual iterative way and a mathematical away. The mathematical way is smarter.
In the first: The second is more like an equation: Included in the ZIP file is both the code as text file, as well as an Excel 2007 file that uses the functions in equations accessible to the user.
Excel_COM_MemoryCleanup_C#.txt

VSTO (C#) COM Memory Cleanup

Two examples (one works on the workbook's pivot caches, the other is on a single instance of a pivot) of working through Excel and cleanup
VBA_Evaluation.xlsx

Technical Evaluation Matrix for VBA

An Excel 2007 worksheet to facilitate in technical interviews of Excel/Acces VBA candidates.
DesignPatterns.xlsx

Design Pattern List (Updated)

An updated list of design patterns covering ideas laid out by the GoF, Code Complete, Pattern-Oriented Software Architecture, and Patterns of Enterprise Application Architecture.
NETNamingConventions.xlsx

Naming Conventions for .NET

Naming conventions for the .NET platform, a cleaner version of a previous file.
C#_CellsetToPivotTable.txt

CellSet, DataTable, Recordset Conversion (C#)

This C# code that generates a cellset from MDX, transforms it into a datatable, tranforms that into an ADODB recordset, and then creates a pivotcache/pivottable from the recordset. It gets around the inability of Excel 2003/2007 to create pivot tables directly from MDX.
MDocumentProperties.txt

Working with Document Properties (VBA)

Code module for working with document properties. Original code is from Pearson's website, with some modifications by myself.
MServerInterface.txt

Generic SQL Database Interface (VBA)

Basic code for executing stored procedures, or SQL, via a connection object with VBA.
C#_HyperlinkAdd.txt

Inserting Hyperlinks (C#)

Code for inserting hyperlinks. This was initially designed to work from a ribbon, which finds the selected cell(s) and passes that to the methods to insert hyperlinks.
VBA_MoveFile.txt

Rename File System Objects (VBA)

Uses file system objects to rename files by moving them.
Excel_DeleteBadRanges.txt

Delete Bad Ranges (Excel VBA)

Iterates through the active workbook and deletes all invalid named ranges, e.g. #REF.
CFilesToModify.txt

Change Pivot Table Connections (VBA)

An excel class that processes a collection of Excel files to change pivot table connection strings, as well as other parameters.
ActiveDirectorySamples.zip

Access VBA Modules from forms, that show how to retrieve domain-related information. These are provided as examples, since you will need to decouple the code from the underlying tables and related forms to use them.
VBA_CubeMetaData_Iteration.txt

VBA for iteration an OLAP cube's metadata and values.
CLog.txt

A simple class to do logging
CDBIRSData.txt

Example of a class module used to interface between Access or Excel application and SQL Server.
ZipCollection.cls.txt

Access class module, although should work in Excel with minor modification, that automates zipping files, with parameters for the following:

Properties Methods
Access_Notes_Send.txt

A function that sends (no prompt) a Notes e-mail, based on passed values for subject, recipient, body, and attachment.
ChangeLinkedDB_Excel.txt

Changes the DB referenced in data refreshes, provided that all the linked queries are the same DB.
ASCII.xls

Listing of ASCII character representation by number, as well as appearance if formatted as W*Dings in Excel.
MScreenResolution.txt

A very simple module to return the screen resolution of the current desktop.
clsFunction_Workday.txt

A simple class function to provide weekday calculation, native to Excel, in Access.
Access_MkDirMulti_Pearson.txt

A module which replaces the MkDir function, such that it verifies and creates an entire path; MkDir only creates a new directory on an existing path. Please note that this module was create by Pearson Consulting for Excel, and I simply modified it to work in Access.
Excel_Fx_NextWorkday.txt

Excel VBA Workday Function

Function that can be used to calculate target dates, taking into account a date and hours/minutes of work, compensating for weekends, but not holidays: It populates most of the Original Target Date column, I, and is built into the spreadsheet. Also, It might be inaccurate over longer time intervals, but it works in this context.
System_Globals.txt

Excel Constants as Global Vairables for Late-Binding

The entire list of Excel 2003 constants and values, useful when late-binding Excel in Access automation.
Analyst Toolkit.xla

An XLA that exhibits a floating toolbar with numerous built-in functions, including numeric formatting, special print formatting via screen selections, and a drop-down menu with parsing options.
Access_Excel_Sort.txt

From Access, manipulates an Excel workbook - it defaults to the first tab but code can be improved to specify a worksheet object or a specific tab - sorting a worksheet object on a passed field and a sort order.
Access_Excel_FindRange.txt

From Access, finds a text value in Excel and returns its range (cell). The function is passed a worksheet object, a text value to find, and an optional row to search. If no row is specified, it searches the used range of the specified worksheet.
Excel_AutoSum.txt

Sums to each column of a Excel range (UsedRange), but requires passed variables for the starting column and starting row.
Excel_AutoSave.txt

An AutoSave feature for Excel, done entirely in code. Includes a procedure for adding a dropdown menu to a toolbar.
Excel_PivotChart_Drilldown.zip

Excel Chart DrillDown: Captures Shift+Click, enabling drill down into a pivot chart. It has three (3) components, one a class module, one contains code for ThisWorkbook, and one is a regular module that sets and resets the event to the current chart. I've trimmed out anything specific to the client, but you still to debug and customize it to fit your needs.
Excel_SQLLogging.txt

In conjunction with a backend database and stored procedure, modularizes logging in Excel workbook.
Excel_Recordset2Array.txt

Executes a recordset and returns values to array. Internally, the array is transposed, then transposed again, to return the array in its expected recordset-like structure.
Excel_NewSheet.txt

Worksheet event forces naming of new sheet or chart. Used with global variable, such that if the global variable is "", the procedure executes, otherwise it skips.
Excel_IllegalCharacterReplacement.txt

Replace illegal characters: Function accepts string and replacement value; replaces string with value for a set of illegal characters, i.e.,[,],\,/,:, ', ?, and *.
Excel_FormatRange.txt

Excel VBA Example: Formatting Ranges

Formats a range: supply a range and it formats to the end of the used range, meaning converts text to values and justifying.
Excel_Validation.txt

VBA Functions to Validate Types

Multiple functions that validate data types.
Access_ExportData.txt

Export Access Tables for Excel (VBA)

Procedure to export all data tables as Excel workbooks. includes a procedure to format each workbook. Note, that the internal path is fixed and will need to be modified to suit your needs, either as a new path, or as a recipient of a more dynamic, form value.
Excel_PivotTables.txt

Creating Pivot Tables (Excel, VBA)

Two example procedures for creating pivot tables in code. One simply create a pivot table with an internally-specified worksheet, while the other works as a function, accepting various workbook-related objects, and returning a boolean to indicate success or failure.
Euro-Live.xla

Excel (pre-2007) Addin for Euro to US Conversion

An XLA for Euro-US conversion. Built several years ago, the code could be improved, but includes a menu, command bar, internet data retrieval, and special functions to convert currencies in ranges/cells.
Excel_Documentation.txt

Export VBA Modules as Text

Exports Excel modules and forms as text to a hard-coded subdirectory. In conjunction with a form, the path can be dynamic, and provide additional source code backup.

Please note that you will need to modify macro security to enable "Trust access to the Visual Basic project" via the menu (Tools | Macro | Security... | Trust Sources).
Excel_FixText.txt

Fix Numbers Stored as Text (Excel, VBA)

Fixes numbers stored as text, a common problem with Access exports to Excel, by copying the value back into the cell, avoiding formulae.
Excel_FileExists.txt

File Exists Function (VBA)

Simple function to test for a file's existence.
Excel_Printing.txt

Excel Format and Printing Functions (VBA)

Used as a function, receives an optional print orientation, and formats a worksheet for printing; provides a consistency of appearance, with coloring, header and footer. Returns a boolean.
Excel_Formatting.txt

Excel Formatting (VBA)

A collection of procedures to format numbers; previously included in an XLA to reduce effort in using functions repeatedly.
Excel_Parsing.txt

Text Parsing (Excel VBA)

A collection of procedures to parse text; previously included in an XLA to reduce effort in using functions repeatedly.
Access_VerifyStrings.txt

Verify Email and URL Strings (VBA)

Two modules to verify strings, e-mail and URL. Works in either Excel or Access.
Access_ExportTables.txt

Export Table to Excel (Access VBA)

Exports all internal tables as Excel spreadsheets. Has hard-coded internal output path, which can be modified, as well as recoded to be directed via form.
Excel_OutlookEMail.txt

Late-bound Outlook E-Mail from Excel

VBA code to create late-bound Outlook E-Mail from Excel, with abilities for To, CC, BCC, subject, memo, and attachments.
Excel_CommandBar.txt

Building an Excel Command Bar (pre-2007)

Displays the basics of building an Excel command bar, as well as how to delete it. This can be part of a larger XLA or XLS, with workbook specific functions.
Access_ExportExcel.txt

Export Recordsets to Excel

Three (3) procedures to export recordsets to Excel, including two (2) procedures that format Excel, either when called from within other procedures, or alone from a form.
Access_ExcelEnumeration.txt

Excel VBA Constants

Enumerates VBA constants, useful when Excel is late-bound in Access, or when simply late-binding Excel VBA code, since many methods and properties are not readily available.