Home>

vba - [vbscript] is there any difference in speed depending on the set/close timing of fsoopentextfile?

Question

When appending text to a CSV file, is there a difference in speed between Set and Close inside and outside the loop?

Code example Set and close in a loop

To narrow the scope of a variable

Const strCSVPath = "hoge.csv"
For Each str In Array ("a", "b", "c")
    Dim FSO: Set FSO = CreateObject ("Scripting.FileSystemObject")
    Dim TS: Set TS = FSO.OpenTextFile (strCSVPath, 8)
    TS.WriteLine str
    TS.Close
Next
Set and close outside the loop

If I want to reduce the number of Set and Close

Const strCSVPath = "hoge.csv"
Dim FSO: Set FSO = CreateObject ("Scripting.FileSystemObject")
Dim TS: Set TS = FSO.OpenTextFile (strCSVPath, 8)
For Each str In Array ("a", "b", "c")
    TS.WriteLine str
Next
TS.Close
vba vbscript
  • Answer # 1

      

    Is there a difference in speed between Set and Close inside and outside the loop?

    Yes.
    If you set and close in the loop,Scripting.FileSystemObjectis generated (file open), written, and closed three times.
    If you set or close outside the loop,Scripting.FileSystemObjectis generated (file open) once, written three times, and closed once.

    It is rough, but the former requires 9 steps, while the latter requires 5 steps.

    Also, creating objects (creatingScripting.FileSystemObject) and opening and closing files are expensive (slow).

  • Answer # 2

    Processing that does not need to be performed inside the loop will be executed outside the loop because the amount of instructions to be executed is reduced.

    This is also the case. Moreover, since the process includes disk access, the impact is relatively large.
    However, as in this example, there is no significant difference in about 3 loops.

Related articles