Posts by Fencliff

    Re: Optimize Inneficient VBA Macro Sum Code

    One thing that speeds up the code is to process the data in memory, rather than reading from the sheet:

    It still doesn't scale too well, but processes 10 000 integer entries in ~10 seconds on my PC.

    What's your data like? Small integers, floating point numbers etc? There might be some additional optimization we could base on that.

    Hi All,

    Other programming best practices aside, please keep this one in mind:

    Always program like the maintenance programmer taking over your work is a murderous psychopath who knows where you live.

    Well, I am that maintenance programmer, and I am determined to find out where you live. Earlier this week an interesting change request popped into my queue: "[tool name removed] needs upgrade" said the request title. Not too bad, you think? Just take a tool and add whatever features required? Why are you so angry?

    I think in some cases a few numbers can tell more than a thousand words:

    85.022 Code lines

    7.421 Code lines in single form constructor

    657 On Error Resume Next statements

    359 Public variables

    242 Comment lines (most of which is commented out code)

    52 Forms

    17 Procedures (not counting form/control event handlers)

    1 Public variable called "sWhatever" - type Long

    1 code module (!)

    1 home-rolled ActiveX control with no source code available that does something, but not sure what.

    1 too many CopyMemory API calls for my taste.

    1 man to refactor this whole abortion and convert it to .NET, with

    0 patience for bad code.

    So just remember, don't ever do this :)

    Re: Send Dos Commands

    Well, this is not really true.

    Shell <program name> executes a program, not a command line command. So


    executes that program, but it won't execute any command line instructions. Try


    and see what happens.

    Why you can launch net.exe without specifying the full path is because the containing folder %winpath%\system32\ is defined in your PATH environment variable. Same goes for all other Windows command line tools.

    However to send a command line instruction, you need to run cmd.exe with your instructions as parameter /c. For instance the following will create a text file contents.txt with the output of folder C: directory listings:

    Dim cmd As String 
    Dim ret As Double
    cmd =  "cmd.exe /c dir C: > C:\contents.txt"
    ret = Shell(cmd, vbHide)

    The cmd.exe will close itself after executing the command, so reading the returned pId will not really do you any good, so you could just as well disgard the return value

    Re: Differences &amp; Incompatibilities Across Versions

    That's not a safe assumption. I am no expert on the matter, because my development for Excel 2007 has been fairly minimal, but at least some Chart-manipulation code in our legacy VBA app broke when our payroll department upgraded to Excel 2007.

    The ticket didn't get assigned to me, so I don't know details. However it seems it's not 100% backwards-compatible.

    Also try to use Sheet.Rows.Count and Sheet.Columns.Count instead of hard coded values 65536 and 256, because Excel 2007 supports larger worksheets.

    Any XML-manipulation code will have likely changed between 2003 and 2007 as well, because the Excel 2007 document type is now native XML, and doesn't require binary transformation.

    Creating custom menus will need to be handled differently for the Excel 2007 RibbonX interface.

    Here's just some things you should watch out for.

    Hi All,

    Found an interesting integer overflow condition today, thought I would share in case anybody else runs into same issue.

    The condition arises when you use a literal int as the test expression in a Select Case statement:

    When the Selection.Rows.Count is larger than maximum 16-bit integer value 32,767, the "Case Selection.Rows.Count" line throws an overflow error.

    I don't know how the compilation of Select Case constructs to p-code is implemented, but obviously each statement is evaluated in the (assumed) data type of the test expression.

    The fix is easy: use a 32-bit integer (Long) constant as the test expression

    Sub DoesNotOverFlow()
        Const lVALID_REGION_COUNT As Long = 2
        Select Case lVALID_REGION_COUNT
            Case Selection.Rows.Count

    Re: Activate Webpage Button Where 2 Have Same Name

    Quote from Dave Hawley

    Crystal ball playing up again, Fen :)

    All I see in my crystal ball is "401 Unauthorized" :(

    I've tried shaking it, polishing it, turning it on and off, banging it on the top, but nothing. Could somebody pls help?

    Re: Vba Replace Funtion Wildcard Char

    RegExp is the tool for you here, check out I'm just about to leave work, so don't have time to post more details. I'm sure someone else here can help.

    Edit: actually, you don't need grep here:

    s = "SELECT A, B, C, D, E FROM TABLE WHERE ID = '123123123';"
        s = "SELECT * " & Right$(s, Len(s) - InStr(1, s, " FROM"))
        Debug.Print s '--> SELECT * FROM TABLE WHERE ID = '123123123';

    Re: Find Whole Cell Value, Not Part

    You can restore the default settings (or actually record any settings you want to) by doing a dummy search with the values you want to "leave behind". The SearchFormat settings can be accessed through Application.FindFormat.

    Unfortunately the general settings cannot be read. At one point I had a procedure that ran an elaborate set of tests that would determine what settings the user had before, but I've lost that long ago somewhere, and it's not really worth it.

    Re: Blank Or Grey Area In The Left Of The Screen


    I used to have the same problem on my laptop on Excel 2003 Professional. As far as I could tell, it's (like Dave suggested) an issue with a lack of RAM. When you're running a memory-intensive macro, your system starts paging memory to your hard disk, which is orders of magnitude slower than reading from RAM, and often results in paging faults. In this case the pixels on your screen don't get drawn correctly.

    There really isn't a fix for the issue, except to try to consider your program, and how you can deallocate memory as you go to keep the stack at minimal size.

    Re: Activate Webpage Button Where 2 Have Same Name


    The reason why you keep getting the error is that the buttons aren't actually part of the main document displayed in the window, but an embedded IFrame object.

    You can try to change your code so that it refers to that embedded document:

    Set Doc = IE.document.frames(0).document

    However I can't see the contents of that embedded frame, because I don't have access to your system. This frame might contain more frames, which may contains more frames etc, in which case you might need to traverse the document.frames collection deeper:

    Set Doc = IE.document.frames(0).document.frames(n).document.frames '...

    Godspeed and good luck, unfortunately I can't help you any further.

    Re: Activate Webpage Button Where 2 Have Same Name

    Hmmm, could you do run the following code, and post the text from your Immediate Window:

    Re: Activate Webpage Button Where 2 Have Same Name

    I don't actually quite understand what you're saying - do you mean that when you click "view source..." in your browser, it doesn't show you the same DOM (document object model) as IE Developer Toolbar? If this is the case, could you post the HTML you see when you click "view source.."? I'm thinking the website might be using frames, and would like to verify that.

    Re: Find Whole Cell Value, Not Part

    Yeah, I'm aware of that, and do a record-restore every time I use Find in one of my public-facing apps. However I didn't mention it, because I figured it might have been a little out of the scope of this discussion.