A way to speed up code VBA in for loop with ExecuteExcel4Macro

[rong3]

Hi there! , as theard title the time of code excute takes about 1 min on normal PC and take much than on other lower PC.
By using ExecuteExcel4Macro to read value of closed file and For loop to get row by row value of it as below code:


For r = 1 To 500 ' 500 sample data
'Condition for ending Sub if the row at E is empty
If Replace(CStr(ExecuteExcel4Macro(Ret & Range("E" & r).address(, , xlR1C1))), " ", "") <> "0" Then
If UCase(CStr(ExecuteExcel4Macro("'" & wbPath & "[" & wbName & "]" & title_trakhuon & "'!" & Range("H" & r).address(, , xlR1C1)))) = "V" _
And CDate(CLng(ExecuteExcel4Macro(Ret & Range("J" & r).address(, , xlR1C1)))) = CDate(UserForm1.txt_ngay.Text) _
Then
count_dap = count_dap + 1
For chay = 0 To 6 'get each column (6 cols)
UserForm1.txt_editor.Text = UserForm1.txt_editor.Text & ExecuteExcel4Macro("'" & wbPath & "[" & wbName & "]" & title_trakhuon & "'!" & Range("A" & r).Offset(0, chay).address(, , xlR1C1)) & " | "
Next chay
UserForm1.txt_editor.Text = UserForm1.txt_editor.Text & vbNewLine
UserForm1.txt_editor.Text = UserForm1.txt_editor.Text & "-------------------------------------------------------------------------------------------------------------------------------" & vbNewLine
End If
Else:
UserForm1.lbl_count_dap = "Status: There counts " & count_dap & " data(s)"
UserForm1.Show
Exit Sub
End If
Next r

The for looping get each rows of closed file by ExecuteExcel4Macro then set it into txt_editor which is textbox.
Is there other way to optimize code , it takes too much time. Thanks for reading, hope the helps of all.

[Jan Karel Pieterse]

Have you timed whether just opening the file in question and getting the values from the cells isn't actually faster?

[rong3]

Have you timed whether just opening the file in question and getting the values from the cells isn't actually faster?
It is totally slow, all the time it got value into textbox takes about 1min30s

[Kenneth Hobs]

It takes long to open by Workbooks.Open method or using excel4 method?

The excel4 method opens and closes the file on each call. The method can get more than one cell value in one call. It is better to put the results into array(s) and then work with them.

String concatenation takes a long time if you build large strings using & method.

Say in words what you want to do. Show data with sample file(s) attached.

[Paul_Hossler]

If you're looking to retrieve 500 (+/-) cells from a closed workbook, it seems like it'd be faster to open the workbook in the macro, grab the data, close the workbook, and then build your string

[rong3]

It takes long to open by Workbooks.Open method or using excel4 method?

The excel4 method opens and closes the file on each call. The method can get more than one cell value in one call. It is better to put the results into array(s) and then work with them.

String concatenation takes a long time if you build large strings using & method.

Say in words what you want to do. Show data with sample file(s) attached.
Takes long by using excel4 method. I have no choice in using concatenation to get value, but it takes about 15 length of value at all.
If put the results into array(s), so then i must put array results into textbox which I want to show and i think the time takes longer.
All I want to do is speed up as title by getting value from closed file and put it into textbox to show.

[rong3]

If you're looking to retrieve 500 (+/-) cells from a closed workbook, it seems like it'd be faster to open the workbook in the macro, grab the data, close the workbook, and then build your string
Yah, it takes only 2-3 seconds in speed, but I must open in background then close it, but I tried code about close background file , seems not close.

[Kenneth Hobs]

My tests found this:
3,000 GetValue() calls = 47.734375 seconds
1 GetObject() call = 0.5625 seconds

I used array methods. For the GetObject() method. A Copy GetClipboard method might be a bit faster.

Create 4 textbox controls with the first two with MultiLine set to True. Add one CommandButton. Put single textbox3 below the big textbox1.


Private Sub UserForm_Initialize()
Dim d As Double

d = Timer
Test1
'3,000 calls=47.734375 seconds
TextBox3.Value = Timer - d & " seconds"

d = Timer
Test2
'1 call=0.5625 seconds
TextBox4.Value = Timer - d & " seconds"
End Sub




Private Sub Test1()
Dim calc As Integer, p$, f$, r$, s$, t$
Dim i As Long, j As Integer, a, rr As Range

p = ThisWorkbook.path '& "\"
f = "Rows Columns.xlsx"
s = "Sheet1"
r = "A1:F500"


On Error GoTo EndSub
Application.ScreenUpdating = False
Application.EnableEvents = False
calc = Application.Calculation
Application.Calculation = xlCalculationManual

Set rr = Range(r)
ReDim a(1 To rr.Rows.Count, 1 To rr.Columns.Count)
For i = 1 To rr.Rows.Count
For j = 1 To rr.Columns.Count
a(i, j) = GetValue(p, f, s, Cells(i, j).Address)
Next j
Next i
TextBox1.Value = Join2D(a)

EndSub:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = calc
End Sub


Private Sub Test2()
Dim calc As Integer, p$, f$, r$, s$, t$, fn$
Dim i As Long, j As Integer, a, rr As Range

p = ThisWorkbook.path & "\"
f = "Rows Columns.xlsx"
s = "Sheet1"
r = "A1:F500"
fn = p & f

If Dir(fn) = "" Then Exit Sub


On Error GoTo EndSub
Application.ScreenUpdating = False
Application.EnableEvents = False
calc = Application.Calculation
Application.Calculation = xlCalculationManual

a = GetObject(fn).Worksheets(s).Range(r).Value
Workbooks(f).Close False
TextBox2.Value = Join2D(a)

EndSub:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = calc
End Sub




Private Sub CommandButton1_Click()
Unload Me
End Sub




'Retrieves a value from a closed workbook
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
'path = "d:\files"
'file = "budget.xls"
'sheet = "Sheet1"
'ref = "A1:R30"

'Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = CVErr(xlErrNA)
End If

'Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function




'http://dailydoseofexcel.com/archives/2015/01/28/joining-two-dimensional-arrays/
Public Function Join2D(ByVal vArray As Variant, _
Optional ByVal sWordDelim As String = vbTab, _
Optional ByVal sLineDelim As String = vbCrLf) As String

Dim i As Long, j As Long
Dim aReturn() As String
Dim aLine() As String

ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1))
ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2))

For i = LBound(vArray, 1) To UBound(vArray, 1)
For j = LBound(vArray, 2) To UBound(vArray, 2)
'Put the current line into a 1d array
aLine(j) = vArray(i, j)
Next j
'Join the current line into a 1d array
aReturn(i) = Join(aLine, sWordDelim)
Next i

Join2D = Join(aReturn, sLineDelim)
End Function

[rong3]

Thank you so much, I better chose open closed file in background and pass all to array and dispose it. It now takes 5-6 seconds at all. Thanks for supporting me!

[snb]

The simplest/fastest method:


Private Sub Userform_initialize()
With getobject("G:\OF\example.xlsx")
.sheets(1).range("A1:F50").copy

With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
TextBox1.Text= .GetText
End With

.close 0
end with
End Sub

[Kenneth Hobs]

I guess the "might" in post #8 was wrong. Even so, it is the more simple method as snb said. In speed tests, when I see numbers less than one second, I am happy with that.

In these tests, Test3 is the clipboard method as snb showed in post #10. I just added Application.DisplayAlerts=False as large blocks of data will throw up the save clipboard prompt.

When I test speed, what runs first can make a big difference. Usually, it is the 1st run of any macro that is the slowest. Speed is also affected by running in VBE vs. other methods. These tests were ran in the VBE.

Test3 0.31640625
Test2 0.19921875
Test3 0.3125
Test2 0.19921875


Test2 0.22265625
Test3 0.2890625
Test2 0.22265625
Test3 0.2890625