تمرین IF

تمرین IF : ماکرویی طراحی کنید که نمره ی دانش آموزی را از کاربر گرفته اگر

0=<نمره =<7     ضعیف

7<نمره =<14   متوسط

14<نمره =<20 خوب

پیام های مقابل آنها را چاپ نماید .

Sub NOMREH()

Dim NOM As Single

NOM = InputBox(" نمره را وارد کنید")

If NOM >= 0 And NOM <= 7 Then MsgBox "BAD"

If NOM > 7 And NOM <= 14 Then MsgBox " NOT BAD"

If NOM > 14 And NOM <= 20 Then MsgBox "GOOD"

End Sub

به این نکته توجه داشته باشید که اگر فرامین IF  فقط یک دستور باشند دیگر نیازی به END IF  نیست البته دستور مورد نظر را باید در پشت سر THEN  نوشت .

راهنمای استفاده از دستور شرطی IF

If...Then...Else Statement

       *برگرفته از راهنمای اکسل*

Conditionally executes a group of statements, depending on the value of an expression.

Syntax

If condition Then [statements] [Else elsestatements]

Or, you can use the block form syntax:

If condition Then
[statements]

[ElseIf condition-n Then
[elseifstatements] ...

[Else
[elsestatements]]

End If

The If...Then...Else statement syntax has these parts:

 

Part	Description
condition	Required. One or more of the following two types of expressions:
	A numeric expression or string expression that evaluates to True or False. If condition is Null, condition is treated as False.
	An expression of the form TypeOf objectname Is objecttype. The objectname is any object reference and objecttype is any valid object type. The expression is True if objectname is of the object type specified by objecttype; otherwise it is False.
statements	Optional in block form; required in single-line form that has no Else clause. One or more statements separated by colons; executed if condition is True.
condition-n	Optional. Same as condition.
elseifstatements	Optional. One or more statements executed if associated condition-n is True.
elsestatements	Optional. One or more statements executed if no previous condition or condition-n expression is True.

 

Remarks

You can use the single-line form (first syntax) for short, simple tests. However, the block form (second syntax) provides more structure and flexibility than the single-line form and is usually easier to read, maintain, and debug.

Note   With the single-line form, it is possible to have multiple statements executed as the result of an If...Then decision. All statements must be on the same line and separated by colons, as in the following statement:

If A > 10 Then A = A + 1 : B = B + A : C = C + B

A block form If statement must be the first statement on a line. The Else, ElseIf, and End If parts of the statement can have only a line number or line label preceding them. The block If must end with an End If statement.

To determine whether or not a statement is a block If, examine what follows the Then keyword. If anything other than a comment appears after Then on the same line, the statement is treated as a single-line If statement.

The Else and ElseIf clauses are both optional. You can have as many ElseIf clauses as you want in a block If, but none can appear after an Else clause. Block If statements can be nested; that is, contained within one another.

When executing a block If (second syntax), condition is tested. If condition is True, the statements following Then are executed. If condition is False, each ElseIf condition (if any) is evaluated in turn. When a True condition is found, the statements immediately following the associated Then are executed. If none of the ElseIf conditions are True (or if there are no ElseIf clauses), the statements following Else are executed. After executing the statements following Then or Else, execution continues with the statement following End If.

Tip   Select Case may be more useful when evaluating a single expression that has several possible actions. However, the TypeOf objectname Is objecttype clause can't be used with the Select Case statement.

Note   TypeOf cannot be used with hard data types such as Long, Integer, and so forth other than Object.

شرط ها و ساختار های تصمیم گیری در وی بی ای IF IN VBA

یکی از مهمترین بخش های هر زبان برنامه نویسی ساختار های تصمیمگیری در آنهاست .

IF  یکی از پرکاربرد ترین دستورات VBA  می باشد ، ساختار آن عبارت است از :

IF    شرط    THEN

عملیات مورد نظر

END IF

مثال : می خواهیم ساختار شرطی ایجاد کنیم که اگر محتویات سلول A1  از عدد 10 کوچکتر بود یک پیام مردودی نمایش دهد . ( فقط ساختار IF   را نمایش می دهیم )

IF  RANGE("A1")<10 THEN

 MSGBOX" مردود"

END IF

تمرین 2

تمرین 2 - ماکرویی طراحی کنید که نام و نام خانوادگی را از کاربر دریافت کرده سپس آنها را در کنار یکدیگر نمایش دهد .

SUB NAM()

DIM N AS STRING

DIM F AS STRING

N= INPUTBOX("ENTER NAME :")

F=INPUTBOX("ENTER FAMILY :")

MSGBOX N&" "&F

END SUB

تمرین

تمرین : ماکرویی بنویسید که دو عدد را از کاربر گرفته و سپس ضرب آنها را نمایش دهد :

SUB ZARB()

DIM I AS SINGLE

DIM J AS SINGLE

DIM K AS SINGLE

 I= INPUTBOX(" ENTER I :")

 J= INPUTBOX(" ENTER J :")

 K=I*J

 MSGBOX " I*J = " &K

END SUB

معرفی متغیر ها در وی بی ای

برای استفاده از متغیر ها در وی بی ای VBA  بهتر است که آنها را تعریف کنیم ، دو نوع متغیر عددی و غیر عددی وجود دارد که عددی ها عبارتند از :

1. BYTE
2. INTEGER
3. LONG
4. SINGLE
5. DOUBLE
6. CURRENCY
7. DECIMAL

و غیر عددی عا عبارتند از:

1. BOOLEAN
2. OBJECT
3. STRING
4. VARIANT
5. DATE

روش معرفی متغیر ها استفاده از کلمه ی کلیدی DIM بعد از نام ماکروست . به مثال زیر توجه کنید:

I  به عنوان یک عدد صحیح ( متغیر) معرفی شده است .

SUB ALI()

 DIM I AS INTEGER      '  I به عنوان یک عدد صحیح معرفی

     I  = INPUTBOX (" ENTER  A NUMBER INTEGER:")

END SUB

 

 

Data Type Summary

   

The following table shows the supported data types, including storage sizes and ranges.

Data type	Storage size	Range
Byte	1 byte	0 to 255
Boolean	2 bytes	True or False
Integer	2 bytes	-32,768 to 32,767
Long (long integer)	4 bytes	-2,147,483,648 to 2,147,483,647
Single (single-precision floating-point)	4 bytes	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double (double-precision floating-point)	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency (scaled integer)	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	14 bytes	+/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is +/-0.0000000000000000000000000001
Date	8 bytes	January 1, 100 to December 31, 9999
Object	4 bytes	Any Object reference
String (variable-length)	10 bytes + string length	0 to approximately 2 billion
String (fixed-length)	Length of string	1 to approximately 65,400
Variant (with numbers)	16 bytes	Any numeric value up to the range of a Double
Variant (with characters)	22 bytes + string length	Same range as for variable-length String
User-defined (using Type)	Number required by elements	The range of each element is the same as the range of its data type.

Note   Arrays of any data type require 20 bytes of memory plus 4 bytes for each array dimension plus the number of bytes occupied by the data itself. The memory occupied by the data can be calculated by multiplying the number of data elements by the size of each element. For example, the data in a single-dimension array consisting of 4 Integer data elements of 2 bytes each occupies 8 bytes. The 8 bytes required for the data plus the 24 bytes of overhead brings the total memory requirement for the array to 32 bytes.

A Variant containing an array requires 12 bytes more than the array alone.

Note   Use the StrConv function to convert one type of string data to another.

 

INPUTBOX جعبه دریافت داده

هر گاه کاربر قصد دریافت داده ها را از کاربرگ نداشته باشد و بخواهد آنها را مستقیما دریافت کند از این ابزار استفاده می کند :

Sub ALI()
   I = InputBox(" عدد مورد نظر را وارد کنيد    ")
End Sub
 

در مثال فوق ماکرو ساده ای را مشاهده می کنید که پیام " عدد مورد نظر را وارد کنید " را نمایش داده و عدد یا متن کاربر را دریافت کرده و در متغییر I  می ریزد .

شکل این تابع به صورت زیر است :

InputBox Function

       

Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box.

Syntax

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

The InputBox function syntax has these named arguments:

Part	Description
prompt	Required. String expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return–linefeed character combination (Chr(13) & Chr(10)) between each line.
title	Optional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
default	Optional. String expression displayed in the text box as the default response if no other input is provided. If you omit default, the text box is displayed empty.
xpos	Optional. Numeric expression that specifies, in twips, the horizontal distance of the left edge of the dialog box from the left edge of the screen. If xpos is omitted, the dialog box is horizontally centered.
ypos	Optional. Numeric expression that specifies, in twips, the vertical distance of the upper edge of the dialog box from the top of the screen. If ypos is omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
helpfile	Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
context	Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

Remarks

When both helpfile and context are provided, the user can press F1 (Windows) or HELP (Macintosh) to view the Help topic corresponding to the context. Some host applications, for example, Microsoft Excel, also automatically add a Help button to the dialog box. If the user clicks OK or presses ENTER , the InputBox function returns whatever is in the text box. If the user clicks Cancel, the function returns a zero-length string ("").

Note   To specify more than the first named argument, you must use InputBox in an expression. To omit some positional arguments, you must include the corresponding comma delimiter.

 

 

 مثال :

 

Sub proInput()
   

   J = InputBox("For what year do you need this report?")

End Sub

 

 

دانلود تمرین وی بی for & if

 

 

 برای دانلود

 تمرین وی بی کلیک کنید. 

ابتدا تمرین را دانلود کرده و سپس سعی کنید آنرا بدون ااستفاده از راهنما حل کنید:

صورت مسله : ماکرویی بنویسید که اعداد ستون اول را در در ستون دوم جستجو کرده و تکراری های آنرا یافته با رنگ سرخ و پررنگ نمایش دهد .