Excel VBA Append. A TextBox è lento

Ho una forma di utente che genera una grande quantità di text e la mette in una casella di text.

Ho la seguente function per aggiungere la row successiva di text alla casella di text:

Sub AddLineToSQL(sLine As String) frmSQL.txtSQL.Value = frmSQL.txtSQL.Value & sLine & vbCr End Sub 

Quando si aggiungono parecchie centinaia di righe di text occorre un process di elaborazione (fino a 20 secondi).

Il problema con questo è che c'è la possibilità di aggiungere più di mille righe di text.

Abbiamo una vecchia forma che fa la stessa cosa, ma sto cercando di creare un'esperienza utente più pulita. la vecchia forma ha scritto il text in un foglio di lavoro e sembra funzionare molto più velocemente di aggiungere alla casella di text.

C'è un modo più efficiente per aggiungere text a una casella di text di quello che ho sopra?

dovrei fare quello che faceva la vecchia forma e scrivere linee a un foglio di lavoro?

Grazie,

marchio

Non aggiungere linea per row al TextBox. Invece concatenando una string con tutte le righe e impostata quella string come valore di TextBox.

 Sub test() Dim sTxtSQL As String For i = 1 To 5000 sTxtSQL = sTxtSQL & "This is row " & i & vbCrLf Next frmSQL.txtSQL.Value = sTxtSQL frmSQL.Show End Sub Sub test () Sub test() Dim sTxtSQL As String For i = 1 To 5000 sTxtSQL = sTxtSQL & "This is row " & i & vbCrLf Next frmSQL.txtSQL.Value = sTxtSQL frmSQL.Show End Sub 

se la tua quantità di text sia più grande allora potresti usare questa class:

 ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Opzione esplicita ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Private Sub Class_Initialize () ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub End Sub ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub End Sub ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Fine properties; ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Fine properties; ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Clear () ' Class: StringBuilder ' from http://stackoverflow.com/questions/1070863/hidden-features-of-vba Option Explicit Private Const initialLength As Long = 32 Private totalLength As Long ' Length of the buffer Private curLength As Long ' Length of the string value within the buffer Private buffer As String ' The buffer Private Sub Class_Initialize() ' We set the buffer up to it's initial size and the string value "" totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub Public Sub Append(Text As String) Dim incLen As Long ' The length that the value will be increased by Dim newLen As Long ' The length of the value after being appended incLen = Len(Text) newLen = curLength + incLen ' Will the new value fit in the remaining free space within the current buffer If newLen <= totalLength Then ' Buffer has room so just insert the new value Mid(buffer, curLength + 1, incLen) = Text Else ' Buffer does not have enough room so ' first calculate the new buffer size by doubling until its big enough ' then build the new buffer While totalLength < newLen totalLength = totalLength + totalLength Wend buffer = Left(buffer, curLength) & Text & Space(totalLength - newLen) End If curLength = newLen End Sub Public Property Get Length() As Integer Length = curLength End Property Public Property Get Text() As String Text = Left(buffer, curLength) End Property Public Sub Clear() totalLength = initialLength buffer = Space(totalLength) curLength = 0 End Sub 

basta inserirlo in qualsiasi module di class e denominarlo dopo "StringBuilder"

allora puoi testarlo in modo analogo per la risposta di Axel:

 Sub test() Dim i As Long Dim sb As StringBuilder Dim sTxtSQL As String Dim timeCount As Long timeCount = Timer Set sb = New StringBuilder For i = 1 To 50000 sb.Append "This is row " & CStr(i) & vbCrLf Next i sTxtSQL = sb.Text MsgBox Timer - timeCount frmSQL.txtSQL.Value = sTxtSQL frmSQL.Show End Sub Sub test () Sub test() Dim i As Long Dim sb As StringBuilder Dim sTxtSQL As String Dim timeCount As Long timeCount = Timer Set sb = New StringBuilder For i = 1 To 50000 sb.Append "This is row " & CStr(i) & vbCrLf Next i sTxtSQL = sb.Text MsgBox Timer - timeCount frmSQL.txtSQL.Value = sTxtSQL frmSQL.Show End Sub Dim i As Long Sub test() Dim i As Long Dim sb As StringBuilder Dim sTxtSQL As String Dim timeCount As Long timeCount = Timer Set sb = New StringBuilder For i = 1 To 50000 sb.Append "This is row " & CStr(i) & vbCrLf Next i sTxtSQL = sb.Text MsgBox Timer - timeCount frmSQL.txtSQL.Value = sTxtSQL frmSQL.Show End Sub Avanti i Sub test() Dim i As Long Dim sb As StringBuilder Dim sTxtSQL As String Dim timeCount As Long timeCount = Timer Set sb = New StringBuilder For i = 1 To 50000 sb.Append "This is row " & CStr(i) & vbCrLf Next i sTxtSQL = sb.Text MsgBox Timer - timeCount frmSQL.txtSQL.Value = sTxtSQL frmSQL.Show End Sub 

Il mio test ha mostrato una significativa riduzione del tempo per gli "i" per oltre 50k