שימוש בפונקציות Excel
הפעלת 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 , Variant….
הצבה במשתנים פשוטים:
ערך = שם משתנה
5 = X
אופרטורים מתימטיים:
אם 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
ניתן לראות מה ערכו של מאפיין או לשנות אותו.
התיחסות למאפיין :
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
ניתן לבחור הקלטה כשהכתובות של התאים הן יחסיות או קבועות.
האוביקט 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(rng).end |
| |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
האוביקט
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)
Forms טפסים
מוסיפים טופס (באופן דומה להוספת מודול)
ממקמים עליו את הפקדים הרצויים
נותנים ערכים למאפיינים של הפקדים
כותבים מקרו (אחד או רבים) שיגיבו לארועים המתרחשים בפקדים
יוצרים אופציה להפעלת הטופס (למשל כפתור על הגיליון) וכותבים מקרו המפעיל את הטופס.
פקדים
|מאפיינים עיקריים |פקד |
|Name, caption, |label |
|Name, caption,text |textBox |
|Name, caption,default, cancel |CommandButton |
|Name, caption,value, |OptionButton |
הצגת טופס:
formName.Show
הסרת טופס:
Unload me
RC format
Scope of variables
ההוראה
Dir
נראות המקרו
צהוב – 4 שיעורים ראשונים
שיעור 5 - ורוד
שיעור 6 - תכלת
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.