Code || VBA, .NET, SQL

This cheat sheet helps you choose the best Azure Machine Learning Studio algorithm for your predictive analytics solution. Your decision is driven by both the nature of your data and the question you’re trying to answer.

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.

We all make bad decisions sometimes, but have you ever wondered what mental obstacles can lead you astray? This infographic goes over 20 of the most common cognitive biases that can mess with your head when it’s decision time.

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.

When founders are getting started with the Canvas, they tend to focus on the right side. And rightly so, Blank notes—that’s where product/market fit will unfold. But it’s the oft-neglected left side of the canvas that will tell you what your founding team should look like. On a successful Canvas, the two sides should mirror each other; the right is all about customers, and the left tells you what you need to reach them.

Filling in the Canvas is level one. But understanding the interplay of the boxes takes you to level two. That’s when your Canvas goes from a checklist to a strategy.

Finding the right co-founder is one of the most instrumental pieces of that strategy. Start by looking carefully at your Key Activities box. Of the nine modules on the Canvas, it most defines what you need to do to build your product and deliver it successfully to consumers. Then jump down to the next box labeled Key Resources, where you’ll list what’s required to execute those activities. Is there a gap between what you’re going to need and what you bring to the table yourself? That delta reveals the expertise you need to find in your co-founder.

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.

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.

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.

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.

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.

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.

This is code possible through .NET, in that it bridges across to COM for automation in VBA. This concept can be extended to work for many other COM-based applications. This code allows the add-in to expose internal .NET-coded procedures to Excel COM, extending the use of the .NET code.

This is derived from actual code of mine, and elements of this sample combine the Facade pattern with the Publisher (Observer) pattern. The primary class, based on the ISubscriber interface, is fairly complicated to use, requiring delegates, threading, and asynchronous callbacks. The facade, based on ISubscriberFacade, encapsulates all the methods required to work with the Windows Communication Foundation (WCF) service , handling threading, delegate creation, and asynchronous callbacks internally, so that that the clients only need to create the object. I wrote the encapsulating client to ease the adoption of the WCF service for legacy clients, seeing that the code complexity was likely a hurdle.

Salient Characteristic(s)

Reduces or hides complexities to other clients or systems

Simplified interaction between systems and/or types

Notes

DispatchingObservableCollection is based on ObservableCollection commonly used in Windows Presentation Foundation (WPF) applications and available here.

Some classes are not available in this example, i.e., TrackedConnections, CubeInfo, and ConfigurationList, since they are part of the larger project, and would detract from this example

A fairly straightforward 'fill-in-the-blanks' example of a Lazy Initialization design pattern, except that this uses an enumeration for the type - I hate passing strings, so easy to screw up - as well as a struct for the type that is lazily initialized.

Following is an example of the Factory Method. In this case, a method call sets and returns the type. Also, pardon the values set as field, rather than as properties, but this is a simple example, rather than a full implementation.

My work requires working with Windows Communication Foundation (WCF) services daily, and I typically have two (2) types of clients, either Excel, which is the largest part of my user base, or external applications, usually server-based. This example uses the idea of two (2) client types, each requiring a different host/port combination, which the abstract factory pattern will return.

The Singleton design pattern seems generally frowned upon, except for use in logging classes. Below are several variants of the Singleton design pattern, of which, only the thread-safe version merits attention:

Working with WPF and ObservableCollection using threads, one will run into the problem whereby you cannot update an ObservableCollection from outside the owning thread; Delegates and Invoke will not work. A solution is to inherit and expand the class, as is done in this example on michIG's Blog

The attached file contains the original code in C#, as well as code converted to VB.NET.

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.

This is C# WinForm code that demonstrates using Outlook (requires a third-party COM product called Redemption) to get at the SMTP addresses from the outlook address book. It takes about 60 minutes to output a file, also attached, of ~160k users and their SMTP address, but it shows the basics of the process. This is not likely appropriate for server use. Below is the code, but an easier solution might simply be for the code sending the email to iterate through the list and send an email to each person, and record failure for any particular address

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.

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.

An Access DB that queries the domain for object, e.g., user ID's. Requires that you modify code to specify a domain The code can be run from the supplied form or directly, and truncates and appends to the supplied table. Please note, this was last edited in Access 2007, and if you are using an earlier version, you will need to correct broken references.

A class that encapasultes an import process, although somewhat tied to specific process, easy modifiable to decouple and/or modify it as needed. In general, the process requires a source data file, an import type specified, staging and master tables, a report date field, a level1 field (a coupled requirement that can be removed), and a cleanup bit for dirty imports that can be turnd on or off.

Access code to backup all modules, forms, and queries, as well as create tables of table features; code checks for tables' existence and creates if necessary. Dynamically creates backup folder in the folder of the Access MDB.

Form Resizer For Microsoft Access (By Jamie's Software)
The Form Resizer is a Microsoft Access MDB (Microsoft Database) that allows you to install a module in your own Access MDBs that can resize your program's forms to fit the current screen resolution. It is intended for Access developers with basic Visual Basic for Applications (VBA) coding knowledge. Works with Access 97, 2000, 2002 and 2003.

in SMS, a simple graphical interface for installations. Once compiled, one only need modify the INI file with application information, path information, and EXE name. The EXE and INI should be located in the same directory.

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.

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.

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.

Verifies that columns in a table exist, created to verify column heading on imported Excel spreadsheets. The function receives a table name as string, and comma-delimited column names as string, verifies existence of table, parses the column string into array, and then tests for each column, returning TRUE if all exist, else False.

Procedures (2) to compare strings in Access, either by stripping all non-numeric characters, or by stripping all non-alpha characters. Useful in queries when having to compare strings that are functionally the same, but typed differently, e.g., with hyphens or commas.

Access MDB to backup stored procedures. Code checks for table's existence and creates if necessary. Requires modification of module level variables to fit your environment. Creates both a date-stamped folder of individual SQL files, as well as an export to a single HTML document.

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.

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.

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.

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.

Exports Access components to tables, in the case of queries and tables, or subdirectories, for modules, to a hard-coded subdirectory. Also includes function to enumerate Access data type values as strings. In conjunction with a form, the path can be dynamic, and provide additional source code backup.