Hello everyone
I am trying to build a code but make it general .. That's because there are a lot of changes and new columns added and other columns may be changed in place. So I thought of using modVariables to put the columns numbers in variables and to be easier to follow and edit later
in modVariable I put these lines
Option Explicit
Public wsData As Worksheet
Public wf As Object
Public lrData As Long
Public Const startDate As Date = "1/10/2020"
Public Const sDataSheet As String = "Data"
Public Const colStdCode As Integer = 1
Public Const colStdID As Integer = 2
Public Const colStdName As Integer = 3
Public Const colStdNationality As Integer = 4
Public Const colStdReligion As Integer = 5
Public Const colStdBirthDate As Integer = 6
Public Const colStdDay As Integer = 7
Public Const colStdMonth As Integer = 8
Public Const colStdYear As Integer = 9
Public Const colStdGovern As Integer = 10
Public Const colStdQid As Integer = 11
Public Const colStdGender As Integer = 12
Public Const colStdStatus As Integer = 13
Public Const colSchoolName As Integer = 14
Public Const colSchoolCode As Integer = 15
Public Const colSeatNumber As Integer = 16
Public Const colLagnaNumber As Integer = 17
Public Const colLagnaCode As Integer = 18
Public Const colLagnaName As Integer = 19
Public Const colMemberName As Integer = 20
Public Const colSecretNumber As Integer = 21
Public Const colSchoolEhsaaCode As Integer = 22
Public Const colNameInEnglish As Integer = 23
Public Const colArabicFirst As Integer = 24
Public Const colEnglishFirst As Integer = 25
Public Const colSocialFirst As Integer = 26
Public Const colGabrFirst As Integer = 27
Public Const colHandsaFirst As Integer = 28
Public Const colScienceFirst As Integer = 29
Public Const colCompFirst As Integer = 30
Public Const colReligionFirst As Integer = 31
Public Const colArtFirst As Integer = 32
Public Const colPEFirst As Integer = 33
Public Const colActivOneFirst As Integer = 34
Public Const colActivTwoFirst As Integer = 35
Public Const colArabicSecond As Integer = 36
Public Const colEnglishSecond As Integer = 37
Public Const colSocialSecond As Integer = 38
Public Const colGabrSecond As Integer = 39
Public Const colHandsaSecond As Integer = 40
Public Const colScienceSecond As Integer = 41
Public Const colCompSecond As Integer = 42
Public Const colReligionSecond As Integer = 43
Public Const colArtSecond As Integer = 44
Public Const colPESecond As Integer = 45
Public Const colActivOneSecond As Integer = 46
Public Const colActivTwoSecond As Integer = 47
Public Const colHighLevel1 As Integer = 48
Public Const colHighLevel2 As Integer = 49
Public Const colRecordAbsent As Integer = 50
Public Const dMarkArabic As Double = 80
Public Const dMarkEnglish As Double = 60
Public Const dMarkSocial As Double = 40
Public Const dMarkMaths As Double = 60
Public Const dMarkScience As Double = 40
Public Const dMarkTotal As Double = 280
Public Const dMarkComp As Double = 20
Public Const dMarkReligion As Double = 40
Public Const dMarkArt As Double = 20
Public Const dMarkPE As Double = 40
Public Const dMarkActivOne As Double = 20
Public Const dMarkActivTwo As Double = 20
And in ThisWorkbook module I put these lines
Private Sub Workbook_Open()
Set wsData = ThisWorkbook.Worksheets(sDataSheet)
Set wf = Application.WorksheetFunction
lrData = wsData.Cells(Rows.Count, colStdCode).End(xlUp).Row
End Sub
In modUtility I used some UDFs
Public Function UseSpeedyCode(goFast As Boolean)
With Application
.ScreenUpdating = Not goFast
.EnableEvents = Not goFast
If goFast Then .Calculation = xlManual Else .Calculation = xlAutomatic
End With
End Function
Function SumFirstSecond(ByVal v1, ByVal v2)
If Val(v1) = -1 And Val(v2) = -1 Then
SumFirstSecond = -1
Else
SumFirstSecond = Application.Max(0, Val(v1)) + Application.Max(0, Val(v2))
End If
End Function
Function SumFirstSecondMaths(ByVal v1, ByVal v2, ByVal v3, ByVal v4)
If Val(v1) = -1 And Val(v2) = -1 And Val(v3) = -1 And Val(v4) = -1 Then
SumFirstSecondMaths = -1
Else
SumFirstSecondMaths = Application.Max(0, Val(v1)) + Application.Max(0, Val(v2)) + Application.Max(0, Val(v3)) + Application.Max(0, Val(v4))
End If
End Function
Function SumTotal(ByVal v1, ByVal v2, ByVal v3, ByVal v4, ByVal v5)
SumTotal = Application.Max(0, Val(v1)) + Application.Max(0, Val(v2)) + Application.Max(0, Val(v3)) + Application.Max(0, Val(v4)) + Application.Max(0, Val(v5))
End Function
Now when trying the following code that SUMs some columns, I found it takes about 5 seconds for only 7000 rows
Sub Update_Database()
Dim a, b, i As Long
UseSpeedyCode True
a = wsData.Range("A2:AW" & lrData).Value
ReDim b(1 To UBound(a, 1), 1 To 12)
For i = 1 To UBound(a, 1)
b(i, 1) = SumFirstSecond(a(i, colArabicFirst), a(i, colArabicSecond))
b(i, 2) = SumFirstSecond(a(i, colEnglishFirst), a(i, colEnglishSecond))
b(i, 3) = SumFirstSecond(a(i, colSocialFirst), a(i, colSocialSecond))
b(i, 4) = SumFirstSecondMaths(a(i, colGabrFirst), a(i, colHandsaFirst), a(i, colGabrSecond), a(i, colHandsaSecond))
b(i, 5) = SumFirstSecond(a(i, colScienceFirst), a(i, colScienceSecond))
b(i, 6) = SumTotal(b(i, 1), b(i, 2), b(i, 3), b(i, 4), b(i, 5))
b(i, 7) = SumFirstSecond(a(i, colCompFirst), a(i, colCompSecond))
b(i, 8) = SumFirstSecond(a(i, colReligionFirst), a(i, colReligionSecond))
b(i, 9) = SumFirstSecond(a(i, colArtFirst), a(i, colArtSecond))
b(i, 10) = SumFirstSecond(a(i, colPEFirst), a(i, colPESecond))
b(i, 11) = SumFirstSecond(a(i, colActivOneFirst), a(i, colActivOneSecond))
b(i, 12) = SumFirstSecond(a(i, colActivTwoFirst), a(i, colActivTwoFirst))
Next i
wsData.Range("AY2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
UseSpeedyCode False
End Sub
The code is simple as it sums columns together and put the results in array
What makes the code slow...? I like to hear your ideas
I can't attach sample now but it is so simple and the data are just numbers ..
Bookmarks