Untitled

[dataprose]

Excel Gets Formal

No, not the classic rock album by the boys from AC/DC – Excel has a new black theme, and with a registry hack, you can give the Visual Basic Editor a black background – let’s take a look. Office 365 Pro Plus Update The Black Theme is only available for subscribers of Office 365. I’m using Office 365 Pro Plus and I had to jump through a few hoops to get the new black theme as well as the 6 new…

View On WordPress

[dataprose]

Transfer Excel To SQL

My grandparents had a small farm in Iowa . We only lived a few miles away and my brothers and I spent a great deal of time at the farm with our grandparents when we weren’t in school. I traded the rolling corn fields of Iowa for the rusty-reddish brown soil of Arizona many years ago, but I frequently think about my grandparents and working around the farm with the crops, the garden, the animals…

View On WordPress

[dataprose]

Spark Something Off - 2

In my last post on Excel Sparklines with VBA, I demonstrated: Dynamically determine a data range on a worksheet Add a Sparkline Group to the next available column Format the Sparkline Group for line and Spark Points Add the appropriate Axis to each Sparkline Group to be used as a reference to compare actual values to target values But followers of this blog (both of you) know that I loves me some…

View On WordPress

[dataprose]

Spark Something Off

A sparkline is a small intense, simple, word-sized graphic with typographic resolution. – Edward Tufte Today, I’ll look at how I might be able to : Dynamically find a Range of data by finding the first and last Cells of data Add a SparkLine Group for the data Range Format the Sparklines and add markers for various SparkPoints Add appropriate SparkAxes to be used as reference lines for visualizing…

View On WordPress

[dataprose]

Push To Excel - 2

Push To Excel – 2

The Dust Bowl ravaged the Great Plains from Canada down to Texas from 1930-1936/1940. On May 9, 1934, a large storm arose in the Great Plains pushing 350 million tons of topsoil more than 10K feet high, and carried it all the way out to the Atlantic Ocean leaving 1/4 inch of soil on ships off the coast on May 11. The History Channel produced a show, aptly named “Black Blizzard”, that does an…

View On WordPress

[dataprose]

Push To Excel

Ahhh, mowing the lawn. Going to William’s 66 to buy some fuel. The smell of the gas, the fresh cut grass, fighting the fly-wheel, trying to get perfectly straight lines, rushing to beat the encroaching summer storm, getting a little too close to mom’s peonies – oops!

I traded a mix of Bluegrass, Rye and Fescue of the Midwest a long time ago for the rusty reddish brown that dominates the…

View On WordPress

[dataprose]

What's In A Name [Excel VBA Named Arguments]

Being the 2nd in a series of posts on “Names” in Excel.

Excel uses several “Names”. In a previous post, I wrote about Named Formulas. Today, I’ll take a look at Named Arguments.

Named Arguments

Named arguments are that descriptive tags you sometimes see in VBA code snippets. They are not required, hence, “sometimes”. Here’s a sample of a few:

In the sample snippet, I created a function to get a…

View On WordPress

[dataprose]

What's In A Name [Excel Named Formulas]

What’s In A Name [Excel Named Formulas]

Excel uses several Names. I’ll take a look at a few beginning with Named Formulas. When developing budgets and forecasts, it is convenient to create an annualized amount for various accounts and spread the amount over time in your model whether it be 52 weeks, 12 months, 360 days, whatever your model calls for – generally driven by your domain or sphere of activity.

Named Formulas

I have…

View On WordPress

[dataprose]

Customize Your Excel Tables

Customize Your Excel Tables

I really like a lot of the Custom Hot Rods out there. Among my favorites are the Ford Mercuries that have been chopped with a Photon Laser and lowered. Among the Custom Hot Rod group the preference is for the 1951 Model. But I prefer the 1940 Model shown here with the hood coming to an apex, that awesome grille,split windshield and those enormous swooping fenders. Cars nowadays have by and large…

View On WordPress

[dataprose]

Excel Used Ghost

Here in the States – All Hallows’ Eve (Halloween) is quickly approaching. One of my favorite times of the year with leaves changing color, daylight getting shorter, a crispness in the air, football season is in full swing, and the Fall Classic (Major League Baseball) begins in a few weeks.

All Hallows’ Eve is a time for hobgobblery and apparitions of all sorts as with Charlie Brown and the Peanu…

View On WordPress

[dataprose]

Bringing The Full Power of SQL To Bear In Excel

Bringing The Full Power of SQL To Bear In Excel

In my last post on using ActiveX Data Objects (ADO) with Excel VBA, I demonstrated some code to load a Recordset, filter the Recordset using the Recordset’s Filter Prpoerty, and copy the Filtered Recordset to a Worksheet using the CopyFromRecordset Method of the Range Object.

I put a link to the blog post on the Excel VBA and Users Group on LinkedIn. To our good fortune, James Wilsonwas reading.…

View On WordPress

[dataprose]

Opinion: Data Should Begin At R1C1

Opinion: Data Should Begin At R1C1

In Coffee Talk on Satruday Night Live, Linda Richman, played by Mike Myers would hurl out “Discussion Topics”. Here are a few of my favorites:

“The Partridge Family were neither partridges, nor a family. Discuss.”

“The radical reconstruction of the South after the Civil War was neither radical nor a reconstruction. Discuss.”

“The jelly bean is neither made of jelly nor is it a bean. Discuss.”

Ho…

View On WordPress

[dataprose]

Weeding Out Recordsets [ADO]

Weeding Out Recordsets [ADO]

Dandelions are weeds. I’ve spent more hours weeding them out of my family’s yard and my grandparents’ yards than I care to count. Yet, when you come across them in a meadow, they are very nice to look at and add a kind of tranquility in their own right. They are edible and make a nice tasting wine. Here’s a recipe from AllRecipes. Let us know how it turns out.

Today’s post, however, is not about…

View On WordPress

[dataprose]

This Will Go Down On Your Permanent Record(set) [ADO]

This Will Go Down On Your Permanent Record(set) [ADO]

I hope you know that this will go down on your permanent record Oh yeah? Well, don’t get so distressed Did I happen to mention that I’m impressed?

So go the lyrics from Kiss Off by the Violent Femmes pictured here. Great song from the 80’s. If you are not familiar with the Femmes, check ‘em out – highly recommended.

However, today’s post is not about the Alternative Rock scene of the early 80’s.…

View On WordPress

[dataprose]

Advance To R1C1

AAAAAARRRRGGGGHHHHH!!!!! I just hate reviewing an Excel Workbook with lots of tabs and the ActiveCell is different on every tab. Let’s look at some ways to fix this. Before I can get going, I need to:

Add several copies of a worksheet to the active workbook

Randomize the ActiveCell on each worksheet

edit:While my main point of this post is the GoTo Method of the Excel Application Object, I…

View On WordPress

[dataprose]

Undaunted By UnPivot II

Undaunted By UnPivot II

Some time back, Shane Devenshire and I added our two cents to a question a user asked on LinkedIn here. So when I decided to write a post regarding UnPivot using nested For..Next Loops, it only made sense to ask Shane if he would like to write a guest-post and tutorial on his process and Normalization Utility.

So, without further ado, take it away Shane!

Un-pivoting Excel Spreadsheet data –…

View On WordPress

[dataprose]

Undaunted By UnPivot

Well, maybe an UnPivot or Reverse CrossTab process in Excel with VBA does not require the same amount of courage as Meriwether Lewis (pictured here) must have had as he and Clark set out on their exploration of the Louisiana Territory – nonetheless, the process can seem a bit overwhelming to a newcomer. I’ll do my best to explain it in a straight-forward manner.

Many times, we receive data in a…

View On WordPress