ساخت فهرست شیتها در اکسل با استفاده از کد vba

این ماژول اسامی شیتهای موجود در فایلتان را بصورت فهرست لینک دار در یک شیت جدید ایجاد خواهد کرد کافی است در اکسل به محیط ویژوال بیسیک رفته و فایل را وارد نمائید . با اجرای ماکرو فهرست ساخته خواهد شد.

Sub Build_Sheet_Navigator_with_Goto_Button()
   ' On Error Resume Next
    Application.DisplayAlerts = False
    Application.DisplayAlerts = False

    Call Insert_Navigator_WorkSheet    'Inset Sheet_Navigator worksheet
    Call DeleteAllShapes   'first remove pervious Button
    Call Insert_Goto_Home_Button  ' Insert Buttons
    Call ShapePrint   'Does not Print Button
   
    Worksheets("Sheet_Navigator").Shapes("HomeBtn").Delete
    Worksheets("Sheet_Navigator").Range("A2").Clear
    Worksheets("Sheet_Navigator").Range("A2") = "فهرست مطالب"
    Worksheets("Sheet_Navigator").Activate

    Application.DisplayAlerts = False
    Application.ScreenUpdating = True


End Sub
Private Sub Insert_Goto_Home_Button()

    On Error Resume Next

    For Each sh In Worksheets

        With sh.Shapes.AddShape(msoShapeRectangle, 2, 2, 45, 15)
            .Name = "HomeBtn"
            .Fill.ForeColor.RGB = RGB(255, 0, 0)
            .TextFrame.Characters.Text = "Home"
            .Line.Visible = False
        End With

        sh.Hyperlinks.Add Anchor:=sh.Shapes("HomeBtn"), Address:="", SubAddress:="Sheet_Navigator!A1", ScreenTip:="Click Here to go Sheet_Navigator Worksheet"

    Next

End Sub
Private Sub Insert_Navigator_WorkSheet()

    On Error Resume Next


    Worksheets("Sheet_Navigator").Delete
    Worksheets.Add(Sheets(1)).Name = "Sheet_Navigator"


    For Each sh In Worksheets
        i = i + 1
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 1, 1), Address:="", SubAddress:="'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
    Next

    With Columns("A:A")
        .EntireColumn.AutoFit
        HorizontalAlignment = xlLeft
    End With



End Sub
Private Sub DeleteAllShapes()

    On Error Resume Next

    For Each sh In Worksheets
        sh.Shapes("HomeBtn").Delete
    Next

End Sub
Private Sub ShapePrint()

'This Procedure set button print property to false which Button does not print

    On Error Resume Next
    Application.ScreenUpdating = False

    For Each sh In Worksheets
        sh.Activate
        sh.Shapes("HomeBtn").Select
        Selection.PrintObject = False
        sh.Cells(1, 1).Activate
    Next

End Sub

تبدیل متن به عدد در اکسل به کمک VBA

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

میخواهیم کل ستون هارا به فورمت عدد تبدیل کنیم.

کد زیر را استفاده میکنیم:

Sub ConvertTextToNumber()
Dim ws As Worksheet
Dim r As Range
For Each ws In Sheets
On Error Resume Next
For Each r In ws.UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then r.Value = Val(r.Value)
Next
Next
End Sub

IF های تو در تو در اکسل

میخواهیم در نوشتن فرمول زیر از ساختار if های تو در تو استفاده کنیم، به مساله دقت کنید و تلاش کنید آن را بنویسید.

مقدار سلول A13 برابر است با:

• A: اگر مقدار سلول A1 از ۰ تا ۲۰ باشد.
• B: اگر مقدار سلول A1 از ۲۰ تا ۴۹ باشد.
• G: اگر مقدار سلول A1 از ۵۰ تا ۸۴ باشد.
• E: اگر مقدار سلول A1 از ۸۵ تا ۱۰۰ باشد.

در مساله ی بالا، در سلول A13 به تناسب اینکه چه عددی بین ۰ تا ۱۰۰ در سلول A1 نوشته شده باشد پیامی درج میگردد. با استفاده از ساختار if های تو در تو میتوان فرمول زیر را نوشت:

IF(A1>=50;IF(A1>=85;”E”;”G”);IF(A1>=20;”B”;”A”))

در فرمول بالا بجای آرگومان دوم و سوم از توابع if استفاده شده، حال در فرمول بالا در هریک از آرگومان ها دوم و سوم برای ریز تر کردن بازه ها میتوان مجدداً از ساختارهای if دیگر نیز استفاده کرد و به همین ترتیب تا تعداد ۶۴ تابع if تو در تو میتوان در فرمول نویسی تابع if در اکسل لحاظ نمود.

رمز گذاری بر روی vba

برای اینکه بخواهید محیط ویژوال بیسیک (vba) فایل اکسل خود را رمزگذاری کنید به این روش عمل کنید :

ابتدا پس از باز کردن فایل اکسل و زدن کلیدهای ترکیبی (alt+F11) ، وارد منوی tools شده و گزینه VBAProject Properties... را انتخاب کنید

پس از باز شدن پنجره زیر ، بر روی تب protection کلیک کنید
سپس با تیک زدن بر روی lock project for viewing و در کادرهای فرم ، رمز عبور مورد نظر را وارد کنیدپس از کلیک کردن بر روی دکمه ok ، فایل اکسل را ذخیره و فایل را ببندید. پس از باز کردن فایل اکسل و زدن کلید های ترکیبی (alt+F11)  ، کادری نمایش داده می شود که با وارد کردن رمز عبور خود وارد محیط vba خواهید شد

پاک کردن اطلاعات شیت با استفاده از کد vba

ابتدا به تب developer رفته و از گزینه زیر یک دکمه بر روی صفحه اکسل خود درگ کنید.

سپس با زدن دکمه new بر روی پنجره ای که باز شده است ، وارد محیط vba اکسل شوید و کد زیر را در رویداد کلیک دکمه کپی کنید :

Dim soal As Integer

("آیا شما می خواهید اطلاعات داخل شیت را خالی کنید؟", vbYesNo + vbQuestion, "خالی کردن اطلاعات")

If soal = vbYes Then

    Cells.ClearContents

Else

بعد از خارج شدن از محیط vba بر روی دکمه ایجاد شده کلیک کنید و با باز شدن پنجره سوال و انتخاب دکمه yes تمام اطلاعات داخل شیت پاک خواهد شد.