Capture output value from a shell command in VBA?

Based on Andrew Lessard’s answer, here’s a function to run a command and return the output as a string – Public Function ShellRun(sCmd As String) As String ‘Run a shell command, returning the output as a string Dim oShell As Object Set oShell = CreateObject(“WScript.Shell”) ‘run command Dim oExec As Object Dim oOutput As Object …

Read more

Get Name of Current VBA Function

There’s nothing to get the current function name, but you can build a fairly lightweight tracing system using the fact that VBA object lifetimes are deterministic. For example, you can have a class called ‘Tracer’ with this code: Private proc_ As String Public Sub init(proc As String) proc_ = proc End Sub Private Sub Class_Terminate() …

Read more

How to unit test Excel VBA code

Disclaimer: I own Rubberduck’s GitHub repository, and I’m one of the devs involved in the project. Rubberduck is under active development. It’s much more than a unit testing tool for VBA though, but it works pretty well and lets you write VBA unit tests pretty much without any boilerplate: ‘@TestModule Private Assert As New Rubberduck.AssertClass …

Read more

VBA (Excel) Initialize Entire Array without Looping

This is easy, at least if you want a 1-based, 1D or 2D variant array: Sub StuffVArr() Dim v() As Variant Dim q() As Variant v = Evaluate(“=IF(ISERROR(A1:K1), 13, 13)”) q = Evaluate(“=IF(ISERROR(A1:G48), 13, 13)”) End Sub Byte arrays also aren’t too bad: Private Declare Sub FillMemory Lib “kernel32” Alias “RtlFillMemory” _ (dest As Any, …

Read more

How to break a long string into multiple lines

You cannot use the VB line-continuation character inside of a string. SqlQueryString = “Insert into Employee values(” & txtEmployeeNo.Value & _ “‘,'” & txtContractStartDate.Value & _ “‘,'” & txtSeatNo.Value & _ “‘,'” & txtFloor.Value & “‘,'” & txtLeaves.Value & “‘)”

Password hash function for Excel VBA

Here’s a module for calculating SHA1 hashes that is usable for Excel formulas eg. ‘=SHA1HASH(“test”)’. To use it, make a new module called ‘module_sha1’ and copy and paste it all in. This is based on some VBA code from http://vb.wikia.com/wiki/SHA-1.bas, with changes to support passing it a string, and executable from formulas in Excel cells. …

Read more

Run Excel Macro from Outside Excel Using VBScript From Command Line

Ok, it’s actually simple. Assuming that your macro is in a module,not in one of the sheets, you use: objExcel.Application.Run “test.xls!dog” ‘notice the format of ‘workbook name’!macro For a filename with spaces, encase the filename with quotes. If you’ve placed the macro under a sheet, say sheet1, just assume sheet1 owns the function, which it …

Read more

How to add parameters to an external data query in Excel which can’t be displayed graphically?

Excel’s interface for SQL Server queries will not let you have a custom parameters.  A way around this is to create a generic Microsoft Query, then add parameters, then paste your parametorized query in the connection’s properties.  Here are the detailed steps for Excel 2010: Open Excel Goto Data tab From the From Other Sources button choose …

Read more

Quicker way to get all unique values of a column in VBA?

Use Excel’s AdvancedFilter function to do this. Using Excels inbuilt C++ is the fastest way with smaller datasets, using the dictionary is faster for larger datasets. For example: Copy values in Column A and insert the unique values in column B: Range(“A1:A6”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(“B1”), Unique:=True It works with multiple columns too: Range(“A1:B4”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(“D1:E1”), Unique:=True …

Read more