Hi !
.xlsb file format is just to reduce size on disk,
nothing with how Excel "works" !
Excel needs to load all in memory : application, add-ins, ribbon,
workbooks & worksheets, formulas, data, …
As it is just how Excel works so there is almost nothing to do
except to increase the RAM of computer !
Excel can create and manage some Access database.
But for big data, Access is far away the best choice.
On a recent thread on my local forum, a guy asked for a help to end
its project. An helper warned him again like several weeks ago
for this kind of database project Excel is under level, better is to move
to Access. The guy did'nt want that way again as it was so closed to ending.
After some help, two days later the guy was happy to finish but
complained about a long time execution for a main procedure.
Helper tested on his side : 53 minutes to finish the process.
The guy said : « Ok it's normal, almost same time on my side. »
Few hours later, the helper tried on Access : less than a minute !
The guy was surprised and disappointedand wrote
« Ok but 'cause your are an expert too on Access. »
The helper answered his code was like a beginner but yes,
as an expert he optimized his code and his new version lasts
around 20 seconds ! Versus 53 minutes under Excel …
Access is the right choice for big data, safety of data,
shared work between users, work on a lan, …
Often database and maintenance procedures are under Access
but some data can be exported to Excel
(or Excel can directly grab Access data via SQL)
for analysis for example …
Sub Test()
Dim TempArr
Dim cel As Range, x
Dim sTime As Single, eTime As Single
sTime = Timer
myArray = Sheets("DataBase").Range("A1").CurrentRegion ' I think we can not use Current region as data sibe may go to 5laksh rows.
ReDim TempArr(1 To UBound(myArray, 1), 1 To 3)
For i = 1 To UBound(myArray, 1)
TempArr(i, 1) = myArray(i, 1) & "|" & myArray(i, 2)
TempArr(i, 2) = myArray(i, 3)
TempArr(i, 3) = myArray(i, 4)
Next
For Each cel In Range("A1:A8") 'Array is only for a1 to a8 but this trying to make dynamic
x = Application.Match(cel.Value & "|" & cel.Offset(, 1).Value, 'This need to change as per requirent as we have many rows
Application.Index(TempArr, , 1), 0)
Debug.Print x
Next
eTime = Timer
Debug.Print eTime - sTime
End Sub
Sub Test()
Dim TempArr, i
Dim jStr As String
Dim cel As Range
Dim sTime As Single, eTime As Single
sTime = Timer
myArray = Sheets("DataBase").Range("A2:N" & Sheets("DataBase").Cells(Rows.Count, 1).End(xlUp).Row)
For Each cel In Range("V20:V" & Cells(Rows.Count, "V").End(xlUp).Row)
jStr = Join(Array(cel, cel.Offset(, 8), cel.Offset(, 9), cel.Offset(, 11), cel.Offset(, 14), _
cel.Offset(, 15), cel.Offset(, 16), cel.Offset(, 17), cel.Offset(, 18)), "|")
i = Application.Match(jStr, Application.Index(myArray, , 14), 0)
If IsError(i) Then
cel.Resize(, 19).Interior.Color = vbRed
cel.Resize(, 19).Font.Color = vbWhite
GoTo Skip
End If
If myArray(i, 10) = "" Or myArray(i, 13) = "" Then
cel.Resize(, 19).Interior.Color = vbRed
cel.Resize(, 19).Font.Color = vbWhite
ElseIf myArray(i, 10) = "Testing" Or myArray(i, 13) = "Testing" Then
cel.Resize(, 19).Interior.Color = vbRed
cel.Resize(, 19).Font.Color = vbWhite
ElseIf myArray(i, 10) = "Inprogress" Or myArray(i, 13) = "Inprogress" Then
cel.Resize(, 19).Interior.Color = vbRed
cel.Resize(, 19).Font.Color = vbWhite
End If
Skip:
Next
eTime = Timer
Debug.Print eTime - sTime
End Sub
