Help speeding up macro

[nachelle]

Hello brilliant people!

I need help in a major way.

I found the code for this file manager on learnexcelmacro and built a workbook around it to track file names and properties saved to a shared folder. It mostly works very well, but it is rather slow. I am looking for a way to turn off screen updating and do anything else I can to speed up the macro. I tried to use Application.ScreenUpdating = False, but cannot seem to get it right.


The combo box to select the file types to fetch also does not work. It will only fetch all the file details if the "fetch all types of files" is selected. Please, help me figure this out. It has been an intense headache for a very confused VBA newb.

[Leith Ross]

Hello nachelle,

There is a lot going on in your workbook. After a quick look a few standout. I did not see any use of arrays to manipulate worksheet data. Direct cell referencing is slow especially in large loops. Second, the file manager is using the File Scripting Object. While easy to use, it is not the best choice here. Using the Shell would be a faster and better choice for what you doing. Third, creating hyperlinks is a slow process. Unfortunately there is not real way to improve the speed on this issue.

[Paul_Hossler]

Hello brilliant people!

I need help in a major way.

I found the code for this file manager on learnexcelmacro and built a workbook around it to track file names and properties saved to a shared folder. It mostly works very well, but it is rather slow. I am looking for a way to turn off screen updating and do anything else I can to speed up the macro. I tried to use Application.ScreenUpdating = False, but cannot seem to get it right.


The combo box to select the file types to fetch also does not work. It will only fetch all the file details if the "fetch all types of files" is selected. Please, help me figure this out. It has been an intense headache for a very confused VBA newb.



1. Use Option Explicit at the top of the module to require all variables to be explicitly defined (e.g. Dim X as Long). Catches many typos

2. Try not to use "On Error Resume Next" unless there's a very specific reason, and then turn it off with "On Error Goto 0"


In this, you told Excel to just keep on going even though you misspelled "Application" so ScreenUpdating was not turned off





On Error Resume Next
For Each FileItem In SourceFolder.Files

Aplication.ScreenUpdating = False



3. I like to explicitly Dim all variables. Here these are all Variant. They will work, but are not as efficient as explicitly Dim-ed variables



Sub textfile(iSeperator As String)

Dim iRow, iCol
Dim iLine, f



For example




Sub textfile(iSeperator As String)

Dim iRow As Long, iCol As Long
Dim iLine As Long, f as Long





4. Since you didn't specific a worksheet, these will go to what ever WS happens to be active at the time. It might not be the one you want



' display file properties
Cells(iRow, 2).Formula = iRow - 13
Cells(iRow, 3).Formula = FileItem.Name
Cells(iRow, 4).Formula = FileItem.Path
Cells(iRow, 5).Formula = Int(FileItem.Size / 1024)
Cells(iRow, 6).Formula = FileItem.Type
Cells(iRow, 7).Formula = FileItem.DateLastModified
Cells(iRow, 8).Select


Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
FileItem.Path, TextToDisplay:="Click Here to Open"




The 'dot' on .Cells explicitly references the parent (i.e. no assumptions), in this case "File Manager"





' display file properties
With Worksheet("File Manager")
.Cells(iRow, 2).Formula = iRow - 13
. Cells(iRow, 3).Formula = FileItem.Name
. Cells(iRow, 4).Formula = FileItem.Path
. Cells(iRow, 5).Formula = Int(FileItem.Size / 1024)
. Cells(iRow, 6).Formula = FileItem.Type
. Cells(iRow, 7).Formula = FileItem.DateLastModified
. Cells(iRow, 8).Select

Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
FileItem.Path, TextToDisplay:="Click Here to Open"

End With