??????????? ?? ??? Visual Basic ??? Excel sub-procedures ??? ?? ?? ????? ???? ????

???? ?????? ???? ??????
???? ID: 843144 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

?????

?? ???? ????? ???? ?? ?? ????????? ?? ??? Microsoft Visual Basic ??? Microsoft Excel sub-procedures ??? ?? ?? ????? ???? ?????

???? ???????

??? ?? ????????? ??? ??

???????????? ???? ?? ?? ????? ?????? ?? ?? ?????? ?? ???????? ???? ?? ??? ??? ?? ??? ????? ??????? ?? ?? ????? ???? ?? ???? ?? ?? ????????? ??? ??? ??? ????????? ???? ?? ???? ??? ?? ???????? ?? ????? Visual Basic ??? ?? ?? ????? ?? ????? ???? ??:
  • ???? ?? declared ????
  • ??????????? ?? ????????? ?? ?? ?? ????? ?? ???? ????
  • ?? ?? ?? ???????

???? ?????? ??? ?? ?? ?????

???? ?????? ??? ?? ?? ????? ???? ?? ??? simplest ????? ?? ????? ???? ?? ??? ????????? ????? ?????? declares ?? ??, x ?? y, ?????????? ?? ??? ???:
Dim x As Integer, y As Integer
X ?? ??? ?? y ?? ??? ??? ????????? ??????????, Visual Basic telling ??? ???????? ?? ?? ??? ???????? ?????? aside ??? ???? ?? ??? (2 ?????? ???????? x ?? ??? ?? y) ?? ?? x ?? y ??? ???????? ???????-32768 ?? 32767 ?? ??? ??? ???????? ???

???:??? ?? ???? ?? ?? ????? ?? ?? ?? ???? ?? ??????????? ???, ?? ????????? ???? ?????? ???????? ?? ?? ??? ???? ???????

??? ?? ???? ?????? ???, ????? Visual Basic ??? ?? ??? ???, ???????? ?? ?? ??? ????????? ???? ??, ?? ???? ?? y ??? ?? ?? ???? ???????? ?? ?? ??? ???? ?? x variant ?????? ????:
Dim x, y As Integer
???????? ??????? ?? ???, "Variant ???? ??????" ??? ??????

?? ?? ?? ???? ????, ?? ??? ????? ????? ?? ???? ????:
  1. ?????? ?? ???? ???? ????????????? ??? ????, ?? ?? ??? ?? ????????????? ?? ??????
  2. Visual Basic Editor (ALT + F11 ?????) ?? ??????? ?????
  3. ????? ???????????? ???????? ??,???????.
  4. ????? ??? ???? ????:
    Sub Variable_Test()
    Dim x As Integer, y As Integer
    x = 10
    y = 100
    MsgBox "the value of x is " & x & _
    Chr(13) & "the value of y is " & y
    End Sub
  5. ?????Variable_Test??????? ???? ????? ????? ??????? ???? ??::
    x ??? ?? 10 ??
    y ??? ?? 100 ??
  6. ????? ????,OK.
  7. ?????Variable_Test?????? ?? ????? ?????? ?????:
    x = 10
    

    ?? ???:
    x = "error"
    
  8. ?????Variable_Test???????
"??????" ???????? ???? ?? ?? ?? ???????? ?? x ?? ??? ?? ???????? ??? ?? ????? ???? ?? ??? ?????? ?? ??? ??? ??????? ?? ?? ??-???? ?????? ??????? ???????

???? ?????? ?? ??????

????? ?????? ??????? ?????????? ???? ?????? ?? ???? ???? ???? ??:

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
???? ??????:?????? ?? ????????????? ??????
??????2 ????????? ?? ?? ???
????????2 ??????-32,768 ???? ?? ??? 32,767
????4 ??????-2,147,483,648 ???? ?? ??? 2,147,483,647
???8 ??????-1.79769313486232E308 ???? ?? ???-- ??????? ????? ?? ??? 324; 4.94065645841247E - 4.94065645841247E ??????? ????? ?? ??? 324 1.79769313486232E308 ???? ?? ???
??????8 ??????-922,337,203,685,477.5808 ???? ?? ??? 922,337,203,685,477.5807
??????8 ??????????? 1, 100 ?????? 31, ?? ??? 9999
????????10 ?????? + ???????? ?????

Variant ???? ??????

????????? ???? ?? ??? ?? ???? ???? ?????? ????????? ???? ????, ?? ?? ?? ?? ?????, ?? ???? ???? ????? ?? ??, Visual Basic ???????? ??? ?? ?? ?? ?? ?? ??? variant ???? ??????? ?? ???? ?????? ?? ??? ??? declared ?? ?? ??? ????? ???:
  • ?? ????????, ??????, ???, ??????, ?? ???????? ??? ??? ?? ???? ????
  • ?? ??? ?? ?? ?? ???? ?? ??? ?????? ?? ???? ???
Disadvantage ?? variant ?? ?? ?????? ?? ?? ?? ?? ?? 16 ?????? ?? ???????? ??? 16 bytes of memory can be significant in large procedures or in complex modules.

To see how this works in theVariable_Testmacro, follow these steps:
  1. Change the code in theVariable_Testmacro to:
    Sub Variable_Test()
    Dim x, y
    x = "string"
    y = 1.23
    MsgBox "the value of x is " & x & _
    Chr(13) & "the value of y is " & y
    End Sub
    
  2. ?????Variable_Test???????
You will not receive an error because you can assign anything to the variant variables x and y.

???:You can also leave out the following line and the macro will still work as the variables x and y are treated as Variant data types:
Dim x, y

Scope of a variable

When you declare a variable, it may or may not be seen by other macros in the same module, in other modules, or in other projects. This availability of a variable in modules is referred to as scope. The three types of scope are procedure-level, private module-level, and public module-level. The scope depends on how and where you declare your variable or variables.

Procedure-level scope

A variable with procedure-level scope is not seen outside the procedure where it is declared. If you set the value of a variable that has procedure-level scope, that variable's contents will not be seen by other macros.

To verify that a variable with procedure-level scope is not seen outside the procedure where it is declared, follow these steps:
  1. Insert a new module into your project.
  2. Type both of the following macros into this module:
    Sub Macro1()
    Dim x As Integer
    x = 10
    MsgBox "x, as seen by Macro1 is " & x
    'the next line runs Macro2
    Macro2
    End Sub
    Sub Macro2()
    MsgBox "x, as seen by Macro2 is " & x
    End Sub
    
  3. ?????Macro1.

    ???? ????? ????? ??????? ???? ??::
    x, as seen by Macro1 is 10
  4. ????? ????,OK.

    You receive the following message:
    x, as seen by Macro2 is
  5. ????? ????,OK.
Macro2does not display a value for the variable x because the variable x is local toMacro1.

Private and public module-level scope

You can define variables in the declarations section of a module (at the top of a module, above all sub procedures), and set the scope of your variable by using the????statement, theDimstatement, or the???????? If you put the????statement in front of your variable, your variable will be available to all the macros in all the modules in the project. If you put either theDimstatement or the????statement in front of your variable, your variable is available only to macros in the module where it is being declared.

To see the difference between the??????? ??Dimstatement, follow these steps:
  1. Save and close any open workbooks and then open a new workbook.
  2. Start the Visual Basic Editor.
  3. Insert a module into your project.
  4. Type the following code into this module:
    Public x As Integer
    Sub Macro_1a()
    x = 10
    MsgBox x
    Macro_1b
    End Sub
    Sub Macro_1b()
    x = x * 2
    MsgBox x
    Macro2
    End Sub
    
  5. Insert another module into your project.
  6. Type the following code into this module:
    Sub Macro2()
    x = x * 3
    MsgBox x
    End Sub
    
  7. ?????Macro_1amacro in the first module.

    With the variable x declared as "Public x As Integer", all three macros in the project have access to the value of x. The first message box displays a value of 10. The second message box displays a value of 20 (because x is multiplied by 2 inMacro_1b). The third message box displays a value of 60 (because the value of x was changed to 20 inMacro_1band then it was multiplied by 3 inMacro2).
  8. Change the declaration line in the first module from:
    Public x As Integer

    ?? ???:
    Dim x As Integer
  9. ?????Macro_1a???????

    With the variable x declared as "Dim x As Integer", only the macros in the first module have access to the value of x. So the first message box displays a value of 10, the second message box displays a value of 20, (because x is multiplied by 2 inMacro_1b) and the third message box displays a value of 0 (becauseMacro2does not see the value of x and the uninitialized value of zero is used byMacro 2).
  10. Change the declaration line in the first module from:
    Dim x As Integer

    ?? ???:
    Private x As Integer
  11. ?????Macro_1a???????
?? ?? ????? ????? ?? ????? ?? ????????? ?? ???? ??????????????? ??? ?? ??? ??? ????? ??? ?? ?? ?? scope??????? ?? x ??? ??? ???????, ???? ??? declared ?? ??????? ?? ???? ???

???:??? ?? ????? ??? ?? ??????? ???? ??? ?? declared, ????? ???? ?? ??? ????? ???? ?? ??? ???? ?? ?? ??????????????????? ??? ?? ??????????? ?? ????? ?? ?????? ????? ??, ????? ??????? clearer ?? ?? ?? ??? ??? ?? ?? ????? ???? ???????????

?? ?? ?? ??????

??? ?? ????? ??? ?? retains ???? ??? ?? ???? ?????? ?? ??? ??? ???? ???? ??? ???? ?????? ?? ??? ?? ?? ?? ??? ??? ???? ??, ????? ??? ??? ??????? ??? ??, ?? ?? ??? ??????? ??, ?? ?? ?? ?? ??? ???

?? ?? ?? ??? ?? ??????? ????

?? ?? ?????? ????? ???, ?? ??? ?? ??? ?? ??? ??????? ???? ???? ???? ???????? ?? ????? ?? ??????? ?? ??, ?? ?? ????? ???????? ?? ?????-????? ???????? ?? ??????? ?? ?? (""), ?? ??? ??????? ????? ???????? ASCII ??? 0 ?? ??? ??? ????? ???? ?? ??? variant ?? ??????? ???? ???? ??? ???? ?? ??? ???????? ?????? ?? ?? ?????-????? ???????? ??? ?? ????? ?? ???????? ???? ???? ?? ("") ???????? ?? ?????? ????

?????????-???? ??

??? ???? ??? ?? ????? ?? ???? ?????? ??? declared ????????, ?? retains ???? ??? ?? ?? ?????? ?? ??? ??? ??? ?? ?????? ?? ???? ??????? ???, ?? ?? ??? ??? ?? ?? (?????? ?? ??? ?????? ???? ?? ??????) ?? ?? ?? ??????? ?? ??? ??? ????

???? ?????????-???? ?? demonstrate ???? ?? ??? ???, ?? ????? ?? ???? ????:
  1. ???? ????????? ??? ??? ??? ??????? ???????? ?????
  2. ?? ??????? ??? ????? ????? ?????? ???? ????:
    Sub Macro1()
    'set x as a procedure level variable
    Dim x As Integer
       MsgBox "the initialized value of x is " & x
    x = 10
    MsgBox "x is " & x
    'the next line runs Macro2
    Macro2
    MsgBox "x is still " & x
    End Sub
    Sub Macro2()
    MsgBox "x, as seen by Macro2 is " & x
    End Sub
    
  3. ?????Macro1.

    ???? ????? ????? ??????? ???? ??::
    x ?? initialized ??? 0 ??
  4. ????? ????,OK.

    ???? ????? ????? ??????? ???? ??::
    x 10 ?? ??
  5. ????? ????,OK.

    ???? ????? ????? ??????? ???? ??::
    x, ?? ??? ??? Macro2 ?????? ???? ??
  6. ????? ????,OK.

    Macro2????????? ???? ?? x ?? ??? ??? ??? ?? x ?? ??? ??????? ?? ???????Macro1. ???? ????? ????? ??????? ???? ??::
    x 10 ??? ?? ??
  7. ????? ????,OK.
  8. ?????Macro1.
???? ??? 6 ??? ?? 3 ??? ???? ???? ??? ??????? ?? ?? ????? ??????? ???? ?? ????Macro1??? ??? 6 ???, x ?? ?? ??? ??? ??? ??? ??? ?? ?????, ?? ?? ?????Macro1??? 7, ??? ???? ????? ?????? x ????? ?? ??? ??? (initialized ???) ?? ????

????? ??????

?????? ?????? ?? ????? ?? ?? ?????????-???? ?? declared ?? ??, ??? ?? retains ???? ??? ?? ???? ????????? ?? ???: ??? ??? ?????, ??? ???? ??? ?? ????? ??, ???? ??? ?? ?? ???? ????????? ?? ??? ???? ????? ?? ?? ??????? ???? ?? ??? ???? ????? ????

????? ?? ?????? ?????? ???? ????? ???? ??, ????? ????? ?? ???? ????:
  1. ??? ??? ?????Macro1?? ???:
    Sub Macro1()
    'set x as a procedure level variable
    Static x As Integer
       MsgBox "the initialized value of x is " & x
    x = x + 10
    MsgBox "x is " & x
    End Sub
    
  2. ?????Macro1.

    ???? ????? ????? ??????? ???? ??::
    x ?? initialized ??? 0 ??
  3. ????? ????,OK.

    ???? ????? ????? ??????? ???? ??::
    x 10 ?? ??
  4. ????? ????,OK.
  5. ?????Macro1.

    ???? ????? ????? ??????? ???? ??::
    x ?? initialized ??? ?? 10 ??
  6. ????? ????,OK.

    ???? ????? ????? ??????? ???? ??::
    x 20 ?? ??
  7. ????? ????,OK.
??? ??????? ??? ??? ???? ??? ?? ????? ??? ??????? ??? ????? ?? ?? ??? ??? ?? x declared ?? ?? retains ???? ??? ?? ????? ?? ??? ??Macro1???? ????

???:??? ???? ??? ???? ???????-???? ?? ???, ???? ?????? ?? ?? ???? ?? ??? ?????? ?????????-???? ?? ??? ?? ???

?????? ?? ???????-???? ?? ???? ????, ????? ????? ?? ???? ????:
  1. ??????? ?????? ??? ??? ????????? ????Macro1?????:
    Dim x As Integer  'create a module-level variable
    Sub Macro1()
       MsgBox "the initialized value of x is " & x
    x = x + 10
    MsgBox "x is " & x
    End Sub
    
  2. ?????Macro1.

    ???? ????? ????? ??????? ???? ??::
    x ?? initialized ??? 0 ??
  3. ????? ????,OK.

    ???? ????? ????? ??????? ???? ??::
    x 10 ?? ??
  4. ????? ????,OK.
  5. ?????Macro1.

    ???? ????? ????? ??????? ???? ??::
    x ?? initialized ??? ?? 10 ??
  6. ????? ????,OK.

    ???? ????? ????? ??????? ???? ??::
    x 20 ?? ??
  7. ????? ????,OK.
??? ??????? ??? ??? ???? ??? ?? ????? ??? ??????? ??? ????? ?? ?? ??? ??? ?? x declared ?? ?? ??? retains ??? ???? ?? ???? ?? ???Macro1???? ????

???? ????????? ?? ?? ????? ???? ?? ??? ????? ????

??? ?? ?? ?????? ?? ?? ??? ?? ???? ???????-???? ?? ?? ??? ??? ?? ????? ???? ????? ???, ????? ??????????? ???????????? ????? ?? ????? ?????????????? ????????????? ??..

??? ??? ???? ?? ???Macro1????????? ?? ??? ???: ?????Macro1, ?? x ?? ??? ????? ?? ??????? ?? ?? ???? ???? ????? ??????? ???? ??:
x ?? initialized ??? 0 ??
???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
843145??????????? ?? ??? Visual Basic ??? Excel sub-procedures ?? ????? (????????)
???? ???

???

???? ID: 843144 - ????? ???????: 20 ??????? 2011 - ??????: 3.0
???? ???? ???? ??:
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
??????: 
kbvba kbprogramming kbinfo kbmt KB843144 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:843144

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com