Answer My Searches

Here is the VBA code to do this: http://visualbasic.ittoolbox.com/code/archives.asp?d=1466&a=s&i=7

So download and extract that code. Then within your VBA project add DOSOutpus.cls from the zip file to your project.

Then here’s a function I wrote which serves as an example of how to use this code, and it is also just a handy function you may want to use. You just feed it a commandline string (e.g. “ping”) and get the result back.

Public Function popen_and_read(commandline As String) As String
    Dim objDos As DOSOutput
    Set objDos = New DOSOutput
    objDos.commandline = commandline
    popen_and_read = objDos.ExecuteCommand
    Set objDos = Nothing
End Function

This was a bit tricky to find and big thanks to the author of this module for making this thing. I would have never been able to do this on my own.

The terms I searched for were:

1. VBA Spawn
2. VBA System Call
3. VBA Open Pipe

The Lookup, Hlookup, and Vlookup functions in Excel are pretty bad. I found that they die with #VALUE, or #N/A if they don’t find the value you’re looking up, and today I found that my lookup function was only looking for the first word in my lookup value!

So here is a better lookup function to use in Excel when you want it to return a specified default value if your value is not found:
(more…)