שימוש בפונקציות Excel



4 שיעורים ראשונים : צהוב

שיעור 5 ורוד

שיעור 6 תכלת

שיעור 7 ירוק

שיעור 8 אפור

הפעלת VBA:

Tools ( Macro ( Visual Basic Editor

בעורך של VBA כותבים מאקרו ופונקציות בתוך מודול. לפתיחת מודול:

Insert ( Module

לקבלת עזרה על מילה/הוראה מעמידים את הסמן על המילה ומקישים על 1F

חוקים לשמות ב-VBA: שמות חייבים להתחיל באות ולהכיל אותיות ו/או ספרות ו/או קו תחתון.

מבנה פונקציה המוגדרת ע"י המשתמש

Function name (arglist)

[statements]

[name = expression]

[Exit Function]

[statements]

[name = expression]

End Function

כל שורה היא הוראה.

דוגמאות לפונקציות ללא פרמטרים:

Function myId()

myId = 123456

End Function

Function myName()

myName = "Yosi"

End Function

הגדרת משתנים

Dim as

Dim x as integer

סוגי משתנים

Integer, Double, String, Boolean, Object, Range ,Worksheet, Workbook, Variant….

הצבה במשתנים פשוטים:

ערך = שם משתנה

5 = X

נראות משתנים

משתנים גלובליים מוגדרים ברמת המודול ויכולים לשמש את כל המקרואים באותו מודול.

ניתן להגדיר משתנים שיהיו מוכרים לכל המודולים בחוברת ע"י הגדרת המשתנה בעזרת המילהpublic במקום המילה dim לדוגמא:

public a as integer

משתנים סטטיים

ניתן להגדיר משתנים ישמר החל מההפעלה הראשונה של המקרו ועד ליציאה מהאפליקציה או סגירת הקובץ ע"י הגדרת המשתנה בעזרת המילהstatic במקום המילה dim לדוגמא:

Static count As Integer

אופרטורים מתימטיים:

אם b =5 ו-c=2

|אופרטור |תאור |דוגמא |תוצאה |

|+ |חיבור |a = b + c |7 |

|- |חיסור |a = b - c |3 |

|* |כפל |a = b * c |10 |

|/ |חילוק |a = b / c |2.5 |

|\ |חילוק בשלמים |a = b \ c |2 |

|mod |שארית בחילוק בשלמים |a = b mod c |1 |

|^ |חזקה |a = c ^ 3 |8 |

סדר קדימויות מהנמוך לגבוה: חיבור וחיסור, כפל וחילוק, העלאה בחזקה.

ניתן ליצור ביטויים מורכבים ולהוסיף סוגריים לפי הצורך.

אופרטור שרשור: & לדוגמא, שרשור שני טקסטים וביניהם רווח:

connect = str1 & " " & str2

ניתן להוסיף הערה אחרי גרש בודד: '

דוגמאות לפונקציות המקבלות פרמטרים:

Function sqr(num) ' returns the square of num

sqr = num ^ 2

End Function

ניתן להמשיך הוראה בשורה הבאה באמצעות רווח וקו תחתון ו ניתן לכתוב שתי הוראות באותה שורה אם כותבים ביניהן נקודתיים.

Function sqr1(num)

' How to split an instruction into 2 lines

sqr1 = _

num ^ 2

End Function

אפשר ורצוי להגדיר את סוגי הפרמטרים:

Function sqr (num As Double)

' declaring type of parameter

sqr = num ^ 2

End Function

אפשר להגדיר את סוג הערך המוחזר ע"י הפונקציה:

Function sqr (num as Double) As Integer

' declaring the type of answer the function returns

sqr = num ^ 2

End Function

שימוש בפונקציות קיימות באקסל וב-vba

שימוש בפונקציות Excel

WorksheetFunction.nameOfFuncion()

לדוגמא קבלת הערך המקסימלי:

WorksheetFunction.max(10,45,3)

שימוש בפונקציות VBA

VBA.nameOfFunction()

לדוגמא, הגרלת מספר אקראי:

VBA.Rnd

פונקציה הפותחת חלון הודעה:

VBA.msgbox "text"

בהמשך נראה שניתן גם לקבל תשובה מ-msgbox

nameOfVariable = msgbox("text",buttons)

לדוגמא:

If (MsgBox("Continue?", vbYesNo) = vbYes) Then

פונקציה הפותחת חלון קלט

nameOfVariable = VBA.inputbox("What is your name")

פונקציות של מחרוזות

&

Len

UCase

LCase

Left

Right

Mid

Instr

InStrRev

פונקציות תאריך ושעה

מקיפים תאריכים בסימן #: #20/3/2010#

Date

Time

Now

פונקציות כלליות

rnd

מבנה מקרו:

Sub name ()

[statements]

[Exit Sub]

[statements]

End Sub

דוגמא למקרו:

Sub one() ' demonstrate the msgbox function and a string variable

VBA.MsgBox "Good Morning"

Dim msg As String

msg = "Good Day"

VBA.MsgBox msg

End Sub

הפעלת מקרו:

מתוך עורך VBA ע"י לחיצה על הלחצן run sub/userform או ההוראה הנ"ל מתפריט Run או המקש 5F.

מתוך excel:

בחירה בתפריט: Tools ( Macro ( Macros , בחירת המקרו הרצוי והקשה על run.

(קיימות דרכים נוספות כגון שימוש במקש קיצור, שימוש בלחצן בסרגל הכלים או לחיצה על כפתור המוצב על גיליון אקסל. עבור כל אחת מאפשרויות אלה, יש צורך בהקצאת המקרו לאפשרות הרצויה).

הפעלת מקרו בשלבים באמצעות הקשות חוזרות על המקש 8F.

אוביקטים, מאפיינים ושיטות

דוגמאות לאובייקטים באקסל: חוברת העבודה, גיליון, טווח וכו'.

האוביקטים מסודרים באופן היררכי. לדוגמא, האוביקט העליון בהיררכיה הוא האפלקציה אקסל ושמו Application , מתחתיו נמצאים אוביקטים רבים, ביניהם חוברת העבודה, ומתחתיה אוביקטים רבים ביניהם גיליון העבודה וכו'.

לדוגמא: Application.Workbooks(1).Worksheets(1).Range("a1")

השורה הנ"ל היא אחת מהאפשרויות להתיחס לתא A1 בגיליון הראשון של חוברת העבודה הראשונה.

יש אוביקטים שהם אוספים של אוביקטים, לדוגמא, workbooks או worksheets. כאשר משתמשים בשם שהוא אוסף, יש לציין לאיזה אוביקט מתוך האוסף, מתיחסים. בדוגמא הדבר נעשה באמצעות סוגריים ומספרו הסידורי של האוביקט באוסף.

משתנים שהם אוביקטים:

Dim r as Range

Dim obj as Object

הצבה של אוביקטים:

Set y= object

אם המשתנה אינו מצביע על אוביקט ערכו הוא Nothing

אפשר לשאול:

If y Is Nothing Then

ניתן לראות מה ערכו של מאפיין או לשנות אותו.

התיחסות למאפיין :

ObjectName.property

לדוגמא:

ActiveCell.Value

האוביקט הוא התא הפעיל והמאפיין הוא הערך שנמצא בו.

אפשר לראות מה ערך המאפיין:

Msgbox object.property

למשל :

msgbox activecell.Value

או לשנות אותו:

Object.property = value

למשל:

activecell.Value =5

הפעלת שיטה:

ObjectName.method

לדוגמא, העתקת הטווח הנבחר:

Selection.Copy

יש אוביקטים שניתן להתיחס אליהם רק אם מקדימים ומציינים לאיזה אוביקט הם שייכים. לדוגמא:

ActiveCell.Font.Color = vbRed

קובע שצבע הכתב בתא הפעיל יהיה אדום.

יש אוביקטים שבהתיחסות אליהם יש לספק מידע נוסף המזהה אותם:

לדוגמא, האוביקט range:

Range("a1").value = 5

מציב 5 בתא 1A.

יש שיטות שהשימוש בהם דורש מתן מידע נוסף, לדוגמא:

ActiveCell.Copy Destination:=Range("b5")

השיטה מעתיקה את התא הפעיל אל היעד שנקבע לתא 5B.

דוגמא להעברת פרמטר מסוג טווח לפונקצית אקסל:

WorksheetFunction.Sum(Range("f1:g2"))

הקלטת מקרו:

Toos menu ( Macro ( record new macro

סגנון הפניה:

באקסל לבחור בתפריט כלים, אפשרויות, לשונית כללי, לסמן סגנון הפניה R1C1

התוצאה היא שכותרות העמודות הפכו למספרים במקום עמודות.

כדאי לכתוב נוסחאות בתאים, ע"י הצבעה" ולראות שהן נרשמות באופן דומה לדוגמא הבאה:

|2 |4 |

|=R[-1]C*3 |=R[-1]C*3 |

|=R1C1*3 |=R1C1*3 |

בשורה הראשונה מופיעים מספרים.

בשתי השורות הבאות מופיעות נוסחאות. התיחסות לתאים בנוסחאות נעשית ע"י כתיבת האות R (קיצור של ROW) ואחריה מספר השורה ואחר כך האות C (קיצור של Column) ואחריה מספר העמודה.

כאשר רוצים התיחסות קבועה, כותבים את מספר השורה / העמודה האבסולוטיים. דוגמא לכך ניתן לראות בשורה השלישית שבדוגמא. R1C1 הינו התא 1A. כלומר בשני התאים כופלים את התא A1 בשלוש.

כאשר רוצים התיחסות יחסית, כותבים את הסטיה במספר השורות / העמודות בתוך סוגריים מרובעים. בשורה השניה מופיעות נוסחאות ובהן התיחסות יחסית לתא הנמצא באותה עמודה ובשורה אחת מעל.

כאשר מקליטים מקרו, ניתן לבחור הקלטה כשהכתובות של התאים הן יחסיות או קבועות ע"י לחיצה על הכפתור "הפניה יחסית".

האוביקט RANGE

ניתן לציין טווח באחת מן הדרכים הבאות:

Range("a1")

Range(fromCell,toCell) לדוגמא Range("a3","c5")

Range(fromCell:toCell) לדוגמא Range("a3:c5")

Range(nameOfRange) לדוגמא Range("Table")

האוביקט ActiveCell

האוביקט Selection

מאפיינים (rng מציין טווח כלשהו):

|דוגמא |מאפיין |

|range("a1").value = 5 |Range(rng).value |

|msgbox range("a1").formula |Range(rng).formula |

|range("a1").font.name="arial" |Range(rng). font.name |

|range("a1:c5").cells(4)=100 |Range(rng). Cells(#) |

|מציב 100 בתא הרביעי של הטווח, 2A | |

|range("a1:c5").cells(2,3)=100 |Range(rng). Cells(row#, col#) |

|מציב 100 בתא בשורה 2 עמודה 3, 2C | |

|Msgbox Range("a1:c5").count | Range(rng).count |

|מציג את מספר התאים בטווח, 15 | |

|Range("a1").font.color=vbred |Range(rng).font.color |

|קובע את צבע הכתב בתא לאדום | |

|Range("a1").interior.color =vbBlue |Range(rng).interior.color |

|קובע את צבע הרקע בתא לאדום | |

| |Range(rng). Rows.count |

| |Range(rng). columns.count |

|Range("A1").end(xldown).select |Range(rng).end |

|בוחר את התא הפנוי הראשון בסוף רצף תאים מלאים מתחת לתא 1A.| |

|אם עמודה A ריקה, יבחר בתא האחרון בעמודה A. | |

| |Range(rng).CurrentRegion |

|Msgbox range(rng).address |Range(rng).Address |

|מציג את כתובת הטווח | |

.

שיטות:

Range(rng).ClearContents

Range(rng ).Select (for one or more cells)

Range(rng ).Activate (for one cell only)

Range(rng ).Offset(row,column).Activate

Range(rng ).Offset(row, column).Select

Range(rng1).Copy Destination:=Range(rng2)

Range(rng1).Move Destination:=Range(rng2)

חלון קלט המאפשר סימון טווח בגיליון

Set nameOfVariable = Application.Inputbox("text",type:=8)

הערה: המשתנה צריך להיות מסוג אוביקט או טווח.

Objects of collections

Worksheets(#)

Worksheets(name)

פריטים של האוסף Worksheets :

ActiveSheet

Sheet

worksheet

מאפיינים:

Name

count

שיטות:

Select

Paste

Copy

Move

Workbooks(#)

Workbooks(name)

פריטים של האוסף:

ActiveWorkbook

workBook

מאפיינים:

Name

count

שיטות:

Select

Open

Close

Save

saveAs

האוביקט

Application

מאפיינים:

Application.CutCopyMode

Application.Intersect(r1, r2)

Application.Union(r1, r2)

Application.EnableEvents

שיטות:

Application.InputBox

לקבלת אוביקט מסוג טווח :

Set r = Application.InputBox("Range please", Type:=8)

מאפשר למשתמש לסמן את הטווח בגיליון.

Application.OnTime

השיטה מאפשרת לתזמן ביצוע של מקרו בזמן עתידי כפי שמוגדר בפרמטר.

דוגמאות:

מריץ את my_procedure חמש עשרה שניות אחרי ביצוע הוראה זו:

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

מריץ את my_procedure בשעה 5 אחר הצהריים:

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

מבטל את הקביעה הקודמת:

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _

Procedure:="my_Procedure", Schedule:=False

האוביקט

ActiveWindow

מאפיינים:

ActiveWindow.Zoom

מאפיין של אוביקט יכול להחזיר אוביקט אחר:

Range(rng).font

מחזיר אוביקט מסוג פונט

אפשר לעבוד עם האוביקט המוחזר

Range(rng).font.color

כאשר הצבע הוא מאפיין של הפונט שנמצא בטווח

נושא של היררכיה:

ההתיחסות היא לאוביקט הפעיל אלא אם כתוב אחרת.

Range(rng)

מתיחס לטווח הנמצא בגיליון הפעיל בחוברת הפעילה

Worksheets(2).Range(rng)

מתיחס לטווח הנמצא בגיליון השני של החוברת הפעילה

הוראות

הוראה with

With

.property

. method

End with

דוגמא:

With Range("f10").Font

.Name = "David"

.Size = 12

.ColorIndex = 55

End With

הוראת IF:

בשורה אחת: If condition Then statement Else statement

If number >= 0 Then msgbox "Positive" Else msgbox "Negative"

על פני מספר שורות:

If condition Then

statement

Else

statement

End If

If number >= 0 Then

msgbox "Positive"

Else

msgbox "Negative"

End If

הוראת if מקוננת:

If condition Then

statement

ElseIf condition Then

statement

ElseIf condition Then

statement

ElseIf condition Then

statement

Else

statement

End If

If ActiveCell.Value < 0 Then

ActiveCell.Font.Color = vbYellow

ElseIf ActiveCell.Value < 50 Then

ActiveCell.Font.Color = vbRed

ElseIf ActiveCell.Value < 85 Then

ActiveCell.Font.Color = vbBlue

ElseIf ActiveCell.Value = =

אופרטורים לוגיים: and or not

Select Case testexpression

[Case expressionlist-n

[statements-n]] ...

[Case Else

[elsestatements]]

End Select

לולאות

For counter = start To end [Step step]

[statements]

[Exit For]

[statements]

Next [counter]

For Each element In group

[statements]

[Exit For]

[statements]

Next [element]

While condition

[statements]

Wend

Do [{While | Until} condition]

[statements]

[Exit Do]

[statements]

Loop

סינטקס נוסף

Do

[statements]

[Exit Do]

[statements]

Loop [{While | Until} condition

מערכים:

יצירת מערך חד מימדי בגודל X :

Dim varName (X) as type

המערך יכיל תאים שמספרם מאפס ועד x-1

יצירת מערך דו מימדי בעל a שורות ו-b עמודות :

Dim varName (a to b ) as type

LBound מספר תא ראשון של מערך

Ubound מספר תא אחרון של מערך

ניתן להעביר ערכים ממערך ישירות לטווח בגיליון.

לא ניתן להעביר נתונים מטווח בגיליון ישירות למערך, יש לעשות זאת תא תא.

דרך נוספת היא להציב את הטווח מהגיליון לתוך משתנה מסוג variant ואז המשתנה "הופך" למערך.

כאשר מימדי המערך אינם קבועים, יש להשתמש בהוראה redim

קבועים ב-VBA

vbCrLf , vbNewLine

vbYellow, vbGreen, vbBlue, vbRed, vbCyan,

מקרואים המגיבים לארוע

מקרואים המגיבים לארועים נשמרים במודול של האוביקט שלארועים בו הם יגיבו.

בחלון ה- project explorer בוחרים בלחיצה כפולה את האוביקט שעבורו רוצים לכתוב מקרו.

בחלון של המודול, בוחרים משמאל את האוביקט ומימין את הארוע. כותרת המקרו תיווצר אוטומטית.

מבנה כותרת למקרו המגיב לארוע על אוביקט:

Private Sub object_event(parameters)

יש ארועים המופיעים גם עבור האוביקט workbook וגם עבור האובייקטים המיצגים את הגיליונות השונים.

יש מקרואים המקבלים פרמטרים.

אם לא רוצים שהאפליקציה תגיב לארועים, מכבים את המאפיין " הגב לארועים" של האפליקציה באופן הבא:

Application.EnableEvents = False

אפשר להדליק אותו כך:

Application.EnableEvents = True

הוספת פקדים לגליון

תפריט תצוגה -> פקודה סרגלי כלים -> סרגל ארגז כלים לפקדים

בחירת הקפד הרצוי, גריגה על הגיליון במקום ובגודל הרצויים.

לחיצה על הלחצן "מצב עיצוב" לשינוי מאפיינים של הפקד.

לחיצה כפולה על הפקד לכתיבת מקרו מקושר אליו.

Forms טפסים

מוסיפים טופס (באופן דומה להוספת מודול)

ממקמים עליו את הפקדים הרצויים

נותנים ערכים למאפיינים של הפקדים

כותבים מקרו (אחד או רבים) שיגיבו לארועים המתרחשים בפקדים

יוצרים אופציה להפעלת הטופס (למשל כפתור על הגיליון) וכותבים מקרו המפעיל את הטופס.

פקדים

|מאפיינים עיקריים |פקד |

|Name, caption, |label |

|Name, caption,text |textBox |

|Name, caption,default, cancel |CommandButton |

|Name, caption,value, |OptionButton |

הצגת טופס:

formName.Show

הסרת טופס:

Unload me

ההוראה

Dir

נראות המקרו

צהוב – 4 שיעורים ראשונים

שיעור 5 - ורוד

שיעור 6 – תכלת

שיעור 7 ירוק

שיעור 8 אפור

שיעור 9 ירוק זית

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download