VBA speed up. Boost VBA performance with VbaCompiler.

To improve the performance speed of your VBA code, all you have to do is just by compile it with VbaCompiler for Excel. After compilation you may multiply ... VBACompiler Featuresbylicenses Howtocompile Compileroptions ExcelVBAcopyprotection Createtrial/demoversion RuntimeAPIFunctions TranslateRun-TimeMessages VBAcommandlinecompilation CompileTimeTroubleshooting EULA PrivacyPolicy BestwaytoprotectVBAcode VBASpeedUp VBABenchmarks OptimizeVBAcode FrequentQuestions Download Order Contactus VBAspeedup.BoostVBAperformancewithVbaCompiler.   AfteryouhaveappliedallofourmostimportantoptimizationtipsforVBAcodeandnowthatyourcodeperformsfasterthanitdidbeforeoptimization,thenyoumaycontinuetospeedupyourVBAcodefurther.ToimprovetheperformancespeedofyourVBAcode,allyouhavetodoisjustbycompileitwithVbaCompilerforExcel. Aftercompilationyoumaymultiplyperformanceofyourcode,byseveralordersofmagnitudeinsomecases! WhywouldthecompilationtoDLLimprovetheVBAcodeexecutionperformance? Herearethemain3reasonsforthat: 1.Duringcompilation,theVbaCompilerconvertsyourcodefromVBAcodetotheoneofthebestrun-timeperformanceefficientprogramminglanguage–theC-language. 2.MinGWGCCC-compiler,whichisusedastheback-endcompiler,performsspeedoptimizationsduringbinarycodegeneration–theprocessinwhichtheDLLfileiscreated. 3.Weareworkingtomaketherun-timelibraryforVBAelementsasfastaspossible. Let’sconsiderdifferentVBAelementsandhowthecompilationtoDLLaffecttheirperformance. Allofthefollowingexampleswerecompiledwiththeoptimizationoptionlevelsetto“Ofast”. Youmaydownloadthe“vbaspeedup.xlsm”workbook,whichcontainstheexamplesthatareusedhere,fromourwebsite.     ImpressiveVBAspeedupincompiledForLoop.   TheForloopshowsatremendousspeedupaftercompilation,wheretheindexvariableisdeclaredasLong. Forexample,thefollowingpieceofcodeshowsmorethanax3000000(3million)speeduprateaftercompilation,whenoptimizationoptionlevelinVbaCompilercompilationoptionsisselectedas“Ofast”. DimiterCountasLong DimiasLong Fori=0to10000000 iterCount=iterCount+1 Next InthecaseofForLooptheC-compileroptimizerdoesthebestjobtoimproveperformanceofthecompiledcode.     SpeedUpVBAwithStronglyTypedMethodDeclarations   ThewayinwhichVBAmethodsaredeclaredhasasignificantimpactontheperformanceofthecompiledVBAcode. Let’sconsiderdifferentwaysofmethoddeclarations. FunctionFunc1(a,b,c,d) Func1=a+b+c+d EndFunction InthisversionalloftheparametersaredeclaredasaVariant,whicharepassedbyreference.ThisfunctionreturnsaVariantvalue.     Thenextversionofthesamefunction:   FunctionFunc2(aAsLong,bAsInteger,cAsDouble,dAsSingle)AsDouble Func2=a+b+c+d EndFunction Inthisversionofmethoddeclaration,thesameparametersaredeclaredwithspecificnumerictypespassedbyreferenceandthefunctionreturnsDouble.     Thefinalformofthesamefunctiondeclaration: FunctionFunc3(ByValaAsLong,ByValbAsInteger,ByValcAsDouble,ByValdAsSingle)AsDouble Func3=a+b+c+d EndFunction Inthisversionofmethoddeclaration,thesameparametersaredeclaredwithspecificnumerictypes,but,inthiscase,theyarealsopassedbyvalue.Thisfunction,muchlikethesecondfunctioncoveredabove,stillreturnsaDouble.   Asyoucansee,thedeclarationwithaVarianttypehasthelongestexecutiontime,bothintheoriginalVBAcodeandinthecompiledDLLversion.Followingthe‘AvoidVarianttype’andthe‘Avoiddeclarationofmethodparameterswithouttype’VBAoptimizationtipsprovidethebestperformanceboosttothecompiledVBAcode.   ObjectMethods   OurapproachtospeedingupVBAcodeforobjectmethodsaftercompilationwithc-compileroptimizationgivesasmallperformanceimprovementordoesnotgiveaperformanceboostatall.Thisisrelatedtothec-languageoptimizer,whichcannotoptimizecomplexobjects.Also,allofthelow-levelmanipulationsofobjectmethodsinActiveX(atechnologywhichisusedinVBAobjects)arebasedontheVarianttype.TheVarianttype,fromthelow-levelpointofview,isastructure,whichisalwaysmorecomplexthanabasictype.Theperformanceofstructuredatamanipulationscannotbesignificantlyimprovedwithac-languagecompileroptimizer. Let’sconsidertheeffectofcompilationperformanceboostonthecalculationtimeofVBAmethodswhichpopulateabigExcelcellrangewithrandomnumbers(inthiscase,the“PopulateCells”method)andcalculatesumofrandomdatainthisrange(usingthe“CalcSum”method).       Conclusion InordertogetthemaximumoutofyourVBAcodeperformanceaftercompilingwithDoneExVbaCompilerforExcel,youneedtoprepareyourVBAcodebeforethecompilation.ThismeansapplyingalloftheTop25VBAperformanceimprovementtipsandcompilingyourVBAcodewiththeVbaCompiler’s“OptimizationLevel”optionsetto“O3”or“Ofast”. Asyoucanseefromthetestsabove,theperformanceofthecompiledVBAcodedependsonavoidingVarianttypesandcarefullyprovidingtheexactdatatypedeclarationforyourvariables,methodparameters,andmethodreturntypes.