Posts by Fencliff

    Re: Or Operator

    Unfortunately you cannot use the OR operator in this scenario. OR is a logical operator, and can be used to test the validity of at least one statement out of many. For Example

    If x = 1 Or x = 10 Or x = 20 Then
        'If x is 1, 10 or 20, the logical test evaluates to true
    End If

    Regarding how you are initially using the SumIf function, I am sure someone will suggest a DSUPERSUMIF formula to do this, but there is nothing wrong with your initial syntax. If you want to make it more readable, you could do for instance:

    Dim rngIf   As Range
        Dim rngSum  As Range
        Set rngIf = Sheet5.Range("H9:H500")
        Set rngSum = Sheet5.Range("I9:I500")
        With WorksheetFunction
            Cobbs = .SumIf(rngIf, "C/F", rngSum) + .SumIf(rngIf, "4/F", rngSum)
        End With

    Re: Run Macro


    Put the following code into the sheet module of the sheet you are using, and change the YourMacroNameHere to whatever your macro is called:

    Re: Pass Element Of User-Defined Data Type

    The problem here is your architecture. Think of user-defined type as an object (UserType) that has properties (Element1, Element2). The objects are made from the template that is your UDT, but the objects created from that template don't bear any relation to each other.

    To speed up writing the information back into a sheet, what you could do without having to rewrite your entire logic is to loop through your array "Array", writing each Element1's (or Element2's) property into a variant array, which will enable you to write it all into the sheet at once.

    It'll be far from efficient, but better than looping the values directly into the sheet. If you are open to a complete rewrite, I can give you some pointers how to actually make a sensible infrastructure.

    Re: Stop Macro Running After Specfied Time Period

    Out of curiosity, could you describe a little what your macros do? It seems a tad outlandish that any Excel based macro should run for 54 hours, or to be more exact, that any test that could run for 54 hours would be done in Excel (I've done GUI hittests that have been running for a week in a streak, or batch migrations that can execute code top-down for a couple of days, but in those cases I'm damn sure not doing it in VBA!)

    I am assuming that your application loops through some calculation or data. You could simply record the time at the the start of the calculation, and on each running of the loop test whether the maximum time has elapsed, and exit if it has.

    Re: Pass Element Of User-Defined Data Type


    Could you please explain what your application is trying to achieve and what is the design decision behind using arrays of UDT's. It seems to me that this could be achieved much better by a collection of UDT's or a class object.

    Re: Freeze Shape From Scrolling

    The reason why Excel doesn't allow floatings shapes is not because of MSFT's oversight. If you think about it, you soon will realize the following:

    1 Excel is a spreadsheet application
    2 Spreadsheets are about manipulating data
    3 When you have a floating object over a data grid, it will block the user's view of the data
    4 This is a bad idea

    Apart from that, the commandbar/toolbar approach has multiple advantages

    5 The contols are designed for this exact purpose. You can have textboxes, combo boxes, and buttons just you would on the sheet, but in addition dropdown menus, multi-level hierarchies etc.
    6 Your controls aren't tied to the sheet. You don't have to replicate your navigation elements on each sheet you want to use them on, and you don't (necessarily) have to proliferate your code either, which increases it's maintainability.
    7 Your users can use an interface already familiar to them, enhancing the user-adaptability or your application
    8 Uniformity to Excel's native UI gives your application a more professional feel - you can use the hundreds of pre-existing button icons, or make your own. The buttons respond to user hovering over and clicking on them, resulting in immediate feedback, and an application that seems more responsive.

    And no, you don't want to look into the winapi solution. It will be messy, resource expensive, and involve hella-complicated code (compared to vanilla VBA, at least).

    Re: Freeze Shape From Scrolling

    Quote from CATman

    Maybe I should open up this question by asking, "What is a good method to provide a user with buttons that can be used to launch various macro's" besides using hot keys.

    The question posed that way is much easier to answer: A custom toolbar. Check out and google around with words Excel VBA Toolbar and specify your searches as needed to find out how.

    Re: Freeze Shape From Scrolling

    Like AAE said, this is not really possible. A hack could be made with windows API's to change the location of the shape relative to the visible portion of the screen, but that is really not anything you would want to do.

    In your place I would consider using a toolbar menu with a single button. It can be set to float on top of your sheet just like you would want your shape to do, and if you don't want the user to move it around, that can be done using the Protection property. It won't look the same, but will fulfill the exact same function as a shape.

    Re: Extract Dimensions From Single Cell Entry

    Quote from p45cal

    This line

    strVal = Replace(strVal, ".", ",")

    caused a few problems in the English locale of Excel on my machine. (Or is that what makes it the real man's way!?:smile: )

    Ah, well, the real man does what real man wants and screw the 80% majority or users, the most important thing is that it works for me! :)

    Yeah, should probably think a little more when posting sometimes. Usually when the case is that I (or the OP in this case) needs something done as a one-time event, i.e. format garbage data, I just write the code out in two minutes and don't really bother to harness it with a multi-locale test scenarios :P

    Thanks for pointing that out though.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from AAE

    p45cal - nice work. Do you by chance have a handy reference to the character codes? I'm looking for one.

    The Chr function codes are really just the ASCII numbers for characters, 0 to 255.

    The real man (again) writes this to Immediate window in VBE:

    Workbooks.Add: For i = 0 To 255: Range("A" & i + 1).Value = i: Range("B" & i + 1).Value = Chr(i):Next i

    ... and he remembers the meanings of 9 and 13 by heart, because they're illegible in that output. The rest can refer to for instance:

    Re: Extract Dimensions From Single Cell Entry

    I can see this question has already been answered, but to post what I already wrote, if you want to do this the real man's way with a quick and dirty, nested loop procedure, then here you go:

    Right click the sheet containing your data, choose View code, and paste the above into the opened window. Then select the cells where your measuments are (in one column), and press Alt + F8 and run the macro GetNumbersFromSelection.

    Re: Delete First 3 Characters In A String

    AAE got the right idea, but to make it a little more reliable, I would use

    =RIGHT(A1, LEN(A1)-3)

    This doesn't assume that the string is 6 characters long, but always rips out the first three characters.

    Re: Freeze Panes VBA Without Select


    Could you please post a small example how you would, using VisibleRange and GoTo, achieve the following, when used in conjuction with worksheet_activate event:

    • Freeze panes on a certain cell, that may or may not be in view when the sheet is activated
    • Ensure that the scroll position where the user left it when deactivating the sheet is preserved
    • Ensure that the cells that user had selected before deactivating the sheet are kept selected
    • Do this without a displaying a distracting twitch

    I think the list above is a reasonable set of demands in good UI design.

    Also, could you please try to explain why is it better to set the window's freezepanes status to the same point every time the user navigates to the sheet, when you could only do it once when you are running other code (whatever OP is running).

    I would really appreciate it, because I just don't get why your way is better than mine.

    Re: Get Data From Closed Workbooks


    Attached below is a version of the code that works. Please take some time to study the differences to understand what made it fail in the first place.

    Because I am obsessive compulsive when it comes to these things, I changed the variable names to include the data type prefix, declared your arguments as strings, and shuffled some other things around as well. Those were not the reason why the code didn't work though. The culprit were the lines:

    'Create the argument
        arg = "'" & path & "[" & "k217811.xls" & "]" & "Total" & "'!" & _ 
         Range(ref).Range("A1").Address(, , xlR8C2) 
         '   Execute an XLM macro
        getvalue = ExecuteExcel4Macro("arg")

    And the enhanced version:

    Re: Freeze Panes VBA Without Select


    I know my code uses select and activate, because that's the only way to do it. The code I posted was simply showing how I've chosen to work with the problem. If there is a better way, I would be very glad to learn, becaused I have a couple of apps out there that are using this method, and I don't feel very confident in it.

    The question however is completely sensible. You start programming in VBA, and you learn that you don't need to select cells to do things, and suddenly you can't figure out how the heck to freeze panes at a certain point without selecting. No false premises there, IMO.

    Hooking the freezepanes into the worksheet_activate event, while relatively reliable, would be _really_ annoying, because the screen would twich every time you activate a sheet. In the best case scenario, where you would record the previous selection, scroll status, and then toggle them back, the screen would still twitch every time you activate a sheet. With a lot of data, and especially with some shapes (such as charts) mixed in, this can look really bad, as the redrawing time can be perceivable. Or if the user has selected the entire sheet or other large amount of data, and changes sheets temporarily and goes back. On my computer selecting large filled ranges can sometimes freeze Excel for 3-10 seconds.

    Or if the user doesn't like the freezepanes and turns them off, then goes to check another sheet and coming back, bam! The old settings are back. Not to mention the fact that it is completely unnecessary to redo this at every sheet activation, once you freeze the panes while you are running other code anyway, like OP, you don't need to mess with it again until the next time you turn them off.

    The event handler I tried to test this looks like this:

    Peruse it at your will, but you should know that it needs some failguards for if the selection is not a range.

    Re: Create Object In Vba

    I'm not sure if this does exactly what you want, because I'm not clear on what you mean by binding the object to a cell. This should however insert an object to cell A1, the trick is to activate the cell before inserting the object, because the objects always get inserted to the active cell.

    I made a couple of little changes also, please read the comments in the code for details. However, by the end of this code, you will have reference to the shape containing the object, and the object itself, both of which you can use in which ever way you want to proceed.

    Re: Assign Variable To Different Data Types


    I'm not sure why your excel locks up at that point, is should work all right. However, you could try this:

    Cells(1, CLng(ColumnUsed)).EntireColumn.Copy

    To replace all of this:

    Cells(1, ColumnUsed).Select 
        Cells(1, ColumnUsed).End(xlDown).Columns.EntireColumn.Select 

    The CLng function converts the variant into a long integer, ensuring it's passed to Cells as number. For my system though, I can easily pass a variant to Cells without crashing, heck, I can even pass a string, apparently!

    And if that copy is followed by a paste (i.e. you don't access the data on the clipboard or paste into another application, instead of copying the contents to clipboard, then pasting and clearing the clipboard you could do the following to for example copy the column into the column A in Sheet2:

    Cells(1, CLng(ColumnUsed)).EntireColumn.Copy Destination:=Sheet2.Range("A1")

    And one more thing, you don't need that 26-case select case statement. You can replace the whole shebang with:

    ColumnUsed = Columns(ColumnUsed).Column

    Good luck with your project!

    Re: Freeze Panes VBA Without Select

    I think everybody in this thread have temporarily lost their ability to read.

    The OP's question was simple: Whether it is necessary to select a cell or cells (you know, using Range.Select or Application.Goto or whatever else has been suggested in this thread) before applying freezepanes.

    The OP then proceeded to acknowledge the correct answer, which is that because FreezePanes is a method of an window object, the sheet on which you want to apply it needs to be the activesheet, and it is always applied to the top and left of the selected cells.

    The OP didn't ask to freeze panes every time the worksheet is activated.

    But to actually contibute, this is how I'm doing it now:

    I use the rngPrevSelection to store the previous selection and then switch it back, because that's just good practice. Please note however that if the selection is not a range (a chart, for instance) this will return a Type Mismatch error. However, if I remember correctly if you try to turn FreezePanes = False and you have a chart selected, it will fail anyway.

    Re: Disable Hyperlink From Opening Until Confirmed

    Here's a little more fleshed out version of what I posted above, figured one day this might come in handy. This would be fairly easy to modify to for example validate user input before using a POST on a web page through a hyperlink.

    I haven't used Excel's hyperlinks that much before, so I'm sure there's some horrible bug I don't know about. Please let me know if you find any, as this is going to my code library at work.

    Anyway, what you need to do is the set the hyperlink to refer to the cell it is placed in, and use the ScreenTip property of the Hyperlink object to carry the address you really want to go to. You can either specify an external file, a location within the same workbook, or a link to an external workbook AND a location within it. The latter is achieve by separating the file reference and the location by an asterisk, like: "C:\Workbook.xls * MySheet!A1:A10". Any blank space between the asterisk and the parameters doesn't make a difference.

    The ScreenTip property can be changed by going to Edit Hyperlink... > ScreenTip... or directly in the code like ActiveCell.Hyperlinks(1).ScreenTip = "..."

    For more detailed explanation, see the code comments, which I have abused as per usual.

    Re: Disable Hyperlink From Opening Until Confirmed

    How about using the cell's own address as the hyperlink address like Dirk suggested, and load the actual address you want to follow to the hyperlink's ScreenTip property. Then you could simply do

    It is a good idea to let your user know where he or she is heading anyway, so the screentip will also fulfill that function.

    Of course as is, this would only work with simple links to external documents, but for in-document hyperlinks you could simply check if the string has character "!" and whether the text to the left of it is a valid sheet name in your workbook, and then use it as a SubAddress property instead.

    Perhaps I'll come back to this later if I have the time and try to make a catch-all solution.