Understanding Data Normalization: Bringing Order to Database Chaos

Data normalization represents a sophisticated approach to database design that transforms chaotic, unstructured information into a streamlined, efficient system. Much like an architect carefully plans a complex building, database professionals use normalization to create robust, intelligent data structures that can adapt and scale with organizational needs.
 

Understanding the Data Dilemma

Imagine a small business tracking customer orders using a single, massive spreadsheet. Every time a customer places an order, their complete details are rewritten: name, address, contact information, along with product specifics and pricing. This approach quickly becomes a nightmare of redundancy, inefficiency, and potential errors. Normalization emerges as the elegant solution to this complex problem. It’s not just a technical process, but a strategic methodology for organizing information in a way that minimizes redundancy, maximizes data integrity, and creates a flexible foundation for future growth.

Consider a scenario where a customer’s telephone number is repeated with every single order. If that customer changes phone numbers, you’d need to update dozens or hundreds of records manually. Normalization eliminates this complexity by storing core information in a single, authoritative location.
 
Continue reading →

Navigating the Transition: Adapting VBA Procedures for New Outlook Compatibility

Microsoft’s introduction of the New Outlook has presented significant challenges for developers and power users who rely on VBA (Visual Basic for Applications) to automate Outlook tasks. This new version, built on a different technology stack, does not support traditional COM-based automation methods. As a result, existing VBA procedures need to be adapted to ensure compatibility across both classic and New Outlook environments.
 

Understanding the Challenge

The New Outlook Paradigm

The New Outlook, part of Microsoft’s modernization efforts, is built on web technologies and doesn’t support the COM object model that VBA traditionally uses. This fundamental change means that existing VBA scripts and add-ins will NOT function in the new environment.

Implications for VBA Developers

  • Limited Automation: Direct manipulation of Outlook objects through VBA becomes challenging or impossible in New Outlook.
  • Compatibility Issues: Scripts that work flawlessly in classic Outlook may fail entirely in the new version.
  • Need for Alternative Approaches: Developers must explore new methods to achieve similar functionality in New Outlook.

Detecting New Outlook

The first step in adapting your VBA procedures is to implement a reliable method for detecting whether the user is running New Outlook or the Classic version.
 
Continue reading →

Sanitizing Ingested Data: Taming the Wild Unicode Jungle

The Challenge of Raw Data

In the world of data processing, we often find ourselves dealing with information from various sources. This “ingested” data can be a bit like a wild animal – unpredictable and sometimes difficult to handle. One of the most common challenges? Strings containing Nul characters and other Unicode oddities that refuse to play nice with our systems.
 

The Mystery of the Question Marks

Have you ever output a string to the VBE Immediate Window, only to be greeted by a parade of question marks? These enigmatic symbols are often the calling card of characters that can’t be properly rendered. It’s like trying to read a book written in invisible ink – frustrating and not very useful!
 

The Sanitization Solution

So, how do we tame this Unicode jungle? The answer lies in sanitization – carefully processing our raw strings to remove these troublesome characters.

Continue reading →

Unveiling the Hidden Characters in Your Strings

Have you ever encountered mysterious behaviors when working with strings or text from various sources? You’re not alone. Many developers have faced situations where the Len() of a string doesn’t match what’s visually apparent on the screen. Perhaps, you’ve gotten string which displays as a series of ‘?’ characters,
 

The Culprits: Non-Printable and Unicode Characters

These puzzling discrepancies often stem from non-printable and Unicode characters. These sneaky characters can be part of your data but don’t necessarily show up when displayed. They’re like invisible guests at a party – present but unseen!
 
Continue reading →

Add a Term to a String Every Nth Character

While working on my latest pet project:

Using VBA to Identify File Types Using Their File Signatures
So this one is a bit of a niche need, but I recently was part of a discussion in which a user was wanting to recover some files which he insisted were xlsx, but Excel was reported as invalid. He supplied a sample file and he was correct, Excel wouldn’t open it. I started to dig and decided to examine the file in more detail and discovered the file was NOT an xlsx file, regardless of the extension, it was in fact an mp4 video file. Now, I did the above manually and thought to myself if VBA could do…

Continue reading →

I needed to ‘massage’ some signatures into a more common format.  That is I needed to insert a space every 2 characters to match up with the format I was already using.

My original objective was very simple, I had strings like:

53514C69746520666F726D6174203300

and I wanted

53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00

So, I merely wanted to insert a space after every 2nd character in the string.
 
Continue reading →

Working With Excel Column Letter and Number in VBA

In Excel, when working with columns in VBA code we need to often juggle between the alphabetic and numeric values. Some functions require the alphabetic value and others the numeric. Then we might get one, but need to supply the other.

Below are 2 simple functions that allow us to convert back and forth between the 2 formats.
 
Continue reading →

Working With Excel Area Selection In VBA

In a recent project of mine, I needed to allow the users to make a selection within their worksheet and then process the data contained within.

I dusted off some older projects I had done to extract a couple little procedures I thought I’d share as they can be useful when dealing with this type of scenario.  They’re all used to determine the boundaries of the selected area and the location of the ‘ActiveCell’.
 
Continue reading →

How to Determine The Last Used Column in a Row in Excel

A while back, I demonstrated how easy it was to determine the last used row in a given Excel column:

MS Excel - VBA - Determine Last Row
I had been needing to find an easy way to determine the last used row in a given column, or possibly the next available row in a column. I search Google and found numerous examples, which I used for a short period of time. Code such as: Range("A65536").End(xlup).Select But that code was not 100% reliable and I wanted, as much as possible, to avoid useless .Select statement to try and optimize my code as best I could. Well, I finally managed to get it down to a single line of code without the use of any .Select statements and which…

Continue reading →

I realized that I never provided the equivalent code to determine the last used column in a given row, which can be equally useful.

Continue reading →

Using VBA to Identify File Types Using Their File Signatures

So this one is a bit of a niche need, but I recently was part of a discussion in which a user was wanting to recover some files which he insisted were xlsx, but Excel was reported as invalid.

He supplied a sample file and he was correct, Excel wouldn’t open it. I started to dig and decided to examine the file in more detail and discovered the file was NOT an xlsx file, regardless of the extension, it was in fact an mp4 video file.

Now, I did the above manually and thought to myself if VBA could do such an analysis automatically?

Spoiler alert, the answer is yes. Keep reading.

Continue reading →

Let’s Improve The New Microsoft Access Monaco SQL Editor

Collaborating for Improvement: Addressing the Flaws of the Monaco SQL Editor

It’s time for us to unite and demand that Microsoft take significant steps to improve the current state of the Monaco SQL Editor. While this new feature was marketed as a major upgrade, the reality is far from what users were promised.

I’ve put together a series of suggestions for the Feedback Portal, and I urge you to take just 30 seconds to upvote each one. It’s crucial that Microsoft understands how vital it is to fix and enhance the initial release of the Monaco SQL Editor, which has been riddled with issues since its rollout.
 
Continue reading →