Posts by Fencliff

Re: As400 Repeated Copy Macro loop

I'm afraid we're going to need a little bit more information than that.

Re: Pass Array Elements To Private UserForm Module

Good to know. I tried passing it as MSForms.UserForm but that didn't work.

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.

UserForm:

Private msFormString() As String


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

Caller

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.

Interesting.

Re: Undo Causes Copy & Paste. Try And Guess Why

Well, oopsie daisy.

Perhaps there is an event code running that keeps assigning a procedure to .OnUndo ?

Re: Undo Causes Copy & Paste. Try And Guess Why

Do a global search through the macro code for ".Onkey". Perhaps someone has hooked the key combination Ctrl + Z to perform the copy paste action.

Re: V4 Import Text File, Field Format

Wow, I didn't know anybody uses Excel 4 anymore

Sorry, can't help you there, this is all a bit before my time.

Re: V4 Import Text File, Field Format

Is there a reason you're using an Excel 4 macro?

Re: Email Without Outlook

Check out this article from Ron de Bruin, using CDO has worked for me on many occasions:

http://www.rondebruin.nl/cdo.htm

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.

Edit:
-----------------------------

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.

Private Sub DisplayFoldersInListBox(ByVal strRootFolder As String, ByRef lbxDisplay As MSForms.ListBox)


    Dim fso         As Object
    Dim fsoRoot     As Object
    Dim fsoFolder   As Object
    
    'Make sure that root folder contains trailing backslash
    If Right$(strRootFolder, 1) <> "\" Then strRootFolder = strRootFolder & "\"
    
    'Get reference to the FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'Get the root folder
    Set fsoRoot = fso.GetFolder(strRootFolder)
    
    'Clear the listbox
    lbxDisplay.Clear
    
    'Populate the listbox with subfolders of Root
    For Each fsoFolder In fsoRoot.SubFolders
        lbxDisplay.AddItem fsoFolder.Name
    Next fsoFolder


    'Clean up
    Set fsoRoot = Nothing
    Set fso = Nothing
    
End Sub

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: Scramble/Randomize Column Values

Quote from FRIEL

LET ME KNOW HOW IT GOES

Why on earth are you yelling?

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:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rngIntersect    As Range
    Dim rngList         As Range
    Dim rngCell         As Range
    Dim vCompare        As Variant
    
    'Validate that we are only editing one cell. Otherwise checking
    'Target.Value will throw an error
    If Target.Cells.Count = 1 Then
    
        'No reason to process further if we are not marking x. It is
        'cheaper to check this than to do a range intersect
        If LCase(Target.Value) = "x" Then
    
            'Check whether we are adding to column B...
            On Error Resume Next
            Set rngIntersect = Application.Intersect(Target, Me.Range("B:B"))
            On Error GoTo 0
            
            '...if Target is in column B
            If Not rngIntersect Is Nothing Then
                
                'Get list of all non-empty cells in column A
                Set rngList = Me.Range("A:A").SpecialCells(xlCellTypeConstants)
                
                'Get the value in column A where we have added the x
                vCompare = Target.Offset(0, -1).Value
                
                Application.EnableEvents = False
                Application.ScreenUpdating = False
                
                'Loop through all cells in Column A
                For Each rngCell In rngList
                    
                    'If the cell value matches comparison value
                    If rngCell.Value = vCompare Then
                        'Enter x to column B
                        rngCell.Offset(0, 1).Value = "x"
                    End If
                Next rngCell
                
                Application.ScreenUpdating = True
                Application.EnableEvents = True
                
            End If
        End If
    End If
End Sub

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: Hiding Rows And Columns With A Button

That feature is called Group and Outline, and it can be found (in Excel 2003) under Data -> Group and outline...

Re: Text And Formatted Date & Time In Cell

Dave is absolutely right, that's the obviously correct way to do this. Sometimes I am an idiot v:)v

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 http://www.regular-expressions.info/ .

For the most common jobs, you might just find a good and tested expression at http://regexlib.com/

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

Edit:

Ooh, while looking for the Regulator website I came across Regulazy, made by the same guy: http://tools.osherove.com/Default.aspx?tabid=182 . Looks like a fun little tool. Does anybody have any previous experience with it?

Re: Text And Date/time In A Single Cell

Not an error per se. Just happened to be using different locale settings than you.

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)"