~M*E*H*D*I~
2012/07/10, 08:43
با سلام
در اینجا قصد دارم راهکار هایی برای افزایش قابلیت و سرعت برنامه هایی که با VBA نوشته میشه ارائه بدم بعضی از این راه کار ها به صورت تجربی بدست آوردم بعضی هم از جاهای مختلف جمع اوری کردم سایر دوستان هم تو تکمیل این بحث کمک کنن چون به نظرم مبحث مهمیه
برای افزایش سرعت برنامه هایی که از طریق VBA نوشته می شن روش هایی متفاوتی وجود داره مهمترین روش تکنیکی هست تو برنامه نویسی یک برنامه نویس استفاده میکنه که لازمه اون تجربه است اما خود اکسل و VBA امکاناتی برای افزایش سرعت در اختیار ما قرار میده.
1-غیر فعال کردن برخی از خصوصیت ها حین اجرای ماکرو برای این کار کد های زیر را به ابتدا و انتهای برنامه بیافزایید.
Sub macro()
Application.ScreenUpdating = false
Application.DisplayAlerts = false
Application.Calculation = xlManual
……..
Application.ScreenUpdating = true
Application.DisplayAlerts = true
Application.Calculation = xlAutomatic
end sub
از کد های زیر نیز میتوان استفاده کرد:
Application.StatusBar = false
Application.PrintCommunication = False
2-تا حد امکان از انتخاب کردن سل ها و محدوده ها خودداری کنید،بجای انتخاب کردن کپی کردن و paste کردن از تساوی استفاده کنید.
Range("A1").value = 10
کد فوق از کد زیر سریعتر است.
Range("A1").select
Selection.Value = 10
3- تا حدی که می توانید از توابع و متد های زبان برنامه نویسی استفاده کنید و از کد نویسی برای نیاز های خود پرهیز کنید مثلا استفاده از کد زیر
A=Application.WorksheetFunction.Sum(Range("a1", "a10"))
بجای
A=0
For i=1 to 10
A=A + cells(I,1)
Next i
- از ایجاد حلقه ها بخصوص حلقه های تودر تو اجتناب کنید ، برای حل یک مسئله ابتدا روش ها مختلف را بررسی کنید به عنوان مثلا برای جستجوی یک مقدار از توابع search,find,index,match,lookup,… استفاده کنید .
5- اگر میخواهید عملیاتی بر روی یک محدوده اعمال کنید محدوده را نامگذاری کنید برای توسعه محدوده از فرمول offset استفاده کنید بجای اینکه محدوده بزرگی انتخاب کنید تا نیاز اینده را برطرف کند بلکه محدوده را به تدریج توسعه دهید.
6-زمانی که میخواهید عملیات متفاوتی بر روی یک ابجکت اعمال کنید از with block استفاده کنید این کار علاوه بر نظم ظاهری در عمکرد کد هم موثر است.
7-زمانی که میخواهید نیاز خود را از طریق کد نویسی در اکسل مرتفع کنید به جوانب مسئله خود فکر کنید و ان را به دو بخش تقسیم کنید 1- بخشی که لزوما باید کد نویسی کنید و 2-بخشی که از طریق ابزار های اکسل اعم از فرمول ها ، conditional formattingو addins های اکسل و... میتوان حل کرد بنا براین لزومی ندارد کل مسئله به صورت کد پیاده شود تلفیق کلیه امکانات در دسترس نتیجه بهتری میدهد
8-در نوشتن فرمول ها از قواعد نامگذاری استفاده کنید و محدوده ها را با offset توسعه دهید
9-قبل از شروع بکار ابتدا جستجویی در اینترنت داشته باشید معمولا کدهای آماده با عملکرد بالا را میتوانید بیابید این امر بر مهارت شما نیز خواهد افزود به شرط اینکه کد ها را بخوبی تحلیل کنید.
10-برنامه خود را قبل از شروع به کد نویسی روی کاغذ پیاده کنید و طرح اولیه انرا ایجاد کنید سپس به قسمت های کوچک تقسیم کنید سعی نکنید یک پروسه را با یک کد طولانی انجام دهید بلکه برنامه را به توابع و رویه های کوچک تقسیم کنید.
11- از کنترل خطاها یا error handing تا حد امکان استفاده نکنید مگر اینکه طیف وسیعی از افراد کاربر برنامه شما باشند در صورتی که خود شما کاربر برنامه هستید از حداقل کنترل خطا استفاده کنید البته میزان دقت خود شما در این روش مؤثر است.
12-با استفاده از ctrl+end آخرین سل استفاده شده را یافته و در صورتی که نیاز ندارید انها را حذف کنید
13-در صورتی که قصد ایجاد جدول و یا دیتابیس در اکسل دارید از table استفاده کنید.
14-اطلاعات موجود را قبل از استفاده sort کنید.
15- در هر خط برنامه میتوان از خلاقیت برای افزایش کارایی استفاده کرد به عنوان مثال نتیجه دو کد زیر یکسان است اما قابلیت کد دوم به مراتب بیشتر است.
1-
If x>1 then
A=true
Else
A=false
End if
2-
A=x>1
-برای کاهش حجم میتوانید از فرمت xlsb بجای xlsmیا xlsx استفاده کنید در این حالت برنامه شما کم حجم تر خواهد بود.
17-از عوامل مهم کاهش سرعت استفاده از منابع و لینک های خارجی است لذا از ایجاد لینک های غیر ضروری باید پرهیز شود.
18-اگر فایل هایی با فرمت 2003 دارید و در حال حاضر از ورژن های 2007 به بالا استفاده می کنید آنها را به فرمت های جدید convert نمایید.
19-استفاده از pivot table بر کارایی و سرعت برنامه شما نسبت به سایر روش ها خواهد افزود.
در اینجا قصد دارم راهکار هایی برای افزایش قابلیت و سرعت برنامه هایی که با VBA نوشته میشه ارائه بدم بعضی از این راه کار ها به صورت تجربی بدست آوردم بعضی هم از جاهای مختلف جمع اوری کردم سایر دوستان هم تو تکمیل این بحث کمک کنن چون به نظرم مبحث مهمیه
برای افزایش سرعت برنامه هایی که از طریق VBA نوشته می شن روش هایی متفاوتی وجود داره مهمترین روش تکنیکی هست تو برنامه نویسی یک برنامه نویس استفاده میکنه که لازمه اون تجربه است اما خود اکسل و VBA امکاناتی برای افزایش سرعت در اختیار ما قرار میده.
1-غیر فعال کردن برخی از خصوصیت ها حین اجرای ماکرو برای این کار کد های زیر را به ابتدا و انتهای برنامه بیافزایید.
Sub macro()
Application.ScreenUpdating = false
Application.DisplayAlerts = false
Application.Calculation = xlManual
……..
Application.ScreenUpdating = true
Application.DisplayAlerts = true
Application.Calculation = xlAutomatic
end sub
از کد های زیر نیز میتوان استفاده کرد:
Application.StatusBar = false
Application.PrintCommunication = False
2-تا حد امکان از انتخاب کردن سل ها و محدوده ها خودداری کنید،بجای انتخاب کردن کپی کردن و paste کردن از تساوی استفاده کنید.
Range("A1").value = 10
کد فوق از کد زیر سریعتر است.
Range("A1").select
Selection.Value = 10
3- تا حدی که می توانید از توابع و متد های زبان برنامه نویسی استفاده کنید و از کد نویسی برای نیاز های خود پرهیز کنید مثلا استفاده از کد زیر
A=Application.WorksheetFunction.Sum(Range("a1", "a10"))
بجای
A=0
For i=1 to 10
A=A + cells(I,1)
Next i
- از ایجاد حلقه ها بخصوص حلقه های تودر تو اجتناب کنید ، برای حل یک مسئله ابتدا روش ها مختلف را بررسی کنید به عنوان مثلا برای جستجوی یک مقدار از توابع search,find,index,match,lookup,… استفاده کنید .
5- اگر میخواهید عملیاتی بر روی یک محدوده اعمال کنید محدوده را نامگذاری کنید برای توسعه محدوده از فرمول offset استفاده کنید بجای اینکه محدوده بزرگی انتخاب کنید تا نیاز اینده را برطرف کند بلکه محدوده را به تدریج توسعه دهید.
6-زمانی که میخواهید عملیات متفاوتی بر روی یک ابجکت اعمال کنید از with block استفاده کنید این کار علاوه بر نظم ظاهری در عمکرد کد هم موثر است.
7-زمانی که میخواهید نیاز خود را از طریق کد نویسی در اکسل مرتفع کنید به جوانب مسئله خود فکر کنید و ان را به دو بخش تقسیم کنید 1- بخشی که لزوما باید کد نویسی کنید و 2-بخشی که از طریق ابزار های اکسل اعم از فرمول ها ، conditional formattingو addins های اکسل و... میتوان حل کرد بنا براین لزومی ندارد کل مسئله به صورت کد پیاده شود تلفیق کلیه امکانات در دسترس نتیجه بهتری میدهد
8-در نوشتن فرمول ها از قواعد نامگذاری استفاده کنید و محدوده ها را با offset توسعه دهید
9-قبل از شروع بکار ابتدا جستجویی در اینترنت داشته باشید معمولا کدهای آماده با عملکرد بالا را میتوانید بیابید این امر بر مهارت شما نیز خواهد افزود به شرط اینکه کد ها را بخوبی تحلیل کنید.
10-برنامه خود را قبل از شروع به کد نویسی روی کاغذ پیاده کنید و طرح اولیه انرا ایجاد کنید سپس به قسمت های کوچک تقسیم کنید سعی نکنید یک پروسه را با یک کد طولانی انجام دهید بلکه برنامه را به توابع و رویه های کوچک تقسیم کنید.
11- از کنترل خطاها یا error handing تا حد امکان استفاده نکنید مگر اینکه طیف وسیعی از افراد کاربر برنامه شما باشند در صورتی که خود شما کاربر برنامه هستید از حداقل کنترل خطا استفاده کنید البته میزان دقت خود شما در این روش مؤثر است.
12-با استفاده از ctrl+end آخرین سل استفاده شده را یافته و در صورتی که نیاز ندارید انها را حذف کنید
13-در صورتی که قصد ایجاد جدول و یا دیتابیس در اکسل دارید از table استفاده کنید.
14-اطلاعات موجود را قبل از استفاده sort کنید.
15- در هر خط برنامه میتوان از خلاقیت برای افزایش کارایی استفاده کرد به عنوان مثال نتیجه دو کد زیر یکسان است اما قابلیت کد دوم به مراتب بیشتر است.
1-
If x>1 then
A=true
Else
A=false
End if
2-
A=x>1
-برای کاهش حجم میتوانید از فرمت xlsb بجای xlsmیا xlsx استفاده کنید در این حالت برنامه شما کم حجم تر خواهد بود.
17-از عوامل مهم کاهش سرعت استفاده از منابع و لینک های خارجی است لذا از ایجاد لینک های غیر ضروری باید پرهیز شود.
18-اگر فایل هایی با فرمت 2003 دارید و در حال حاضر از ورژن های 2007 به بالا استفاده می کنید آنها را به فرمت های جدید convert نمایید.
19-استفاده از pivot table بر کارایی و سرعت برنامه شما نسبت به سایر روش ها خواهد افزود.