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

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 & “‘)”

What does mean?

Yes, it means “not equal”, either less than or greater than. e.g If x <> y Then can be read as if x is less than y or x is greater than y then The logical outcome being “If x is anything except equal to y”

How does one decompile and recompile a database application?

To Decompile an Access database you’ll need to create a shortcut with the following elements: Path to the MS Access Executable (MSACESS.exe) Path to the database you would like to decompile The /decompile flag All together, then, the shortcut would look something like the following: “C:\Program Files\Microsoft Office\Office\MSACCESS.EXE” “C:\users\tim\documents\Mydatabase.mdb” /decompile Obviously, the paths will be …

Read more

Detect merged cells in VBA Excel with MergeArea

There are several helpful bits of code for this. Place your cursor in a merged cell and ask these questions in the Immidiate Window: Is the activecell a merged cell? ? Activecell.Mergecells True How many cells are merged? ? Activecell.MergeArea.Cells.Count 2 How many columns are merged? ? Activecell.MergeArea.Columns.Count 2 How many rows are merged? ? …

Read more

VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

When the workbook first opens, execute this code: alertTime = Now + TimeValue(“00:02:00”) Application.OnTime alertTime, “EventMacro” Then just have a macro in the workbook called “EventMacro” that will repeat it. Public Sub EventMacro() ‘… Execute your actions here’ alertTime = Now + TimeValue(“00:02:00”) Application.OnTime alertTime, “EventMacro” End Sub

How to Pretty print VBA code?

You can use Notepad++ to accomplish this in three ways. Just so you know, Notepad++ is a more advanced version of Notepad, which supports syntax highlighting of different code files “out of the box” – Visual Basic included! Download & install it, fire it up, and load up your VBA code. You should automatically see …

Read more