Just reiterating what has already been said: Option Explicit Sub Macro1() ‘turn off as much background processes as possible With Excel.Application .ScreenUpdating = False .Calculation = Excel.xlCalculationManual .EnableEvents = False End With Dim historyWks As Excel.Worksheet Dim inputWks As Excel.Worksheet Dim nextRow As Long Dim oCol As Long Dim myCopy As Excel.Range Dim myTest As … Read more
Shortly put (to answer the answer as titled): No. VBA’s Dir function simply does not work with paths beyond 260 characters. Long version: http://msdn.microsoft.com/en-us/library/aa365247(VS.85).aspx#maximum_path_length (then Ctrl+F and search for “260”) Maximum Path Length Limitation In the Windows API (with some exceptions discussed in the following paragraphs), the maximum length for a path is MAX_PATH, which … Read more
If your goal is to perform replacements in the whole documents (it looks so from the code, but it is not explicit), I would suggest you use Document.Range instead of the Selection object. Using Document.Range will make sure everything is replaced, even inside tables. Also, it is more transparent to the user, as the cursor … Read more
Here is my solution, it works consistently and you don’t need to manually copy the sheets and code across to a blank workbook. I’ve tested this method on several corrupted workbooks that were giving me the “Automation error – Catastrophic failure” error upon launch. NOTE: Original corrupted file was saved as .xlsm Open a blank … Read more
Something like this using Union to glue together your range Please note that For each loops are quicker than a For i = 1 to x approach You may well be able to use SpecialCells to determine your new range instantly (e.g. any blanks, any errors, any formulae, etc) Sub Test() Dim rng1 As Range … Read more
I would use a simple VBA-Collection and add items with key. The key would be the item itself and because there can’t be duplicit keys the collection will contain unique values. Note: Because adding duplicit key to collection raises error wrap the call to collection-add into a on-error-resume-next. The function GetUniqueValues has source-range-values as parameter … Read more
Calling regasm without administrative rights for COM interop I think it should be possible to use RegistrationServices.RegisterAssembly and RegOverridePredefKey APIs together to implement automatic registration under the HKCU hive, in a UAC-friendly way. I’ve posted a more detailed answer here.
You should use this pattern: Sub my_Procedure() MsgBox “hello world” Call test ‘ for starting timer again End Sub Sub test() Application.OnTime Now + TimeValue(“00:10:00”), “my_Procedure” End Sub
Hiding the active workbook is possible with ActiveWorkbook.Windows(1).Visible = False You may need to replace ActiveWorkbook with an appropriate reference if the workbook in question is not the active one and/or add a loop like For i = 1 To ActiveWorkbook.Windows.Count if the workbook has multiple windows.
I think you may not be doing anything wrong, because I have experienced exactly the same behaviour. However, in order to debug your VBA, I suggest that you create a macro (via the Tools|Macro|Macros menu) that calls your script function with a test e-mail item that you create in the macro. Maybe something like this: … Read more