vba – Make Me Engineer

How to wait until ActiveWorkbook.RefreshAll finishes before executing more code

June 23, 2023 by Tarik

I had the same issue with an OLEDBConnection connection type, however DoEvents (as suggested in a prior answer) didn’t help me as my data connections had background-refresh enabled. Instead, using Wayne G. Dunn’s answer as a jumping-off point, I created the following solution, which worked: Sub Refresh_All_Data_Connections() For Each objConnection In ThisWorkbook.Connections ‘Get current background-refresh … Read more

Excel telling me my blank cells aren’t blank

June 15, 2023 by Tarik

a simple way to select and clear these blank cells to make them blank: Press ctrl + a or pre-select your range Press ctrl + f Leave find what empty and select match entire cell contents. Hit find all Press ctrl + a to select all the empty cells found Close the find dialog Press … Read more

Return background color of selected cell

June 15, 2023 by Tarik

You can use Cell.Interior.Color, I’ve used it to count the number of cells in a range that have a given background color (ie. matching my legend).

Reverse order of For Each loop

June 14, 2023 by Tarik

It’s not possible to loop backwards using the for each loop syntax. As an alternative you can use a For i = a To 1 Step -1 loop: Sub reverseForEach() Dim i As Long, rng As Range Set rng = ActiveSheet.Range(“A1:B2”) For i = rng.Cells.Count To 1 Step -1 Debug.Print rng.item(i).Address ‘ Or shorthand rng(i) … Read more

Get Name of Current VBA Function

June 13, 2023 by Tarik

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

EXCEL VBA Check if entry is empty or not ‘space’

June 13, 2023 by Tarik

A common trick is to check like this: trim(TextBox1.Value & vbnullstring) = vbnullstring this will work for spaces, empty strings, and genuine null values

What’s the difference between “end” and “exit sub” in VBA?

June 13, 2023 by Tarik

This is a bit outside the scope of your question, but to avoid any potential confusion for readers who are new to VBA: End and End Sub are not the same. They don’t perform the same task. End puts a stop to ALL code execution and you should almost always use Exit Sub (or Exit … Read more

Excel Date Conversion from yyyymmdd to mm/dd/yyyy

June 11, 2023 by Tarik

You can convert the value to a date using a formula like this, next to the cell: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Where A1 is the field you need to convert. Alternatively, you could use this code in VBA: Sub ConvertYYYYMMDDToDate() Dim c As Range For Each c In Selection.Cells c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2)) … Read more

How to name an object within a PowerPoint slide?

June 10, 2023 by Tarik

PowerPoint for Windows: Click on the object (textbox, shape, etc.) to select it. In the Drawing Tools | Format tab, click on Selection Pane in the Arrange group. From there, you’ll see names of objects. Double click (or press F2) on any name and rename it. By deselecting it, it becomes renamed. You can also … Read more

Display milliseconds in Excel

June 7, 2023 by Tarik

Right click on Cell B1 and choose Format Cells. In Custom, put the following in the text box labeled Type: [h]:mm:ss.000 To set this in code, you can do something like: Range(“A1”).NumberFormat = “[h]:mm:ss.000” That should give you what you’re looking for. NOTE: Specially formatted fields often require that the column width be wide enough … Read more