Петли на VBA пользовательская форма для сбора номеров из ячеек на листе Excel


Я работаю на пользовательской форме и все работает так, как надо, но это займет немного больше времени, чем хотелось бы. Это зацикливание через 42 метки для каждого For заявление и есть шесть из них.

В стремлении узнать, как код немного более эффективным, может кто-то пожалуйста, проверьте мой код и показывают мне некоторые быстрые способы, чтобы получить работу?

Private Sub Find()
Application.ScreenUpdating = False
Dim i As Integer, k As Integer
k = 1

i = 1

For i = i To 42
C1 = "C1_" & i

Me.Controls(C1) = Sheet14.Range("I" & k).Value

If Me.Controls(C1) = "0" Then
Me.Controls(C1).ForeColor = &H8000000F

End If
k = k + 1

Next

k = 1

i = 1

For i = i To 42
C2 = "C2_" & i
Me.Controls(C2) = Sheet14.Range("J" & k).Value
If Me.Controls(C2) = "0" Then
Me.Controls(C2).ForeColor = &H8000000F

End If
k = k + 1

Next
'
k = 1

i = 1

For i = i To 42
C3 = "C3_" & i
Me.Controls(C3) = Sheet14.Range("K" & k).Value
If Me.Controls(C3) = "0" Then
Me.Controls(C3).ForeColor = &H8000000F

End If
k = k + 1

Next
k = 1

i = 1

For i = i To 42
C4 = "CL4_" & i
Me.Controls(C4) = Sheet14.Range("L" & k).Value
If Me.Controls(C4) = "0" Then
Me.Controls(C4).ForeColor = &H8000000F

End If
k = k + 1

Next

k = 1

i = 1

For i = i To 42
C5 = "C5_" & i
Me.Controls(C5) = Sheet14.Range("M" & k).Value
If Me.Controls(C5) = "0" Then
Me.Controls(C5).ForeColor = &H8000000F

End If
k = k + 1

Next

k = 1

i = 1

For i = i To 42
C6 = "C6_" & i
Me.Controls(C6) = Sheet14.Range("N" & k).Value
If Me.Controls(C6) = "0" Then
Me.Controls(C6).ForeColor = &H8000000F

End If
k = k + 1

Next
Application.ScreenUpdating = True
End Sub


126
1
задан 24 февраля 2018 в 03:02 Источник Поделиться
Комментарии
2 ответа

i = 1 и For i = i To 42 не так, как вы инициировать цикл.

Вот псевдо код написать For Next петли:

For i = Low_Bound to Upper_Bound

Next

Это видео объяснит это лучше :VBA для Excel введение Часть 16 - для следующей петли. Я вам рекомендую посмотреть полную серию на YouTube.

Здесь вы установить ForeColor если одно условие выполнено, но вы никогда не сбросить его. Дважды запустив код даст не работать должным образом.

If Me.Controls(C6) = "0" Then 
Me.Controls(C6).ForeColor = &H8000000F
Else
Me.Controls(C6).ForeColor = -2147483630
End If

&H8000000F это шестнадцатеричный код для значения по умолчанию ForeColor в пользовательской форме. Если вы хотите скрыть этикетку, чем изменить его видимость.

Me.Controls(C6).Visible = Not Me.Controls(C6) = "0" 

Application.ScreenUpdating не влияет на пользовательской форме. Использовать его при написании или форматировании диапазона.

Как правило, если у вас есть большое количество, повторите код распакуйте его в вспомогательную функцию.

Рефакторинг Кода

Private Sub UpdateLabels()

Dim Index As Long
For Index = 1 To 42
setLabel "C1_", "I", Index
setLabel "C2_", "J", Index
setLabel "C3_", "K", Index
setLabel "CL4_", "L", Index
setLabel "C5_", "M", Index
setLabel "C6_", "N", Index
Next

End Sub

Sub setLabel(Prefix As String, RefColumn As Variant, Index As Long)
With Me.Controls(Prefix & Index)
.Caption = sheet14.Cells(Index, RefColumn).Value
If .Caption = "0" Then
.ForeColor = &H8000000F
Else
.ForeColor = -2147483630
End If
End With
End Sub

Примечание: моя цель в том, чтобы сделать процесс как можно более простым. Я не заморачиваюсь загрузки данных в массив, потому что чтение данных из рабочего листа-это недорогой процесс, и мы только делаем 252 просмотров. Код практически мгновенно на проверку пользовательской формы.

0
ответ дан 24 февраля 2018 в 09:02 Источник Поделиться

Как твое имя суб? Найти? Это встроенная функция приложения. И это не объясняет, на все, что ты делаешь.

Также ваши отступы и интервалы глубоко заблуждается. Даже если учесть дополнительное пространство, ваша функция должна выглядеть так -

Option Explicit
Private Sub PopulateLabels()
Application.ScreenUpdating = False
Dim i As Integer, k As Integer

k = 1
i = 1

For i = i To 42
C1 = "C1_" & i
Me.Controls(C1) = Sheet14.Range("I" & k).Value
If Me.Controls(C1) = "0" Then
Me.Controls(C1).ForeColor = &H8000000F
End If
k = k + 1
Next

k = 1
i = 1

For i = i To 42
C2 = "C2_" & i
Me.Controls(C2) = Sheet14.Range("J" & k).Value
If Me.Controls(C2) = "0" Then
Me.Controls(C2).ForeColor = &H8000000F
End If
k = k + 1
Next

k = 1
i = 1

For i = i To 42
C3 = "C3_" & i
Me.Controls(C3) = Sheet14.Range("K" & k).Value
If Me.Controls(C3) = "0" Then
Me.Controls(C3).ForeColor = &H8000000F
End If
k = k + 1
Next

k = 1
i = 1

For i = i To 42
C4 = "CL4_" & i
Me.Controls(C4) = Sheet14.Range("L" & k).Value
If Me.Controls(C4) = "0" Then
Me.Controls(C4).ForeColor = &H8000000F
End If
k = k + 1
Next

k = 1
i = 1

For i = i To 42
C5 = "C5_" & i
Me.Controls(C5) = Sheet14.Range("M" & k).Value
If Me.Controls(C5) = "0" Then
Me.Controls(C5).ForeColor = &H8000000F
End If
k = k + 1
Next

k = 1
i = 1

For i = i To 42
C6 = "C6_" & i
Me.Controls(C6) = Sheet14.Range("N" & k).Value
If Me.Controls(C6) = "0" Then
Me.Controls(C6).ForeColor = &H8000000F
End If
k = k + 1
Next

Application.ScreenUpdating = True
End Sub

Я переименовал его и добавил отступы. Теперь давайте перейдем к коду.


Переменные

&H8000000F является постоянным для vbButtonFace. Я бы по крайней мере дать ему константу вместо того, чтобы повторять его снова и снова

 Const FACE_COLOR as Long = vbButtonFace

Ваш i и k переменные говорите мне ничего о том, что они. Дайте мне некоторый контекст. Кроме того, вы не объявленные переменные

C1
C2
C3
C4
C5
C6

Что это? Кроме того, зачем они создаются для разных строк?

C1 = "C1_" & i
C2 = "C2_" & i
C3 = "C3_" & i
C4 = "CL4_" & i 'Where did that L come from
C5 = "C5_" & i 'Where did that L go
C6 = "C6_" & i

Я не могу представить, что они, или почему они должны быть установлены в 42 раза. Также похоже, что вы переходите на k такое же количество раз как iтак зачем вы нужны k?


Рефакторинг

Вы постоянно установить i и k 1, то итерации i от 1 до 42, ростом k по одной каждый раз. i и k такие же и у них всегда один и тот же набор чисел.

Не только это, но вы делаете то же самое в 6 раз -

For i = 1 To 42
'something is something and i
'something is Sheet14, letter and i
'Check if it's 0 and change color
Next i

Насколько я могу судить, вы измените строку управления и буквой столбца. Я уверен, что мы можем создать процедуру, которая принимает аргументы для этого -

Sub DoTheThing(ByVal controlNameBase As String, ByVal columnNumber As Long)
Dim i As Long
Dim controlName As String
For i = 1 To 42
controlName = controlNameBase & i
Me.Controls(controlName) = sheet14.Cells(i, columnNumber)
If Me.Controls(controlName) = 0 Then Me.Controls.ForeColor = vbButtonFace
Next
End Sub

Теперь у нас есть -

Option Explicit
Private Sub PopulateLabels()
Application.ScreenUpdating = False

Const CONTROL_STRING1 As String = "C1_"
Const CONTROL_STRING2 As String = "C2_"
Const CONTROL_STRING3 As String = "C3_"
Const CONTROL_STRING4 As String = "CL4_"
Const CONTROL_STRING5 As String = "C5_"
Const CONTROL_STRING6 As String = "C6_"
Const CONTROL_COLUMN1 As Long = 9
Const CONTROL_COLUMN2 As Long = 10
Const CONTROL_COLUMN3 As Long = 11
Const CONTROL_COLUMN4 As Long = 12
Const CONTROL_COLUMN5 As Long = 13
Const CONTROL_COLUMN6 As Long = 14

DoTheThing CONTROL_STRING1, CONTROL_COLUMN1
DoTheThing CONTROL_STRING2, CONTROL_COLUMN2
DoTheThing CONTROL_STRING3, CONTROL_COLUMN3
DoTheThing CONTROL_STRING4, CONTROL_COLUMN4
DoTheThing CONTROL_STRING5, CONTROL_COLUMN5
DoTheThing CONTROL_STRING6, CONTROL_COLUMN6

Application.ScreenUpdating = True
End Sub

Sub DoTheThing(ByVal controlNameBase As String, ByVal columnNumber As Long)
Dim i As Long
Dim controlName As String
For i = 1 To 42
controlName = controlNameBase & i
Me.Controls(controlName) = sheet14.Cells(i, columnNumber)
If Me.Controls(controlName) = 0 Then Me.Controls.ForeColor = vbButtonFace
Next
End Sub

Я уверен, что мы сможем переделать его немного.

Option Explicit
Private Sub PopulateLabels()
Application.ScreenUpdating = False
Const BASE_STRING As String = "C"
Const ALTERNATE_STRING As String = "CL"
Const END_STRING As String = "_"
Dim i As Long
Dim controlString As String
For i = 1 To 6
If i = 4 Then
controlString = ALTERNATE_STRING & i & END_STRING
Else
controlString = BASE_STRING & i & END_STRING
End If
DoTheThing controlString, i + 8
Next
Application.ScreenUpdating = True
End Sub

Теперь все 27 строк. Сначала это был 95.


Производительности

Вы сказали, что ваши петли долго. Это потому, что вы обращаетесь к лист каждый раз, когда вы петли. Принести вместо этих значений в массив.

Sub DoTheThing(ByVal controlNameBase As String, ByVal columnNumber As Long)
Dim i As Long
Dim controlName As String
Dim lookUpValues As Variant
lookUpValues = GetArray(columnNumber)
For i = 1 To 42
controlName = controlNameBase & i
Me.Controls(controlName) = lookUpValues(i,1)
If Me.Controls(controlName) = 0 Then Me.Controls(controlName).ForeColor = vbButtonFace
Next
End Sub

Private Function GetArray(ByVal columnNumber As Long) As Variant
GetArray = Sheet14.Range(Sheet14.Cells(1, columnNumber), Sheet14.Cells(42, columnNumber))
End Function


Заключение

Так что теперь вместо зацикливания постоянно через лист, вы просто получите все значения в массив и цикл через это. Это гораздо быстрее, чем с листа. Мы также исключены 5 из 6 петель путем создания процедуры. Он также, кажется, намного чище и менее громоздким.

0
ответ дан 24 февраля 2018 в 04:02 Источник Поделиться