چطور به کمک برنامه نویسی VBA محدوده ها را در اکسل مدیریت کنیم ؟
سلام به یکی دیگر از سری آموزش های سایت سافت پلاس خوش آمدی . امروز در زمینه برنامه نویسی VBA در اکسل می خواهیم راجع به این قضیه با هم صحبت کنیم که “چطور به کمک برنامه نویسی VBA محدوده ها را در اکسل مدیریت کنیم ؟ ”
توی این آموزش اول یاد خواهی گرفت که منظور از محدوده چیست ؟
دوم یاد خواهی گرفت که با محدوده ها چه کارهایی می شود انجام داد؟
و در آخر هم قرار است یاد بگیری که چطور به کمک برنامه نویسی و کدهای وی بی ای کار مدیریت کردن محدوده ها و اطلاعات موجود در آنها را انجام دهی .
من علیرضا شهرآئینی هستم . به اکسل و برنامه نویسی در آن علاقمندم و دوست دارم چیزهایی را که یاد می گیرم را با اطرافیان به اشتراک بگذارم .
خوشحال خواهم شد اگر تا انتهای این آموزش من را همراهی کنی و خوشحال تر خواهم شد اگر شما هم اطلاعات کاملتری در خصوص این موضوع داری را با من و بقیه خوانندگان به اشتراک بگذاری .
پس از شما دعوت می کنم که تا انتهای این آموزش همراه من باشی .
اول : باید بدانی که منظور از محدوده در اکسل چیست ؟
قرار است کار کردن با محدوده ها در اکسل را یاد بگیری . اما قبل از هر چیز باید بدانی که منظور از خود کلمه محدوده در اکسل چیست ؟ و وقتی از اصطلاحی به نام محدوده در کار با اکسل صحبت می کنیم منظورمان دقیقا چیست ؟
در اکسل ما اصطلاحی داریم به نام Range که ما آن را تحت عنوان ” محدوده ” نام گذاری می کنیم . اما منظور از محدوده چی هست ؟
همانطور که می دانی نرم افزار اکسل می تواند دارای شیت های کاری متعددی باشد . هر شیت کاری هم برای خودش دارای تعداد زیادی سلول است .
اگر بخواهم کمی دقیق تر وارد موضوع شوم هر شیت کاری از اکسل دارای 16,384 ستون و 1,048,576 ردیف است .
حالا کافی است همین دو تا عدد را در هم ضرب کنی تا دست آخر به عدد 17,179,869,184 برسی . این می شود تعداد سلول های موجود در یک شیت کاری از اکسل .
فکرش را بکن فقط توی یک شیت از اکسل بیشتر از 17 میلیارد سلول وجود دارد .
به نظر که دود از کله آدم بلند می شود . 17 میلیارد سلول فکرش را بکن .
محال ممکن است که در اکسل بخواهی روی یک شیت کار کنی و از همه 17 میلیارد سلول آن بخواهی استفاده کنی . پس چه کار می کنی ؟
خب معلوم است فقط یک تعداد مشخص از این سلول ها را برای کار انتخاب می کنی .
حالا این تعداد خاص می تواند فقط شامل یک سلول باشد . می تواند شامل 10 یا 100 یا 1000 و یا حتی چند میلیون سلول باشد .
اینجا دیگر تعداد مهم نیست هر تعداد سلولی را که برای کار در یک شیت کاری انتخاب می کنی و در داخل آنها اطلاعات مورد نظر خودت را وارد می کنی همان تعداد از سلول ها می شوند ” محدوده ” کاری شما .
پس وقتی ما صحبت از محدوده یا همان Range در اکسل می کنیم می تواند منظورمان فقط یک سلول باشد یا هزاران هزار سلول که با آنها کار می کنیم .
حالا که محدوده مورد نظرت را انتخاب کردی می توانی هر کاری که مد نظر داری را انجام دهی مثلا :
– می توانی اطلاعات خودت را در داخل آنها وارد کنی
– می توانی رنگ پس زمینه سلولهای محدوده را تغییر دهی
– می توانی در داخل سلول های آن فرمول های محاسباتی را وارد کنی
– می توانی در داخل سلول ها یادداشت گذاری کنی
و خیلی از کارهای دیگری که در کار با اکسل می شود با محدوده ها انجام داد .
اما همه این کارها را چطور می توان انجام داد .
خب همه این کارها را از دو طریق می شود انجام داد .
یکی اینکه همه این کارها را و هر تغییری را که مد نظرمان است در محدوده ها صورت بگیرد را خودمان و بصورت دستی انجام بدهیم .
راه و روش دوم هم این است که همه این کارها و تغییرات در سطح محدوده ها را به کمک برنامه نویسی VBA انجام دهیم .
روش اول که یک روش عادی و مرسوم است و همه دارند از ان استفاده می کنند . اما ما امروز می خواهیم بصورت اختصاصی با روش دوم کار کنیم .
اینکه چطور محدوده یا محدوده های مورد نظر را با استفاده کد نویسی وی بی ای مدیریت کنیم .
یک پیشنهاد خواندنی : ذخیره نمودارها با کدهای VBA بصورت تصویر +راهنمای گام به گام
مدیریت محدوده ها توسط برنامه نویسی VBA
منظور از مدیریت کردن محدوده ها در اکسل به کمک برنامه نویسی وی بی ای چیست ؟
مدیریت کردن یعنی اینکه بتوانی کاری کنی که به کمک کدهای VBA محدوده سلول های مورد نظرت را کنترل کنی .
یعنی بتوانی کاری کنی که به جای اینکه خودت بصورت دستی اطلاعاتی را وارد کنی ، کدهایی را بنویسی که وارد عمل شده و سه تا کار زیر را برایت بصورت کاملا خودکار انجام دهند :
1- اول اینکه باید کدهایی را بنویسی که به کمک آنها بشود اطلاعات مورد نظرت را در داخل سلول های یک محدوده وارد کنی .
2- باید کدهایی را بنویسی که با استفاده از آنها بتوانی اطلاعاتی را که از قبل در داخل یک محدوده از سلول ها ثبت شده اند را بخواند .
3- باید کدهایی را بنویسی که به کمک آنها بشود تغییراتی را که مد نظر داری در محدوده انتخاب شده اعمال نماید .
اما قبل از این که بتوانی همه این کارها را انجام دهی لازمه اش این است که یک کار خیلی مهم را انجام دهی . آن یک کار مهم چیست ؟
آن کار این است که بتوانی به آن محدوده یا محدوده های مورد نظرت از طریق کد نویسی دسترسی پیدا کنی . در ادامه بیشتر راجع به این قضیه صحبت می کنیم .
اهمیت دسترسی به محدوده ها در اکسل
چرا این قدر دسترسی به محدوده ها به کمک کدهای وی بی ای در اکسل مهم است .
اجازه بده یک مثال عینی را با هم بررسی کنیم .
فرض کن که می خواهی که سقف اتاقت را رنگ آمیزی کنی .
برای این کار چه می کنی .
مطمئنا اول از همه به سراغ رنگ فروشی رفته و یک سطل رنگ به همراه یک یا چند تا قلم مو و یا غلطک رنگ آمیزی را تهیه می کنی .
یک مطلب خواندنی دیگر : 7 اشتباه در کد نویسی VBA که ممکن است شما هم مرتکب شوید .
بعد که همه چیز را فراهم کردی حالا پای کار می آیی . برای اینکه بتوانی کار رنگ آمیزی سقف اتاقت را انجام دهی اولین کار این است که به هر نحوی شده به آن دسترسی پیدا کنی .
خب برای این کار روش ها ی مختلفی وجود دارد که می توانی از آنها استفاده کنی .
مثلا می توانی :
1- از یک چهار پایه استفاده کنی .
2- از یک نردبان استفاده کنی .
3- از یک داربست استفاده کنی .
4- یا اینکه می توانی از یک غلطک با طول دسته زیاد استفاده کنی .
و یا از هر روش دیگر و یا وسیله دیگری که به فکرت می رسد می توانی برای دسترسی به سقف استفاده کنی .
متوجه یک قضیه شدی ؟
اینجا هدف یک چیز است . ” دسترسی به سقف ”
اما برای رسیدن به این هدف نه یک راه بلکه چند تا راه وجود داشت . یعنی می توانی از هر کدام از این راهها که فکر می کنی برایت ساده تر است برای رسیدن و دستیابی به سقف استفاده کنی .
وقتی که یکی از راهها را انتخاب کردی حالا به سقف دسترسی داری و می توانی سقف را به رنگ مورد نظرت در بیاوری .
همه این داستانی که در دنیای واقعی ما قرار دارد عین آن در دنیای برنامه نویسی وی بی ای و کار با محدوده ها هم عینا وجود دارد .
قضیه از این قرار است :
– فرض کن که می خواهی رنگ سلول های موجود در محدوده سلول های A1 تا D15 را که در شیت کاری شماره 2 از یک فایل اکسل وجود دارند را با استفاده از کد نویسی به رنگ آبی در بیاوری .
خب اینجا هم دقیقا اولین کاری که باید انجام دهی این است که با استفاده از کد نویسی به محدوده سلول های مورد نظرت دسترسی پیدا کنی .
به خاطر داری که برای دسترسی به سقف اتاق نه یک روش که چندین روش وجود داشت .
اینجا هم قضیه درست به همین منوال است .
یعنی برای اینکه به یک محدوده از سلول ها دسترسی پیدا کنی نه از یک روش که می توانی از چندین روش استفاده کنی .
روش هایی که همه آنها را با هم در ادامه همین آموزش بررسی می کنیم .
پس در ادامه با من همراه باش .
انتخاب یک سلول در شیت جاری
اول از ساده ترین حالت ها شروع می کنیم .
فرض کن که داخل نرم افزار اکسل و در sheet1 مشغول به کاری . یعنی در حال حاضر از بین شیت های مختلفی که در اکسل وجود دارد sheet1 بصورت فعال است .
حالا می خواهی به کمک کد نویسی VBA سلول A1 در این شیت کاری را انتخاب کرده و یک سری از کارها را در داخل آن انجام دهی .
برای این کار می توانی از کدهای زیر استفاده کنی .
Application.Workbooks(1).Worksheets(1).Range("a1").Select
اجازه بده کمی این کد ها را بررسی کنیم .
این کدها با کلمه کلیدی Application شروع می شود . Application در واقع اشاره به نرم افزار اکسل می کند .
بعد از کلمه Application به کلمه workbooks می رسیم .
ورک بوک در واقع همان فایل های اکسل هستند . از آنجا که ممکن است در آن واحد چند فایل اکسل توسط کاربر باز شود اکسل برای اینکه بهتر بتواند آنها را شناسایی کند شروع به شماره گذاری آنها می کند .
به این شماره در اصطلاح ایندکس می گویند .
مثلا وقتی سه فایل اکسل باز هستند و ما در کدهای خود از عبارت(1) workbooks نرم افزار متوجه می شود که از بین سه فایلی که در حال حاضر باز هستند با فایل شماره 1 کار داریم .
حالا می رسیم به کلمه Worksheets .
هر فایل اکسل می تواند دارای تعداد زیادی شیت باشد باید برای برنامه باید مشخص کنیم که دقیقا به کدام یک از شیتها کار داریم .
مثلا اینجا به برنامه گفته ایم که با شیت شماره 1 کار داریم .
حالا که به شیت شماره دسترسی پیدا کردیم باید مشخص کنیم که از بین سلول های موجود در آن دقیقا با کدام سلول کار داریم .
اینجا سلول A1 را با استفاده از متد Range به برنامه معرفی کرده ایم .
وقتی که از طریق کد نویسی به سلول مورد نظر رسیدیم با خیال راحت می توانیم هر تغییر و یا هر کاری را در این سلول انجام دهیم .
مثلا فرض کنید که می خواهیم رنگ پس زمینه سلول برای ما به رنگ آبی در بیاید .
برای این کار از کدهای زیر استفاده می کنیم .
Application.Workbooks(1).Worksheets(1).Range("a1").Select
Selection.Interior.Color = vbBlue
اینجا برای دسترسی به یک سلول خاص از اکسل از متد Range استفاده کردیم .
اما یک سوال . آیا این تنهاترین روشی است که به کمک آن می توانیم به یک سلول خاص دسترسی پیدا کنیم ؟
مسلما پاسخ این سوال خیر است .
در کنار این روش ، هستند روش های دیگری که به کمک آنها هم می شود به یک سلول در اکسل دسترسی پیدا کرد .
Application.Workbooks(1).Worksheets(1).cells(1,1).Select
Selection.Interior.Color = vbBlue
این بار برای دسترسی به سلول A1 در شیت کاری مورد نظر از متد Cells استفاده کردیم .
خروجی این کدها هیچ تفاوتی نسبت به کدهای قبلی ندارد چون در هر دو حالت ما به یک سلول خاص دسترسی پیدا کرده و یک کار واحد را انجام دادیم .
یا مثلا کدهای زیر هم هر دو یک خروجی را دارند .
ActiveSheet.Cells(3, 2).Select
یــــا
ActiveSheet.Range("B3").Select
تا به اینجای کار ما با استفاده از دو متد سلول هایی را انتخاب کردیم که همه در یک شیت کاری مشخص در یک فایل اکسل بودند .
اما فرض کنید که در حال حاضر یک فایل اکسل داریم که سه تا شیت کاری در داخل آن وجود دارد .
می خواهیم ببینیم که چطور می شود وقتی که در یک شیت کاری هستیم ( مثلا شیت شماره 1 ) به یک سلول در داخل شیت دیگر دسترسی پیدا کرده و در داخل آن تغییرات مورد نظر خودمان را ایجاد کنیم .
آیا اصلا این کار شدنی هست یا نه ؟
انتخاب یک سلول در شیت دیگر
برای اینکه بتوانیم از یک شیت خاص به سلول موجود در شیت کاری دیگر دسترسی پیدا کنیم باید از کدهایی مثل کد زیر استفاده کنیم .
فرض کنید که می خواهیم به سلول D5 از شیت شماره 3 دسترسی پیدا کنیم .
Application. ActiveWorkbook.Sheets("Sheet2").Cells(5, 4)
یا
Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E5"))
خروجی هر دو خط کد بالا دقیقا با هم یکی خواهد بود و در هر دو حالت ما به سلول E5 در داخل شیت 2 دسترسی پیدا می کنیم .
ما اینجا به سلول مورد نظر خود در داخل شیت کاری 2 دسترسی پیدا کردیم بدون اینکه اصلا شیت مورد نظر را فعال کنیم .
اما کدهای زیر اول شیت مورد نظر را برای ما فعال کرده و بعد سلول مد نظر را برای ما انتخاب می کند .
Sheets("Sheet2").Activate
ActiveSheet.Cells(5, 4).Select
خب تا به حال فقط راجع به انتخاب یک سلول صحبت کردیم . حالا این یک سلول یا در شیت جاری قرار داشت یا در شیت های دیگر .
اما فکرش را بکنید که برای دسترسی به چند تا سلول باید چه کار کنیم ؟
در ادامه با من همراه باشید تا راجع به این قضیه هم با هم صحبت کنیم .
دسترسی و کار با محدوده ها در اکسل
در اکسل برای اشاره به چند سلول از اصطلاح محدوده یا Range استفاده می کنیم .
حالا با هم یاد می گیریم که چطوری می شود به یک محدوده از سلول ها در اکسل دست پیدا کنیم .
1- دستیابی به یک محدوده در داخل شیت جاری
فرض کنید که می خواهیم به محدوده سلول های A1 تا C12 در شیت جاری دسترسی پیدا کنیم .
برای این کار می توانیم از کدهایی مثل کد زیر استفاده کنیم .
Range("A1:C12").Select
کد بالا می تواند سلول هایی را که در مجاورت همدیگر قرار دارند را برای ما انتخاب کند . اما اگر محدوده سلول های ما در کنار هم نبودند تکلیف چیست ؟
مثلا به تصویر زیر نگاه کنید .
در حالت عادی وقتی که می خواهیم چند محدوده از سلول ها را در اکسل انتخاب کنیم کافی است اول محدوده مورد نظر را به حالت انتخاب دربیاوریم بعد دکمه CTRL را نگه داشته و بعد محدوده یا محدوده های بعدی را انتخاب کنیم .
اما در برنامه نویسی وی بی ای چه جوری می شود یک محدوده از سلول هایی غیر مجاور را انتخاب کرد ؟
برای این منظور می شود از کدهایی مثل کدهای زیر استفاده کرد
Range("A1:A9, B11:B18","D5","E4").Select
این مطلب ادامه دارد …………………
مطالب زیر را حتما مطالعه کنید
ذخیره نمودارها با کدهای VBAبصورت تصویر+راهنمای گام به گام
لیست باکس ها در برنامه نویسی VBA ؛ تمام چیزی که باید بدانید .
۷ اشتباه در کدنویسی VBA که ممکن است شما هم مرتکب شوید ؟
توی این آموزش راجع به اشتباهاتی که ممکن است در برنامه نویسی VBA مرتکب آنها شوید صحبت می کنیم .
با این خطای Runtime Error 1004 در VBA چکار کنیم ؟
در این آموزش راجع به خطایی به اسم Run time error 1004 در برنامه نویسی وی بی ای با هم صحبت می کنیم .
دیدگاهتان را بنویسید