Consulting

Results 1 to 8 of 8

Thread: Which is Faster?

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    113
    Location

    Which is Faster?

    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.

  2. #2
    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.

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,182
    Location
    @Jolivanes. Spill is a new function within Office 365
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

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

  5. #5
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    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.
    Be as you wish to seem

  6. #6
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    113
    Location
    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.

  7. #7
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    113
    Location
    Quote Originally Posted by Aflatoon View Post
    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.
    Last edited by garyj; 10-27-2023 at 03:39 PM.

  8. #8
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    113
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •