Posts by Fencliff

Re: VBA Code Line Number Writer

MZ-Tools does this within VBE, and so much more.

http://www.mztools.com/v3/download.aspx

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:

Sub macrotest_speed_up()


    Dim suma    As Double
    Dim data()  As Variant
    Dim i       As Long
    Dim j       As Long
    
    data = WorksheetFunction.Transpose(ActiveSheet.Range("namedefinedformydata"))
    
    For i = LBound(data) To UBound(data)
        For j = i + 1 To UBound(data)
            suma = Abs(data(i) - data(j)) + suma
        Next j
    Next i
    
    ActiveSheet.Range("f1").Offset(3).Value = (suma * 2)
    
    Erase data()
    
End Sub

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.

Re: Send Dos Commands

My geek-fu is strong. Resistance is futile.

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

Shell("C:\WINNT\System32\net.exe")

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

Shell("dir")

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:

Sub ProducesSelectCaseOverFlowWhenTooManyRowsSelected()
    
    Select Case 2
    
        Case Selection.Rows.Count
            'Do stuff here
            
        Case Selection.Columns.Count
            'Do stuff here
            
        Case Selection.Areas.Count
            'Do stuff here
            
    End Select


End Sub

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: Activate Webpage Button Where 2 Have Same Name

I don't know. Why don't you try and tell me instead?

Re: Vba Replace Funtion Wildcard Char

RegExp is the tool for you here, check out http://www.regular-expressions.info. 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

Hi,

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

Hi,

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:

Sub Details()
    
    Dim Doc                 As HTMLDocument
     
    Dim IE As SHDocVw.InternetExplorer
    Set IE = New SHDocVw.InternetExplorer
     
    IE.navigate "http://58.27.200.20/board/"
    IE.Visible = True
     
    Do While IE.readyState <> READYSTATE_COMPLETE
        Application.Wait Now + TimeValue("0:00:01")
    Loop
     
    Set Doc = IE.document
     
    Debug.Print "Frames:", Doc.frames.Length
    Debug.Print "Source:"
    Debug.Print "------------------"
    Debug.Print Doc.body.innerHTML
    
End Sub

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: Activate Webpage Button Where 2 Have Same Name

Hi,

Your post above is empty - I think you might have exceeded the maximum length for your post.

Re: Return Nth Value From Single Column Range

That would be nice, we could have =DOSOMESTUFF() with 297 different overloaded argument configurations. I'll be sure to send that proposal to Microsoft to be included in Excel 13

Re: Return Nth Value From Single Column Range

Thanks!

Re: Return Nth Value From Single Column Range

Seems to me you are somewhat bent

Why try to force choose to do:
=CHOOSE(3, DATA)

When you can do:
=INDEX(DATA,3)

?

On a side note: how the heck do I use the Formula formatting Dave used above?

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.