۴ اشتباه رایج در هنگام نوشتن ماکروها در اکسل

نرم‌افزار مایکروسافت اکسل به تنهایی یک ابزار خیلی خوب و کامل برای مدیریت و آنالیز داده‌ها است که با ارائه‌ی امکانی نظیر نوشتن ماکروها (که به زبان «VBA» نوشته می‌شوند) برای کارهای تکراری، خیلی بهتر نیز می‌شود. با این حال، اگر از آن درست استفاده نشود، ممکن است مشکلات زیادی برای شما ایجاد کند.

حتی اگر برنامه‌نویس هم نباشید، VBA امکانات ساده‌ای در اختیار شما قرار می‌دهد که بتوانید به کمک آن‌ها توابع شگفت‌انگیزی را به صفحات خود اضافه کنید. چه در کار با VBA تجربه داشته باشید یا یک تازه کار باشید که فقط می‌تواند دستورات ساده برای محاسبه‌ی فیلدهای خاص را پیاده‌سازی کند، می‌توانید از «تکنیک‌های برنامه‌نویسی ساده» (easy programming techniques) استفاده نمایید تا به کمک آن کدهای بهتر با خطاهای کمتری تولید کنید.

شروع کار با VBA

اگر تا به حال با VBA در اکسل کار نکرده‌اید، باید ابزارهای «Developer» را فعال نمایید. برای اینکار از منوی «File» صفحه‌ی «Options» را باز کرده و سپس گزینه‌ی «Customize Ribbon» را انتخاب کنید.

در این بخش، گزینه‌ی «Developer» را برگزینید تا از پنجره‌ی سمت چپ به سمت راست منتقل شود.

تیک آن را بزنید تا زبانه‌ی «Developer» در منوی اکسل شما ظاهر شود. از این پس برای باز کردن پنجره‌ی ویراستار کد کافی است برروی گزینه‌ی «View Code» در بخش «Controls» که در منوی «Developer» قرار دارد کلیک کنید.

حالا که آماده‌ی کد نویسی هستیم، به 4 اشتباه رایج آن اشاره می‌کنیم تا بتوانید در کدهای خود از آن‌ها بپرهیزید.

1. استفاده از اسامی نا‌مناسب برای متغیرها

در هر زبان برنامه‌نویسی، اولین قدم مهم تعریف متغیرها است.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – تکمیلی در فرادرس

کلیک کنید

در نام‌گذاری متغیرها باید به چند نکته توجه کنید:

• تا جای ممکن از نام‌های کوتاه استفاده کنید.
• تا جای ممکن از نام‌هایی استفاده کنید که مفهوم مشخصی داشته باشند.
• نوع متغیر آن‌ها را در کنارشان ذکر کنید («boolean» ،«integer» و ...).
• به کوچک یا بزرگی حروف توجه کنید.

به تصویر زیر توجه کنید:

زمانی که می‌خواهید از یک متغیر در یک تابع در ماژول یا شیء خود استفاده کنید باید آن را به صورت «عمومی» تعریف کنید که برای اینکار باید در ابتدای آن‌ها از کلمه‌ی کلیدی «Public» استفاده کرده باشید. در غیر اینصورت، متغیرها با کلمه‌ی پیشفرض «Dim» تعریف می‌شوند.

همانطور که مشاهده می‌کنید، برای متغیرهای «integer» از «int» استفاده شده است و برای متغیرهای «string»، در اول نام آن عبارت «str» آمده است. این کار در ادامه به شما کمک می‌کند که در هرجایی با یک نگاه متوجه شوید از چه نوع متغیری استفاده کرده‌اید. همچنین در تصویر مشاهده می‌کنید که برای یک متغیر که وظیفه‌ی نگهداری نام کامپیوتر را بر عهده دارد، نام «strComputerName» انتخاب شده است که مفهوم آن را می‌رساند.

از نام‌های پیچیده‌ای که فقط خودتان متوجه می‌شوید استفاده نکنید. همیشه کدها را به گونه‌ای بنویسید که اگر یک برنامه‌نویس دیگر کد را مشاهده کرد، متوجه مفهوم آن شود. یکی دیگر از اشتباهات رایج این است که مردم عموما از نام‌های پیشفرض «Sheet1»، «Sheet2» و امثال آن برای صفحات خود استفاده می‌کنند. این کار باعث پیچیدگی برنامه‌ی شما می‌شود. به جای آن از نام‌های معنادار استفاده کنید.

با این کار، هنگامی که از نام یک صفحه در کد VBA استفاده می‌نمایید، دارید یک نام معنادار را به کار می‌برید. در مثال بالا، یک صفحه داریم که در آن اطلاعات شبکه ثبت شده است، از همین رو نام آن را «Network» گذاشته‌ایم. زمانی که نام گذاری اینگونه باشد، در کد نیز می‌توانیم به سادگی از نام آن استفاده کنیم و نیازی نداریم هر دفعه برگردیم و ببینیم چه عددی برای آن صفحه ثبت شده است.

2. خروج بد‌هنگام از حلقه

یکی از رایج‌ترین اشتباهات در بین برنامه‌نویسان تازه کار، برخورد نا‌مناسب با حلقه‌ها است، و از آن جایی که اکثر افرادی که در اکسل با VBA کار می‌کنند یک تازه کار در کد نویسی هستند، بیشتر از سایر برنامه‌نویسان مرتکب این اشتباه می‌شوند.

حلقه‌ها در اکسل بسیار پرکاربرد هستند، چرا که معمولا با داده‌های کامل یک سطر یا ستون سر و کار داریم، از همین رو باید با یک حلقه تمام آن‌ها را پردازش کنیم. برنامه‌نویسان جدید معمولا فقط به دنبال این هستند که در اولین فرصت از حلقه خارج شوند.

به پیچیدگی کد بالا کاری نداریم، فقط به دستور «Exit For» که در یک دستور «IF» قرار دارد توجه کنید. برای درک بهتر آن به کد زیر توجه کنید:

1For x = 1 To 20
2If x = 6 Then Exit For
3y = x + intRoomTemp
4Next i

برنامه‌نویسان این کار را به خاطر سادگی آن انجام می‌دهند. معمولا وقتی شرط مورد نظر برای خروج از حلقه پیش می‌آید، می‌خواهید که مستقیما از حلقه خارج شوید، ولی این کار را نکنید. در اکثر مواقع، کدی که پس از دستور «break» می‌آید مهم است، حتی اگر در آخرین اجرای حلقه باشد. یکی از راه‌های بهتر و حرفه‌ای‌تر برای مدیریت شرط خروج از حلقه این است که آن را در یک حلقه‌ی «While» قرار دهید. به کد زیر توجه کنید:

1While (x>=1 AND x<=20 AND x<>6)
2For x = 1 To 20
3y = x + intRoomTemp
4Next i
5Wend

این کار باعث منطقی‌تر شدن کدتان می‌شود. در این کد، آخرین اجرا زمانی است که x برابر با 5 است، و پس از آن که حلقه‌ی «For» به 6 برسد، حلقه خارج می‌شود و دیگر نیازی به دستورات «EXIT» یا «BREAK» در وسط کد نیست.

3. عدم استفاده از آرایه‌ها

یکی دیگر از اشتباهات رایج در بین برنامه‌نویسان VBA این است که سعی می‌کنند همه چیز را در حلقه‌های تو در تویی پردازش کنند که در بین مقادیر تمام سطرها و ستون‌ها می‌چرخند.

درست است که این روش جواب می‌دهد، ولی اگر بخواهید به طور دائم در بین مقدارهای ثابت در ستون‌ها به پردازش بپردازید، منجر به مشکلات بزرگی در عملکرد سیستم می‌شوید. چرخیدن دائمی در بین ستون‌ها برای به دست آوردن مقدار آن‌ها، نه تنها کار خسته کننده‌ای است، بلکه برای پردازنده‌ی سیستم نیز مناسب نیست. یک راه موثرتر برای رسیدگی به لیست‌های بلند، استفاده از آرایه‌ها است.

اگر تا به حال از آرایه‌ها استفاده نکرده‌اید، اصلا نگران نباشید. آرایه مانند یک قالب یخ می‌ماند که می‌توانید به تعداد مشخصی در آن اطلاعات نگهداری کنید. خانه‌های یک آرایه می‌تواند از 1 تا 12 شماره گذاری شده‌ باشد که با استفاده از این شماره‌ها می‌توانیم داده‌ها را در آن وارد کنیم.

برای تعریف یک آرایه تنها کافی است از دستور زیر استفاده کنید:

1Dim arrMyArray(12) as Integer

این قطعه کد یک آرایه‌ی 12 خانه‌ای ایجاد می‌کند. کدی که در زیر آمده است یک حلقه در بین مقادیر هر سطر است که در آن از آرایه استفاده نشده است:

1Sub Test1()
2Dim x As Integer
3intNumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
4Range("A1").Select
5For x = 1 To intNumRows
6If Range("A" & x).Value < 100 Then
7intTemp = (Range("A" & x).Value) * 32 - 100
8End If
9ActiveCell.Offset(1, 0).Select
10Next
11End Sub

در این مثال، وظیفه‌ی کد این است که در تمامی سلول‌های موجود پیمایش کرده و دما را محاسبه کند. با این روش، اگر در ادامه‌ی برنامه در جایی بخواهید همین محاسبات را انجام دهید، باید مجددا این کار را تکرار کرده، در تمامی سلول‌ها پیمایش نموده و مقادیر را محاسبه کنید. حال اگر در همین کد از آرایه‌ها استفاده کرده بودیم، می‌توانستیم 12 مقدار سطر را در آرایه‌ی مورد نظر خود ذخیره کنیم، سپس هر جایی که نیاز به محاسبه‌ی مجدد این مقادیر داشتیم، آن‌ها را از آرایه‌ی خود فراخوانی می‌کردیم.

1Sub Test1()
2Dim x As Integer
3intNumRows = Range("A1", Range("A1").End(xldown)).Rows.Count
4Range("A1").Select
5For x = 1 To intNumRows
6arrMyArray(x-1) = Range("A" & x).value)
7ActiveCell.Offset(1, 0).Select
8Next
9End Sub

دلیل استفاده از «x-1» در کد بالا این است که حلقه‌ی ما از مقدار 1 شروع شده است، در حالی که شماره خانه‌ی آرایه‌ها از صفر شروع می‌شوند. زمانی که آرایه‌ی خود را آماده کرده باشید، در هر جای دیگر برنامه می‌توانید با استفاده از همان آرایه، محاسبات مد نظرتان را انجام دهید.

1Sub TempCalc()
2For x = 0 To UBound(arrMyArray)
3arrMyTemps(y) = arrMyArray(x) * 32 - 100
4Next
5End Sub

کدی که در مثال بالا آمده است، در تمامی سطرهای آرایه چرخیده (شماره‌ی تعداد خانه‌های آرایه توسط تابع «UBound» به دست می‌آید)، محاسبات دما را انجام داده و نتیجه را در آرایه‌ی دیگری به نام «arrMyTemps» ذخیره می‌کند. به وضوح مشخص است که کد محاسباتی دوم چقدر سبک‌تر و ساده‌تر از کد اولیه است. از این پس دقت کنید هر کجا خواستید محاسبات تکراری برروی داده‌های تکراری انجام دهید، از این روش استفاده کنید تا آرایه‌ی شما همیشه آماده و در دسترس باشد.

4. استفاده‌ی بیش از حد از مراجع مختلف

چه در نسخه‌ی کامل ویژوال بیسیک برنامه می‌نویسید یا در VBA، برای دسترسی به برخی ویژگی‌ها نظیر دیتابیس اکسس یا نوشتن برخی از مقادیر در یک فایل متنی، نیاز به استفاده از مراجع (References) مختلف خواهید داشت.

فیلم آموزش آشنایی با برنامه نویسی VBA برای خودکار کردن برنامه های Office (رایگان) در فرادرس

کلیک کنید

مراجع به مانند همان «کتابخانه‌ها» (libraries) می‌مانند که اگر از آن‌ها استفاده کنید به توابع خاصی دسترسی پیدا خواهید کرد. برای استفاده از این مراجع، باید از منوی «Tools» گزینه‌ی «References» را انتخاب کنید.

در این صفحه لیستی از مراجع استفاده شده در پروژه‌ی فعلی را مشاهده خواهید کرد. این لیست در هر رایانه متفاوت است. به طور کلی نحوه‌ی استفاده‌ی شما از اکسل، ویژگی‌ها و افزونه‌های فعال شده یا مراجعی که قبلا استفاده کرده‌اید، همه برروی این لیست تاثیر می‌گذارند.

از آن جایی که مراجع اضافی و غیر ضروری منابع سیستم را تلف می‌کنند، بررسی کردن این لیست می‌تواند بسیار مفید باشد. برای مثال اگر هیچ کاری با فایل‌های «XML» ندارید، چرا باید «Microsoft XML» را فعال کرده باشید؟ یا اگر هیچ فعالیتی ندارید که به دیتابیس وابسته باشد، می‌توانید «Microsoft DAO» را حذف نمایید. اگر هم هیچ فایل متنی در خروجی تولید نمی‌کنید، واقعا نیازی به «Microsoft Scripting Runtime» ندارید.

اگر به این مراجع و عملکرد آن‌ها آشنایی ندارید، دکمه‌ی «F2» را بزنید تا وارد «Object Explorer» شوید. در بالای این پنجره می‌توانید کتابخانه‌ی مرجع را انتخاب کنید تا بتوانید اطلاعات آن را مرور نمایید.

هر گزینه‌ای که در این صفحه انتخاب کنید، لیست تمام اشیاء و توابع آن را مشاهده خواهید کرد و با کلیک کردن برروی آن‌ها، توضیحات آن برای شما نمایان خواهد شد. برای مثال اگر کتابخانه‌ی «DAO» را برگزینید، به سرعت متوجه خواهید شد که این کتابخانه برای برقراری ارتباط با دیتیابیس‌ها استفاده می‌شود. کاهش تعداد مراجع در هر پروژه‌ای مفید است و به اجرای هرچه بهتر برنامه‌ی شما کمک می‌کند.

نتیجه گیری

اکثر مردم از کد نویسی در اکسل ترس دارند، ولی واقعا کار ترسناکی نیست. VBA یک زبان بسیار ساده است که اگر در آن مواردی که در بالا گفته شد را رعایت کنید، قطعا کد شما بدون عیب، کارآمد و قابل فهم خواهد بود.

امیدواریم که این مطلب برای شما مفید بوده باشد. مطالب زیر نیز در مورد برنامه‌نویسی و اکسل به شما پیشنهاد می‌شود:

• گنجینه آموزش های اکسل (Microsoft Excel)
• آموزش‌های مجموعه نرم‌افزاری آفیس
• آموزش برنامه نویسی VBA در اکسل

#