Posts by Fencliff

    Re: Identify Cause Of Long Running Function

    For a moment there I was hoping I could make some clever quip about culprit found, and welcome you to your worst nightmare.

    Without posting the workbook (could you obfuscate the data?) I'm afraid there's very little I can do to help.

    Re: Text And Date/time In A Single Cell

    The following works for me:

    ="Last Updated: " & TEXT(NOW();"mm/dd/yyyy hh:mm") & " (GMT+2)"

    Added by admin

    Personally I would Enter NOW() into the needed Cell and Custom Format like
    "Last Updated:" mm/dd/yyyy h:mm "GMT +2"

    This way the cell houses a true date rather than text

    Re: Identify Cause Of Long Running Function

    When you're testing, do you uncomment the Calculation = xlCalculationManual line? When you delete a bunch of columns, most formulas will recalculate.

    What kind of PC are you running on? Could you send the file, so we could see whether the issue is with your techical setup.

    Re: Identify Blank Rows And Columns

    There are a hundred ways to do this. Here's one:

    If Application.WorksheetFunction.CountA(Sheet1.Rows(1)) = 0 Then
        End If
        If Application.WorksheetFunction.CountA(Sheet1.Columns("A")) = 0 Then
        End If

    Re: Close Another Workbook On Workbook_open And Continue

    To update links with any prompt, see Update Links

    Interesting, I've never noticed that behavior as well, but it seems that the execution of the event code in Forcemacroenable.xls is controlled by Forcemacroenablemaster.xls, and when the workbook closes, the execution ceases.

    As a workaround, you could simply hide the master workbook, and close it after execution is finished:

    Re: Naming Workbook Next In Series

    On the rest, you'll have to give a little more detail to get help.

    Re: Solve Runtime Error 13 In Provided Macro

    Wow, just wow. That code is just a huge WTF.

    The fundamental problem here is that we are trying to parse a RTF file. More immediate problem with it is, that the code cannot handle the RTF formatting strings correctly, at least in the sample file.

    The best way to fix this would be to modify the code to convert the RTF into plain text and parse the text file instead. Alternatively the code could be modified to parse the input RTF correctly, but there is no guarantee those changes would work to any number of input files.

    However, as you have said you are not familiar with VBA. If I were you I would contact the vendor's technical support and ask them to correct the issues.

    Or perhaps some good soul here has the time to fix the macro for you, but it's quite a lot to ask from anybody

    Re: Populate Array For Later Use In Macro Code

    Well, it's kind of hard to actually advise without further knowing the architecture of your program.

    However, using accessor methods to a fixed, global array is not good code reuse, and it's not really refactoring any functionality either. It's just displacing access to a public variable behind a procedure call, and as such obfuscating the fact that you are modifying a global object.


    That would work if i had everything in one procedure which is what i'm trying to get away from...

    Well, I am assuming you have an entry-point or points, where you can declare the array. If you want to extract functionality into separate procedures, you cannot really escape the two options: passing the array along wherever necessary, or using a global variable. I would personally use the former, as even though it doesn't seem elegant, it is the "correct" form to do this.


    I Can't redim the array to an expected size because that is an unknown factor.

    In general VBA procedures tend to run top-down, and I can't see any conceivable circumstance where you would not know a ballpark estimate on how many indexes your array needs. It is still vastly cheaper to overallocate your array, and shave off the unused indexes when the values have been populated.

    I don't mean to seem like I think I know better than you, of course you are the one with the intimate knowledge of the application structure. However to me it certainly seems your design is not well thought out, and while I'm sure you are improving the code quality from the original monster proc, it still leaves a lot to be improved.

    Perhaps sit down with a pen and paper and try to streamline your design?

    Re: Populate Array And Get Data Back

    Maybe I'm missing something, but you could just pass your array to the procedure ByRef

    But all in all, I don't really see the point of what you're doing here. Why not ReDim the array to expected size beforehand? With larger loops the constant ReDim Preserve is going to be really expensive.

    Without seeing the big picture, I think if I inherited your refactored code, I would factor it again myself. If you are capable of seeing where OO patterns could make the code more readable, use them.

    Re: Password Protection Fails

    In short, no.

    The most common workaround to this is to set all worksheets except one to VeryHidden upon saving the file, and only unhide the sheets if the user inputs a correct password upon startup.

    The remaining visible sheet should contain some sort of instructions to enable macros.

    Re: Calculation Values Of True And False

    Quote from Dave Hawley

    Nope. True can be any value above or below zero

    Semantics. Any value <> 0 evaluates to True, but True always evaluates to one in Excel formulae. I believe the latter is discussed ITT.

    Re: Limit Number Of Text Characters In A Cell And Prevent Additional Entries After Limit

    I'm afraid it's not possible to validate a cell while the user is typing in it, i.e. it's impossible to prevent the user typing more than x characters. The validation only kicks in when the user tries to enter the value to the cell.