Posts by Fencliff

    Re: Pass Array Elements To Private UserForm Module

    All right, I have no idea of the cause of this behaviour, and I don't have time to look into it further, but I did figure out a workaround: Instead of assigning to a property, pass the array to a method. Not as pretty, but works.


    Private msFormString() As String
    Public Sub PassFormString(s() As String)
         msFormString = s
    End Sub


    Sub PassString(frmString As FString, sString() As String)
        frmString.PassFormString sString
    End Sub

    Re: Pass Array Elements To Private UserForm Module

    What Dave was saying was to declare the public variable on top of a public _module_, not a class module (userform).

    However, there has to be a better way. Has to be. I WANT TO BELIEVE :)

    I'll take a look at this and will get back to you if I figure out anything. However I'm reproducing the same error, and the case seems to be that the only procedure that can assign to the property is the one that instantiates the form. I already tried declaring the form instance on a module-level, so issue is not that the form instance goes out of scope.


    Re: Fastest Lookup Method

    Good question.

    I am not entirely sure how the VBA Collection object is implemented, but I can make an educated guess based on the implementation of VB6 Collection object. VBA and VB6 share are lot of the codebase, but not in all cases, so I cannot say for certain this is the case here. Please consider any statements after this based on the assumption that they are.

    The collection object is essentially implemented as two structures, a linked list that holds the actual data and a pointer to the next item in the list, and a hash table, that contains the key and a pointer to the value. The upside of this implementation is that looking for a value by its key is fast: search for the key in the hash table [O(1)] and get the value from the pointer [not sure, but would guess at worst O(n log n)]. The downside is that locating a value by its index is can at worst be a O(2n) operation, because we need to enumerate the values in the linked list and traverse the list until we find the value.

    With Scripting.Dictionary, that is basically just a hash table implementation, we have a little less versatility, because we can't refer to an item by it's index, but any possible search is also quite a bit faster [O(1)]. Insertion and deletion for a dictionary are also faster.

    I guess what tool to use boils down to how much speed you actually *need*. For most applications a dictionary or a collection are both all right, with dictionary providing some additional useful methods like Exists, that's useful in searching through the dictionary, and the possibility for case-insensitive keying.

    I personally prefer the dictionary. You can also instantiate with late binding. The correct syntax there is CreateObject("Scripting.Dictionary")

    Hope this helps.


    Out of curiosity I actually tested this, and here are results for 300 000 lookups for 30 key-value pairs, evenly distributed for all keys. The results are the average of 10 runs:

    VLOOKUP: 3,453 sec
    Collection: 1,492 sec
    Dictionary: 0,302 sec

    Here we can clearly see that dictionary is actually over 1100% faster than Vlookup and almost 500% faster than Collection. I didn't test insertion/deletion speeds, because .. well ... with this kind of speed, who gives a damn about insertion.

    Re: Fastest Lookup Method

    Well, as a very short and roundabout answer: It may be possible, but not feasible to create a branch table in VBA.

    Branch tables rely entirely on pointer arithmetic and having an intimate access to the register. I have no doubt you could implement one using memory management APIs, and if you do, please do post the code, but the point is moot. The marshalling overhead would probably negate any benefits gained.

    For a simple (albeit not very well balanced) hash table implementation you should look into Microsoft Scripting Dictionary object. You need to add a reference to Microsoft Scripting Runtime into your project.

    The overhead is minimized into instantiating the object, and typical search time with dictionary is O(1).

    Re: show folders in listbox

    This should do it for you. Just place this in the userform module.

    And you can call it by

    DisplayFoldersInListBox "C:\", Me.ListBox1

    Where you should replace "C:\" with the actual directory, and Me.ListBox1 with the listbox you want to populate.

    Re: Auto Updating Cell In Relation To Input Of Another Cell

    Here ya go. Right-click the worksheet tab of the sheet you want to use this on, and paste the following code into the opening window:

    Please take a moment to read through the comments in the code to understand what is happening.

    Re: Text And Formatted Date & Time In Cell

    You're probably right. I think it's just that judging by the frequency of my brain farts these days, you'd have to be really lucky to catch me in a moment when I don't appear to be a drooling idiot :)

    Re: Move Mid Text In Cell To Front. Reverse

    To continue the off-topic a little bit: Regular expressions ("grepping") are one of the most important tools in the web/scripting world, and every programmer should know them, use them and love them. Unfortunately the VBScript RegExp flavor is a little retarded compared to the engines in Ruby and Perl, but still positively invaluable tool for certain tasks.

    To get started with the syntax, check out .

    For the most common jobs, you might just find a good and tested expression at

    When you start using them, there is nothing like having The Regulator around:…or/tabid/185/Default.aspx


    Ooh, while looking for the Regulator website I came across Regulazy, made by the same guy: . Looks like a fun little tool. Does anybody have any previous experience with it?

    Re: Text And Date/time In A Single Cell

    Whoopsie daisy. Just remembered that the format string used by TEXT is locale sensitive. If I were using the local Danish settings here in Denmark, that wouldn't work, since the signifier for year is å, not y.

    If this is a concern, you could use
    =MONTH(NOW())&"/"&DAY(NOW())&"/"&YEAR(NOW())&" "&HOUR(NOW())&":"&MINUTE(NOW())&" (GMT+2)"