Have two Excel Workbooks Open at the Same Time

I know that this is one of my pet peeves. If you open multiple workbooks in Excel, they all get stuck together in the same window, and it’s hard to do split screens or multiple monitors.

Thankfully there’s an easy way to get two (or more) independent windows of Microsoft Excel to open. All you have to do is open Excel from your start menu each time you want an independent instance of Excel to run, and then open the worksheet in your new program window.

Volia! Two ribbons, two spreadsheets, and you can drag them around to two different monitors if you please.  They can finally be two different sizes, and whatever else you need them to do.  One minor downside though, is that referencing cells between the 2 instances of Excel doesn’t work.

[Cheatsheet] VBA String Functions

Function What Does it do? Example Result
Len(str) Returns the length of the string Len(“Hello”) 5
Ucase(str) Changes the string to upper case Ucase(“Hello”) HELLO
Lcase(str) Changes the string to lower case Lcase(“Hello”) hello
Left(str, num) Shorten the string from the left Left(“Hello”,3) Hel
Right(str, num) Shorten the string from the right Right(“Hello”,3) llo
Mid(str, start, length) Extract the middle of a string Mid(“Hello”,2,2) el
Instr(start, look in this string, find this string) Find if a string is in another string Instr(0,”Hello there”,”Hello”) 1
Trim(str) Removes preceding and trailing spaces Trim(” hello there “) hello there
LTrim(str) Removes spaces from the beginning of a string Ltrim(” hello “) hello
Rtrim(str) Removes spaces from the end of a string Rtrim(” hello “)  hello
String(num, char) Repeats the char num times String(2, “a”) aa
Space(num) Makes a certain num of spaces Space(2)
Cint(str) Change a string to a integer Cint(111.1) 111
Clng(str) Change a string to a long (use if you get an overflow error) Clng(111.1) 111
Cdbl(str) Change a string to a double Cdbl(111.1) 111.1
Split(str,delimiter) Split a string into an array Split(“Hello,There,Friend”, “,”) (0) = Hello
(1) = There
(2) = Friend
Join(array, delimiter) Join an array into a string Join(array(“Hello”, “There”, “Friend”),”,”) Hello,There,Friend

Simple Regular Expression Tutorial for Excel VBA

I’ll go through a simple tutorial for using regular expressions for VBA.  This is a great quick start guide for people who have used regular expressions before in other languages.  Just as a review, regular expressions are used to match patterns in strings.  This is a powerful tool that can help you data cleanse and data mine.  The directions are the same for Microsoft Access as well.

First thing first, let’s enable the reference in Excel so that we can use this functionality.

Go to Tools > References

Then find Microsoft VBScript Regular Expressions 5.5

Click Ok, and now you’ll be able to create a RegExp object to use in your VBA.

Here’s a basic function that finds 5 letter words in a string.

Here’s a copy of the Excel file that I am using in the example: RegEx Excel Tutorial XLS File

Function findFiveLetter(sentence)
    Dim regEx As New VBScript_RegExp_55.RegExp
    Dim matches, s
    regEx.Pattern = "\W\w{5}\W"
    regEx.IgnoreCase = True 'True to ignore case
    regEx.Global = True 'True matches all occurances, False matches the first occurance
    s = ""
    If regEx.Test(sentence) Then
        Set matches = regEx.Execute(sentence)
        For Each Match In matches
            s = s & " Position: " & Match.FirstIndex
            s = s & " Word: " & Match.Value & " "
            s = s & Chr(10)
        Next
        findFiveLetter = s
    Else
        findFiveLetter = ""
    End If
End Function

Now let’s break it down.

First you need to declare your regEx obejct with:

Dim regEx As New VBScript_RegExp_55.RegExp

Next you give your object some parameters.

The most important is .Pattern, which is the pattern of characters that you want your function to find.  Then there are a couple of settings like Global, if you want it to search for all occurrences (TRUE) or just the first occurrence (FALSE), and also IgnoreCase.

Here’s a simple guide to get you started on writing regex patterns.

regEx.Pattern = "\b\w{5}\b"
    regEx.IgnoreCase = True 'True to ignore case
    regEx.Global = True 'True matches all occurances, False matches the first occurance

Now to test if the pattern has been found in a string you can use

regEx.Test(sentence)

To get an array of matches from your regex you simply have the regex execute.

Set matches = regEx.Execute(sentence)

To get the results from this array object, you can reference matches(n) or use a For Each loop. There are two important pieces of information returned in the array, the index of the match and the actual match.

I used a For Each:

For Each Match In matches
            s = s & " Position: " & Match.FirstIndex 'Position of the match
            s = s & " Word: " & Match.Value 'The actual match
            s = s & Chr(10) 'Prints a new line
        Next

Here’s the example output:

Line Matches
What began as a small group of protesters expressing their grievances  Position: 5 Word: began
Position: 16 Word: small
Position: 22 Word: group
Position: 53 Word: their
about economic inequities last month from a park in New York City  Position: 0 Word: about
Position: 31 Word: month
has evolved into an online conversation that is spreading across the harry  Position: 69 Word: harry
country on social media platforms.  Position: 18 Word: media

Here’s a copy of the Excel file that I used in the example: RegEx Excel Tutorial XLS File
The regex function is called findFiveLetter(“put a string in here”)

Basic Regular Expression Patterns for Beginners

This is super simplified, but it’s enough to get started with. I remember being confused by the gigantic tables with all the special clauses, so when you’re beginning keep it simple.

Remember if you want to match a literal character you can just type it out, unless there’s a special character. Special characters have meanings in regex patterns, so you need to type a \ in front of them if you want to specifically match them in the pattern.

Common special characters are: + . \ – [] {} $ ? |

Example: If I wanted to match taste, taster, tasters, I would type a pattern like taste\w{0,2}

If I wanted to match taste!, I would write a pattern like taste\!

Code What does it Match?
\d Numbers
\w Word characters like letters of any case
\s Spaces
\b Boundary of a word character
\D Not a number
\W Not a word character
\S Not a space
\n Line break
\r Line break
\t Tab
? Previous code is optional
* Repeat previous code 0 to infinity
+ Repeat previous code once or more
{1} Repeat previous code once (can use any number)
{1,2} Repeat previous code 1 to 2 times (can use any numbers)

[Demo] Use a PHP & MySQL database to load markers on a Google Map

As of 2018, Google Maps now requires an API key so this will not work for the most part.

Setting up a Google Maps to use PHP and a MySQL back-end isn’t the most straight forward thing to do, especially in comparison to using Google Map’s JavaScript API V3.  I wanted to load a map from a MySQL database with lat, longs, the name of the marker, and a description to put in an information window (it’s the little text bubble that pops up when you click on a marker).  I didn’t want to learn how to use the Maps API through PHP, so what did I do?

  1. Loaded the name, description, latitude, and longitude into a MySQL table
  2. Had a PHP query output a hidden input with the name, description, latitude and longitude in a single string with each field and marker separated by some sort of delimiter that you split the string with.
  3. When the page loads, jQuery will take that hidden input and split the string by the delimiter
  4. Use jQuery and Google Maps JavaScript API V3 to loop through the arrays of markers and plop them to the map and also add the info windows.

Here’s a link to the finished version demo, it should work in Chrome, Firefox and IE: http://www.macrostash.com/wp-content/plugins/big-ten-demo.php

There’s a lot going on behind the scenes, so this is a pretty long demo. Features this demo shows:

  1. Multiple Info-windows
  2. Multiple Markers
  3. Auto-sizing the map
  4. jQuery
  5. MySQL
  6. PHP
  7. Javascript, especially string splitting and arrays

The MySQL Database

I’m using the locations of the 12 colleges/universities in the Big Ten Conference. The XLS of the data that I used is available: Big Ten Schools Demo Data

Basically, what I’ve got there is the latitude, longitude, name of the location, address of the location, and a random fact. In this case the random fact is which division the school is in. Legends or Leaders. Here’s the first school, so that you get an idea of what the data looks like.

Id Lat Long Name Returned Address Division
1 40.01264 -83.0308 Ohio State University – Columbus Campus The Ohio State University, 1739 N High St, Columbus, OH 43210, USA Leaders

The PHP

//Connect to the MySQL database that is holding your data, replace the x's with your data
mysql_connect("localhost", "xxxxx_xxx", "xxxx") or
 die("Could not connect: " . mysql_error());
mysql_select_db("xxxxx_xxxx");
 
//Initialize your first couple variables
$encodedString = ""; //This is the string that will hold all your location data
$x = 0; //This is a trigger to keep the string tidy
 
//Now we do a simple query to the database
$result = mysql_query("SELECT * FROM `big-ten`");
 
//Multiple rows are returned
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
    //This is to keep an empty first or last line from forming, when the string is split
    if ( $x == 0 )
    {
         $separator = "";
    }
    else
    {
         //Each row in the database is separated in the string by four *'s
         $separator = "****";
    }
    //Saving to the String, each variable is separated by three &'s
    $encodedString = $encodedString.$separator.
    "<p class='content'><b>Lat:</b> ".$row[1].
    "<br><b>Long:</b> ".$row[2].
    "<br><b>Name: </b>".$row[3].
    "<br><b>Address: </b>".$row[4].
    "<br><b>Division: </b>".$row[5].
    "</p>&&&".$row[1]."&&&".$row[2];
    $x = $x + 1;
}

The HTML (Body)

We have 2 parts to the body.

The input part has the encoded string, and it’s hidden.

The map_canvas is where the Google map will be loaded into.

<body>
    <div id='input'>
        <input type="hidden" id="encodedString" name="encodedString" value="<?php echo $encodedString; ?>" />
    </div>
    <div id="map_canvas"></div>
</body>

 

The JavaScript

//This javascript will load when the page loads.
jQuery(document).ready( function($){
 
        //Initialize the Google Maps
        var geocoder;
        var map;
        var markersArray = [];
        var infos = [];
 
        geocoder = new google.maps.Geocoder();
        var myOptions = {
              zoom: 9,
              mapTypeId: google.maps.MapTypeId.ROADMAP
            }
        //Load the Map into the map_canvas div
        var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
        map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
 
        //Initialize a variable that the auto-size the map to whatever you are plotting
        var bounds = new google.maps.LatLngBounds();
        //Initialize the encoded string
        var encodedString;
        //Initialize the array that will hold the contents of the split string
        var stringArray = [];
        //Get the value of the encoded string from the hidden input
        encodedString = document.getElementById("encodedString").value;
        //Split the encoded string into an array the separates each location
        stringArray = encodedString.split("****");
 
        var x;
        for (x = 0; x < stringArray.length; x = x + 1)
        {
            var addressDetails = [];
            var marker;
            //Separate each field
            addressDetails = stringArray[x].split("&&&");
            //Load the lat, long data
            var lat = new google.maps.LatLng(addressDetails[1], addressDetails[2]);
            //Create a new marker and info window
            marker = new google.maps.Marker({
                map: map,
                position: lat,
                //Content is what will show up in the info window
                content: addressDetails[0]
            });
            //Pushing the markers into an array so that it's easier to manage them
            markersArray.push(marker);
            google.maps.event.addListener( marker, 'click', function () {
                closeInfos();
                var info = new google.maps.InfoWindow({content: this.content});
                //On click the map will load the info window
                info.open(map,this);
                infos[0]=info;
            });
           //Extends the boundaries of the map to include this new location
           bounds.extend(lat);
        }
        //Takes all the lat, longs in the bounds variable and autosizes the map
        map.fitBounds(bounds);
 
        //Manages the info windows
        function closeInfos(){
       if(infos.length > 0){
          infos[0].set("marker",null);
          infos[0].close();
          infos.length = 0;
       }
        }
 
});

 

All Together With the HTML

*The comments are getting highlighted a little bit weird (I set it to HTML syntax), but the code should copy paste properly into a new file.

<html>
    <head>
    <script type='text/javascript' src='jquery-1.6.2.min.js'></script>
    <script type='text/javascript' src='jquery-ui-1.8.14.custom.min.js'></script>
    <style>
 
        BODY {font-family : Verdana,Arial,Helvetica,sans-serif; color: #000000; font-size : 13px ; }
 
        #map_canvas { width:100%; height: 100%; z-index: 0; }
    </style>
    <script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false" /></script>
    <script type='text/javascript'>
 
    //This javascript will load when the page loads.
    jQuery(document).ready( function($){
 
            //Initialize the Google Maps
            var geocoder;
            var map;
            var markersArray = [];
            var infos = [];
 
            geocoder = new google.maps.Geocoder();
            var myOptions = {
                  zoom: 9,
                  mapTypeId: google.maps.MapTypeId.ROADMAP
                }
            //Load the Map into the map_canvas div
            var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
            map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
 
            //Initialize a variable that the auto-size the map to whatever you are plotting
            var bounds = new google.maps.LatLngBounds();
            //Initialize the encoded string       
            var encodedString;
            //Initialize the array that will hold the contents of the split string
            var stringArray = [];
            //Get the value of the encoded string from the hidden input
            encodedString = document.getElementById("encodedString").value;
            //Split the encoded string into an array the separates each location
            stringArray = encodedString.split("****");
 
            var x;
            for (x = 0; x < stringArray.length; x = x + 1)
            {
                var addressDetails = [];
                var marker;
                //Separate each field
                addressDetails = stringArray[x].split("&&&");
                //Load the lat, long data
                var lat = new google.maps.LatLng(addressDetails[1], addressDetails[2]);
                //Create a new marker and info window
                marker = new google.maps.Marker({
                    map: map,
                    position: lat,
                    //Content is what will show up in the info window
                    content: addressDetails[0]
                });
                //Pushing the markers into an array so that it's easier to manage them
                markersArray.push(marker);
                google.maps.event.addListener( marker, 'click', function () {
                    closeInfos();
                    var info = new google.maps.InfoWindow({content: this.content});
                    //On click the map will load the info window
                    info.open(map,this);
                    infos[0]=info;
                });
               //Extends the boundaries of the map to include this new location
               bounds.extend(lat);
            }
            //Takes all the lat, longs in the bounds variable and autosizes the map
            map.fitBounds(bounds);
 
            //Manages the info windows
            function closeInfos(){
           if(infos.length > 0){
              infos[0].set("marker",null);
              infos[0].close();
              infos.length = 0;
           }
            }
 
    });
    </script>
 
    </head>
    <body>
    <div id='input'>
 
        <?php
 
        //Connect to the MySQL database that is holding your data, replace the x's with your data
        mysql_connect("localhost", "xxxxx_xxx", "xxxx") or
         die("Could not connect: " . mysql_error());
        mysql_select_db("xxxxx_xxxx");
 
        //Initialize your first couple variables
        $encodedString = ""; //This is the string that will hold all your location data
        $x = 0; //This is a trigger to keep the string tidy
 
        //Now we do a simple query to the database
        $result = mysql_query("SELECT * FROM `big-ten`");
 
        //Multiple rows are returned
        while ($row = mysql_fetch_array($result, MYSQL_NUM))
        {
            //This is to keep an empty first or last line from forming, when the string is split
            if ( $x == 0 )
            {
                 $separator = "";
            }
            else
            {
                 //Each row in the database is separated in the string by four *'s
                 $separator = "****";
            }
            //Saving to the String, each variable is separated by three &'s
            $encodedString = $encodedString.$separator.
            "<p class='content'><b>Lat:</b> ".$row[1].
            "<br><b>Long:</b> ".$row[2].
            "<br><b>Name: </b>".$row[3].
            "<br><b>Address: </b>".$row[4].
            "<br><b>Division: </b>".$row[5].
            "</p>&&&".$row[1]."&&&".$row[2];
            $x = $x + 1;
        }        
        ?>
 
        <input type="hidden" id="encodedString" name="encodedString" value="<?php echo $encodedString; ?>" />
    </div>
    <div id="map_canvas"></div>
    </body>
</html>

 

Quick Tips: VBA, Make a file dialog open

Here’s the code to make a file dialog to open up. Once the user has selected a file, it will be saved in the fileName variable, and you can use that variable to open up the file contents.

In order for this code to work, you must activate the Microsoft Office 14.0 Object Library, or else your VBA will result in an error.  This VBA should work in Microsoft Access and Microsoft Excel.

Dim fileName As String
 
Application.FileDialog(msoFileDialogOpen).Show
fileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

Excel 2007: Adding a Secondary Axis to a Graph

It’s pretty easy to add a secondary axis to a graph if you’d like to plot two different series of data that had different scales or units.  Generally you only want to have graphs of data with different units, if you’re trying to show the relationship between the two series, so use this tip sparingly.

So I’ve set up a quick demo, using fake data of a personal library.  This is what usually happens if you try to graph percentages and values on the same graph.  It’s ugly, so we’ll need add a secondary axis for the % Science Fiction series.  Here’s the finished Excel file, so you can follow along: Secondary Axis Excel File

image

So Right Click the line that you want to be plotted on the secondary series. Now click on the “Format Data Series” option, and a new box should pop up.   Under the “Series Options” tab, you will be able to set the data to be plotted on the secondary axis.

imageimage

And here’s the result.

image

Get Visual Basic 2010 Express to query an Oracle database

So one of the limitations of Visual Basic 2010 Express is that you can only add data sources to link or query Microsoft databases such as an Access database or MsSQL. So if you need to query an Oracle or mySQL database, for example, it seems that you’re out of luck unless you upgrade to the paid version of the software. Thankfully if you have Microsoft Access on your computer there’s a workaround.

All you need to do is set up a linked database in Microsoft Access (look up ODBC drivers if you’re having trouble) and you can query that database as a middleman from your Visual Basic program.

Access Tips: Watch out for case senstitive situations

I just spent like 2 hours trying to get a query with like 3 levels of sub-queries that I built in a sandbox with copied tables in Microsoft Access to work under a live ODBC linked connection with an Oracle database.

Guess what broke my query?

3 references to a user input form that were missing once capitalized letter.

Access Database on Shared Drive Opening as Read-Only

I had some issues with one user who was not able to open a Microsoft Access database on a shared drive.  The user would try to open the database, and it would only allow the user to use it as read only, which was not very useful.  Every other user was able to use the database with out any trouble.  There were no issues with permissions on any other databases on the shared drive or with any linked tables.

We were able to fix the issue by going into the permission for the database (Right Click -> Properties -> Security) and adding the user directly as an administrator to the database.  I have no idea why the user suddenly had issues accessing the database, as they had been using it on a daily basis for about a month with no issues.  Hopefully, this tip might save you some headache.