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
If you're running Vista, is it also safe to assume you're running Excel 2007?
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: Add Text To Formula
="Average Per Month: "&AVERAGE(B324:M324)
Added by admin
Custom Format the cells as "Average Per Month: " 0.00
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
Sheet1.Rows(1).EntireRow.Delete
End If
If Application.WorksheetFunction.CountA(Sheet1.Columns("A")) = 0 Then
Sheet1.Columns("A").EntireColumn.Delete
End IfRe: Identify Cause Of Long Running Function
Do the operation in one go?
With Me
Union( _
.Columns("BE"), _
.Columns("AT"), _
.Columns("AK"), _
.Columns("Y"), _
.Columns("O"), _
.Columns("K"), _
.Columns("H") _
).EntireColumn.Delete
End WithRe: Close Another Workbook On Workbook_open And Continue
ADDED BY ADMIN
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:
Private Sub Workbook_Open()
Call ShowAllSheets
'Hide the master workbook
Workbooks("Forcemacroenablemaster.xls").Windows(1).Visible = False
MsgBox "Test"
Call ValidSetup
frmCtrlPnl.Show
'Close it after the code has finished executing
Workbooks("Forcemacroenablemaster.xls").Close False
End SubRe: Naming Workbook Next In Series
Public Function lFreeFileNumber(ByVal sPath As String, ByVal sPrefix As String, ByVal sSuffix As String) As Long
'Returns the first non-zero number in a set of numbered files.
'Example: Assume that
' sPath = "C:\"
' sPrefix = "Book"
' sSuffix = ".xls"
'If folder C:\ contains files named from Book1.xls to Book7.xls, the function returns 8
'If a number is missing in the sequence, for instance Book4.xls doesn't exist, the function returns 4
Dim lCounter As Long
Dim bFound As Boolean
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
Do
lCounter = 1 + lCounter
bFound = Len(Dir(sPath & sPrefix & CStr(lCounter) & sSuffix))
Loop While bFound
lFreeFileNumber = lCounter
End FunctionOn 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: Exponentiation (Unary) With Negative Numbers
Because (-2)*(-2) really does equal 4.
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.
QuoteThat 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.
QuoteI 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
Sub PushValue(ByVal i As Integer, ByVal s As String, ByRef a() As Variant)
ReDim Preserve a(i)
a(i) = s
End Sub
Sub Foo()
Dim a() As Variant
Dim i As Long
PushValue 0, "Value0", a
PushValue 1, "Value1", a
For i = LBound(a) To UBound(a)
Debug.Print a(i)
Next i
End SubBut 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: Internet Explorer Real Time Dom Viewer
I love my FireBug, but this is pretty cool for the browser-impaired schmucks still using IE 
Re: Macro Run Faster In 2000 Than 2003
Perhaps post the code?
Re: If File Not Exist
The closing parenthesis should come after "Filename". Instead I would use
If Len(Dir("E:\Intag\" & Filename)) Then
'...
End IfRe: 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: Breakdown Number Of Part Matches
Oh, don't get me wrong, I'm still a geek when I get home as much as I am at work. Just different languages, different projects.
Re: Calculation Values Of True And False
Quote from Dave HawleyNope. 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.
