Which is Faster?

[garyj]

Hello Excel Wizards...

I am basically writing a database in Excel using the Filter and Sort features, which operate like db queries. Seems better than Power Query.
One of my features (it is for a dispatch company) is to create a 2 week calendar on a sheet, which uses the [Bus Out date-time] and [Bus Back date-time] to place a line across the calendar representing that the bus is on a trip. My formulas use the various information to find the start and end position of each line: so basically, the Top, Left, and Width of each line, and, if it is longer than the end of the week, the same information for the next week. To accomplish this I have taken a multicolumn filter from the "Bookings" table, using =SORT(FILTER(BusMapData,(F1<=MyTimeIn)*(F2>MyTimeOut)*(MyCancel=0)),4). BusMapData is the name of a 6 column range in Bookings. F1 stores the first day of the calendar at 12:00:00 AM. F2 stores the day after the last day of the calendar at 12:00:00 AM (one second after the calendar ends).
What I am wondering is which of the following methods would be faster in getting the data and formulating it to get the info for .Left, .Top, and .Width for each bus out on a trip:

1. To use VBA code to get the data, to prepare it using formulas, to create ActiveX labels, and to store those values "With MyLbl", OR
2. To add columns after the last column of the spilled range, and use these columns to figure out the same data, and then use VBA code to get this data, create the ActiveX labels, and to store them.

My guess is that the second option is faster.
If so, I have a second question.
Does anyone know how to reference the cell for one column of a multicolumn spill without losing the spill? In a single column spill starting at A5, typing ="A5#" will show the whole column, where "A5" will only reference the one cell. In a multicolumn spill A5:C5 (regardless of length) how would I get only column B? See snippet to explain, I hope you can follow all my arrows

Screenshot.jpg

Since my rows will be short, the first question is rather moot. But I would like to know out of curiosity.

[jolivanes]

There are multiple timers on these sites. Here are a couple.

Dim t
t = Timer
    '<---- All your code here
MsgBox "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."

Or

Dim Tm As Double
Tm = Timer
    Your code here
MsgBox Format((Timer - Tm) / 60, "0.00") & " sec."

Never heard of a "spill" in excel.

[Aussiebear]

@Jolivanes. Spill is a new function within Office 365

[jolivanes]

OK, thanks.
I still use the "antique" versions.
Googled it and found what it does.
Thanks again

[Aflatoon]

For a multicolumn spilled array, use INDEX to return specific rows/columns/cells.

I would strongly suggest not using activex controls at all if they can be avoided.

[garyj]

Thanks for this, jolivanes!

And yes, these new Excel tools take the spreadsheet up a whole new level. I liked the ease in programming of Power Query, but I prefer structure of Dynamic table queries much more. I think it makes database programming less appealing.

[garyj]

For a multicolumn spilled array, use INDEX to return specific rows/columns/cells.

I would strongly suggest not using activex controls at all if they can be avoided.

Sweet! This will come in handy. Thanks for this tip, Aflatoon.


By the way, I did read that once before, to not use activex controls. I am guessing they can cause extra hardship. The reason I chose to use them is twofold...

1. I want them clickable, but not selectable by a user. It shouldn't matter since code will reset next time, but it seems like poor programming to me.
2. I was not able to find a way to trigger an event when clicking a shape. But am open to ideas.

[garyj]

In case anyone wonders, I did do a test. I loaded my sheet with only 15 lines of data, and then ran the code on that. The difference would be larger if the data was longer.

Using VBA to get the values, do the math and store in variables, and then use those variables to draw the shapes took .007292 sec.
Accomplishing the same thing by making a dynamic table do the math so that VBA only has to take the result to draw the shapes took .006250 sec.

I suppose if I skipped the step of the variable in the first round, and took the result of the math to draw, it might have been close, but that method isn't as clean and easy to follow.