وبلاگ

در آموزش قبل با مجموعه Worksheets در VBA آشنا شدیم. در این آموزش می خواهیم یاد بگیریم که چطور با استفاده از این مجموعه می توانیم یک ماکرو برای افزودن یک صفحه جدید (sheet) به اکسل با استفاده از VBA بنویسیم.

مدت زمان تخمینی مطالعه: 8 دقیقه

متد Add مجموعه Worksheets

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

یکی از این متدها Add می باشد که برای افزودن صفحه جدید (sheet) به شیء workbook یک فایل اکسل کاربرد دارد.

مستندات متد Add مجموعه Worksheets

دستور متد Add

expression.Add (Before, After, Count, Type)

expression در کد بالا بایستی یک شیء باشد که به مجموعه Worksheets اشاره داشته باشد.

دستور متد Add از قسمت های زیر تشکیل شده است.

مقدار بازگشتی متد Add مجموعه Worksheets

شیء ای که نماینده صفحه اضافه شده می باشد.

نوشتن ماکروی افزودن صفحه جدید (sheet) به اکسل

حالت اول :‌ افزودن صفحه به فایل اکسل جاری با استفاده از VBA

این حالت زمانی کاربرد دارد که می خواهید در فایلی که ماکرو را در آن می نویسید یک صفحه جدید اضافه نمایید.

بخش اول : تعیین متغیر workbook

تعیین متغیری که مشخص کننده workbook باشد. برای این کار کد زیر را به ماکروی خود اضافه نمایید.

Dim wb As Workbook
Set wb = ThisWorkbook

در خط اول متغیر موردنیاز را اعلان کردیم و در خط دوم مرجع متغیر را با استفاده از دستور Set روی ThisWorkbook (که نماینده فایل اکسل ماکرو می باشد) تنظیم کرده ایم. دقت داشته باشید که هر جا که نوع متغیر یک نوع شیء باشد نمی توانیم متغیر را با استفاده از عملگر = مقدار دهی یا assign نماییم. در این موارد حتما بایستی از دستور Set استفاده نماییم.

بخش دوم : افزودن صفحه جدید

حالا با استفاده از خاصیت Worksheets شیء ThisWorkbook که توسط متغیر wb به ارث برده شده است می توانیم کاربرگ جدید را اضافه نماییم.

wb.Worksheets.Add

کد بالا ساده ترین روش برای افزودن یک صفحه جدید به اکسل می باشد. اگر ماکرو را در این حالت اجرا نمایید، صفحه جدید قبل از صفحه فعال فایل اکسل اضافه خواهد شد.

بخش سوم : افزودن صفحه در مکان تعیین شده

برای اینکه بتوانیم محل صفحه جدید را تعیین نماییم بایستی یکی از آرگومان های Before یا After را تکمیل کنیم. برای اینکار یک متغیر جدید نیاز داریم که با استفاده از آن به VBA بگوییم که منظور ما کدام صفحه می باشد.

Dim sh As Worksheet
Set sh = wb.Worksheets("iranvba")

متغیر sh در کد بالا از نوع Worksheet می باشد که کلاسی است که در VBA مشخصات و متدهای صفحه اکسل را نگهداری می نماید. در خط اول کد بالا ما به VBA می گوییم که یک کپی از کلاس Worksheet نیاز داریم. در خط دوم تعیین کرده ایم که متغیر sh به صفحه ای به نام iranvba در شیء workbook به نام wb که فایل اکسل فعلی می باشد اشاره دارد.

اگر بخواهیم قبل از صفحه iranvba صفحه جدید اضافه شود متد Add را به شکل زیر استفاده می نماییم:

wb.Worksheets.Add sh

اگر بخواهیم بعد از صفحه iranvba صفحه جدید اضافه شود متد Add را به شکل زیر استفاده می نماییم:

wb.Worksheets.Add After:=sh

بخش چهارم: افزودن بیش از یک صفحه با VBA

برای افزودن بیش از یک صفحه به اکسل با استفاده از VBA کافیست از آرگومان Count متد Add به شکل زیر است کنیم:

wb.Worksheets.Add After:=sh, Count:=5

کد بالا 5 صفحه بعد از صفحه iranvba اضافه خواهد کرد.

بخش پنجم : کاربرد مقدار بازگشتی متد Add در تعیین مشخصات صفحه جدید

همانطور که در مستندات متد Add عنوان شد، مقدار بازگشتی این متد شیء صفحه اضافه شده می باشد. بنابراین مقدار بازگشتی متد را می توانیم در یک شیء Worksheet ذخیره نماییم. برای اینکار به یک متغیر جدید نیاز داریم.

Dim newSh As Worksheet
Set newSh = wb.Worksheets.Add(After:=sh)
newSh.Name = "vba is fun"

در کد بالا خط اول متغیر newSh متغیری است که می خواهیم شیء صفحه جدید را در آن نگهداری نماییم. در خط دوم مرجع متغیر جدید را مقدار بازگشتی متد Add تعیین کرده ایم. با توجه به اینکه مقدار بازگشتی متد از نوع شیء می باشد، مقدار آن باید در متغیر newSh با استفاده از دستور Set تعیین یا assign شود.

در خط سوم نام صفحه جدید را با استفاده از خاصیت نام کلاس Worksheet تعیین نموده ایم.

اگر در خط دوم یکبار دیگر دقت کنید متوجه تفاوت آن با وقتی که از مقدار بازگشتی متد Add استفاده نکردیم خواهید شد. در روش اول از پرانتر استفاده نکردیم ولی در روش دوم از پرانتز برای بستن محدوده آرگومان های متد استفاده کردیم.

این یکی از خصوصیات ذاتی VBA می باشد. وقتی که می خواهید یک تابع یا متد را فقط فراخوان کنید و به مقدار بازگشتی آن نیازی ندارید نباید از پرانتزها برای محدوده آرگومان ها استفاده کنید ولی وقتی که می خواهید مقدار بازگشتی تابع یا متد را در یک متغیر ذخیره نمایید حتماً باید از پرانتزها استفاده کنید.

بخش ششم: ماکروی کامل

Sub addNewSheet()
Dim wb As Workbook
Dim sh As Worksheet
Dim newSh As Worksheet

    Set wb = ThisWorkbook
    Set sh = wb.Worksheets("iranvba")
    Set newSh = wb.Worksheets.Add(After:=sh)
    newSh.Name = "vba is fun"
End Sub

حالت دوم :‌ افزودن صفحه به فایل اکسلی غیر از فایل جاری با استفاده از VBA

در این حالت همه مراحل شبیه حالت قبل می باشد به جز اینکه در مرحله اول باید متغیر workbook روی فایل اکسل مدنظر تعیین شود.

Dim wb As Workbook
Set wb = Excel.Workbooks.Open(ThisWorkbook.Path & "/test-2.xlsx")

در خط دوم کد بالا مرجع متغیر wb را مقدار بازگشتی متد Open مجموعه Workbooks شیء Excel تعیین کرده ایم. مجموعه Workbooks همانند مجموعه Worksheets عمل می نماید با این تفاوت که کتاب کارهای اکسل را مدیریت و نگهداری می نماید.

ThisWorkbook.Path در خط دوم مسیری است که فایل فعلی اکسل در آن ذخیره شده است، بنابراین عبارت ThisWorkbook.Path & "/test-2.xlsx" فایل اکسلی به نام test-2.xlsx می باشد که در مسیر کتاب کار فعلی ذخیره شده است.

ماکروی کامل حالت دوم

Sub addNewSheetToAnotherFile()
Dim wb As Workbook
Dim newSh As Worksheet

    Set wb = Excel.Workbooks.Open(ThisWorkbook.Path & "/test-2.xlsx")
    Set newSh = wb.Worksheets.Add
    newSh.Name = "vba is fun"
End Sub

دانلود فایل اکسل آموزش