Excel.   
  

  


     Microsoft Excel      ,  -     (   )  ,     .               Excel.      ,      .





  ,   

Excel.   








     Excel,     Microsoft      Microsoft Office,      .       Excel    :    , IT-, , , , ,   ..   Excel             (    ,      ,     .).

        (      F1     : Microsoft Offic Excel     ).           ,            , ,          Excel (    ).           ,     .       .

          VBA (    VBA    Alt+F11).     ,   ,  .


 

  1         Microsoft Office  Visual Basic for Applications (VBA).            VBA.

  2  ,        ,       ;         Excel (    ).      Excel,          .        ,        .

 3   .          ,      .  ,       ( ,   )   ,    ,      .         ,    ,        .

 ,    ,     4.   ,       ,        ,    ,     ,  .

  5         ,     ,       .

  6    ,    ,    .          .

             Excel: Application, Chart, Range, Workbook  Worksheet.


 

     Excel 2007.              .

           Excel,  ,            ,        ,      .

,      ,       Excel.       ,       ,        .


    Microsoft Excel

  Microsoft Excel     .

         , ,   .

    (     ,    ,   ,   .).

      (   OLAP)    .  XML-.

     .

      Lotus Notes,        (, 1ѻ .).

   (   -,   Microsoft Excel  ,  -,   .).

       (Word, PowerPoint, Access  .).

   : , , ,     .

   Microsoft Office:  ,     ,   ,    .

      VBA.

  Excel     ,      .


 

        .

       ,         .  Excel    .

      .  Excel      : , , ,   .

  ,      -  (, ,   ..).

  ,                   .         ,           .

   ,   ,       .                Shift+F10.

  ,      Excel   Visual Basic for Applications (VBA).            ? .

      ,       .  ,       ,    .

   ,   ,       VBA.

  ,   Excel    .   ,               Excel    ,        .

       Microsoft Excel  ,        Excel.         .

             ,       .

            .





   Microsoft Excel      .


    ,         .

   ,        ,      .       .

      (  )      .      :  ,   ,  ,   ,     ,      ,  , .             .

       .        , ,    .  Excel     Windows.

     ,           .     - ,           ,       .

   ,  ,     Microsoft Excel.        XLSX.         (. ).

      ,   ,    ,     .          .        (. ).

 VBA    ,      ()   VBA.     ,     Alt+F11.

       ,     .       .

          .

     (  )   ,   .        ,  .   ,                (    :    0,24,            0,2).





  ,   ,       ,       .


    Excel,   ,    .      , , ,   ()   (. ).

   .

              .

                .

      .

    Microsoft Excel  ,    .       , , ,   ()  .     ,     VBA.

    ,      ,       .

   ()   ,      .     ,   .  ,       .

    () .     ()    ,      .

     .     (        ),  (       )  .       ,     .

        ,   Excel,            .       ,    .


    

     ( )         http://www.piter.com/downLoad/978591180547/ (http://www.piter.com/downLoad/978591180547/).




 


 ,         dgurski@minsk.piter.com (mailto:%20dgurski@minsk.piter.com) ( ,  ).      !

   http://www.piter.com (http://www.piter.com/)      .




1

   VBA



           Visual Basic for Applications (VBA),     Microsoft Office.

        .    ,        -  ,   ,   , , , , , ,   ..               VBA,         .




  VBA



VBA    ,     Microsoft Office.     VBA     Alt+F11.




 VBA


       VBA       Microsoft Office    .    VBA        ,        .

  Excel   VBA     (,     ):

    ;

    ;

 ,    ;

  ,       ,      ;

   (,     );

    ;

   ,    ,          .

      VBA          .




  VBA


   VB      .1.1.




.1.1.  VBA



    :       (Project),      (Properties)        .    Project ()    ,     ,      (     ).  Properties ()       .

VBA-  Microsoft Excel    :

 (  VBA);

 ;

  ;

  ;

 ;

.


  VBA

  VBA    ,    ,      ( , ,   ..).          Modules.

     ,      ,       .        (),     .


 

    ,     ,    ( ) .          Class Modules.


  

   (   Microsoft Excel Objects)   ,         . ,    ,           (.      ).          ,     VBA          .


  

   ( Microsoft Excel Objects)   ,           . ,    ,          .    VBA                  .


 

  ( Microsoft Excel Objects)   ,        ,      .        .




 ( Forms)    VBA,           .     :      ( ).

     ,     .        .       View Code ( )  ,            Project ().

        ,      View Object ( )  . ,     ,   .1.2.




.1.2.  



       Properties ().    ( )    ,   .                     .




  VBA


     VBA        . ,     VBA,   .

1. ,  (    Option),  API-.

2.     .




,     VBA


     ,         VBA.       . 1.1.


 1.1.     VBA








    ,             .     VBA. ,    ,         .


     ,         VBA.          (           ):



Dim | Static  [As _][, _ [As _]]


  ,         Dim  Static.      .       .    ,      ()    ,     As       .             Dim,     (.  ).         (       ).

  ,   ,  :



Dim intPos As Integer

Dim varValue, intValue As Integer

Static strText As String

Static var1 As Variant, var2 As Variant, var3 As Variant





  


 VBA       .       Rem    .     Rem  ().       ,    Rem      .      . :



Rem  

Dim intRes As Integer

Rem   

intRes = 123


 ,    ,       ,         (   ):



'  

Dim intRes As Integer

intRes = 123   


   VBA  ,               :



'  ,     _

  






Rem  ,     _

  








  VBA   , , , , ,     ,      ( , ,    ..).

  VBA    Unicode.     ,            (, ).

     .

      ( ),    .

     .



!

VBA      .  ,   strmyText  strMyText       .       , ,   ..


    VBA:



strText

CUSTOM_Data2

_











               VBA.




  


VBA     .        , , ,      ,  ,         ,  Variant.


  

    VBA   . 1.2.


 1.2.    VBA








  Decimal        .        Variant(  Variant   ).





     VBA     String ().            .   VB    :    .          .

   ,   ,    ,        (,    ).           .        65 400 .

            VBA.              .       2 .


  

         VBA    Date.         ,   ,       .   , ,     Excel.

 ,   Date    ,  VBA     .     Single (   ).       ,   30  1899 ,      .


  Variant

 VBA       Variant.         VBA  (      ,     ).

     Variant                  VBA    .            (,    ,   ..).

   Variant  (    ),     Empty.


.   Object

 ,   VBA ,    ,      .         (  ).    ,     ,     .

,   VBA, ,        .        .          ,         Set.  objRef     .         :



Set objNewRef = objRef


 objNewRef     ,   objRef.        :



Set objRef = Nothing

Set objNewRef = Nothing


      ,  ,    .

     VBA    Object.   ,         .




 


     ( )       :



Public | Private [WithEvents] _ [As [New] _-

] _

[, _ [As [New] _]]


 ,     ,    :

Public        -  (    );

Private    ,     ,   - .

     ( )        ( ):



Dim | Static [WithEvents] _ [As [New] _] _

[, _ [As [New] _]]


 ,     ,    :

Dim      ,      ;

Static      ,        .

  WithEvents    -  .

_     .

_      .

  [As [New] _]  ,        Variant.

    New,    . New     WithEvents,       Object    _   .

      VBA:



Public intData As Integer

Private intCount As Integer, varData

Dim strText As String

Static a, b, c

Dim objRef As Object

Dim objCtrl As New Control




!

        As    ,      . ,   Dim , ,  As Integer    b    Variant,      Integer.





 


    , VBA      .

      .

       (  ).      .

  Date   00:00:00 30.12.1899 (        ,     ).

   Object   (      )   Nothing,         (  New).




    


     .

VBA     .             Dim, Static, Private  Public.               .  ,        Variant.

        ,       .

 ,    ,      ,  dblSalaryAccount = dblSalaryAcount * 10. 5,   dblSararyAccount   ,       5.37 5.         ,       dblSalaryAcount,    .             ,         VBA  .

        Option Explicit.         ,     Tools ? Options ( ? )  VBA    Options ()    Editor ()   Require Variable Declaration (  ).







      ,  VBA      .     VBA      .     :



[Public | Private] Const _ [As _] = 


         :

Public      ;

Private     ,     ,   .

 ,        ,   Private.

_    .

    (, 1, 1.245 . .)  ,         .

    ,     VBA    .     :



Const PI As Double = 3.14159265359

Public Const MyConstString = MyConst

Private Const  As String = 









 VBA     ,          VBA .          .     ,  VBA-npo.




     


           . 1.3.


 1.3.      








 +      .  bVBA    &,   .        &,                , ,   ,   .





 


        Boolean.   -    ,    True,    False.   ,  VBA,   . 1.4.


 1.4.  









           ,        Variant.      Empty,        NULL.   ,        :  .

         Option Compare.  Excel     : Option Compare Text (     )  Opt ion Compare Binary (   ,      ).        Option Compare Binary.

 VBA     Like  Is,      .      Boolean   .

 I s   ,           .           .    :



 = 1 Is 2


 Like   ,       .    :



 =  Like 


 ,      ,   . 1.5.


 1.5.      









   ,    15 26       .  -      ## ## [A],     15 26  Like ## ## []   True.

            (-).         (   ): [A-Z],   [Z-A].





 ,      ,   . 1.5,       : ([), (]), (#), (?), (*).





 


 VBA  ,       (,    If-Then-Else,    ).    VBA ( )      . 1.6 ( ,   , ,   True  False).


 1.6.   VBA











              .      .       ,    .

 VBA      .       . ,         .  VBA-            .




 


    VBA  ,    :



Public | Private _ ([])[As _] _

[, _ ([]) [As _]]






Dim | Static _ ([])[As _] _

[, _ ([]) [As _]]


        ,          (   ).

        ,         .    ,    ,        .       :      ,      ReDim (     ).

     ,      :



_ To _ | _ _

[,_ To _ | _]


 VBA          60.     .

                 (, 50  100).

             .             (      ).

     ( ,     ):



Dim avarValues()

Dim astrValues(1 To 10) As String, astrValues2(10) As String

Dim aintValues(1 To 10, 1 To 3) As Integer, aintValues(10, 3)

As Integer





    


   ,            .    ,   ,   Option Base.      :



Option Base 0






Option Base 1


       (  ),    .




  


 VBA         .  , VBA       .     ReDim,   :



ReDim [Preserve] _ ([])[As _] _

[, _ ([]) [As _]]


          Dim (     ).         ,        Variant.

   ReDim     Preserve   ,     , .     :



ReDim astrValues(1 To 10), aintValues(10, 20)

ReDim varArray(2 To 4) As Boolean


 Preserve    ,     .            :

    ;

    ,    ;

       .

     ReDim    Preserve:



'   ReDim   

ReDim astrValues(1 To 5, 1 To 10)



'   

ReDim Preserve astrValues(1 To 5, 1 To 25)



'   

ReDim Preserve astrValues(1 To 5, 1 To 15)





  


  VBA      ,              .     VBA   ,   :



LBound(_[, _])

RBound(_[, _])


 LBound     , a RBound  .           ,    .     .    ,      .      Long.

           avarValues (     Long):



lngLBound = LBound(avarValues)

lngRBound = RBound(avarValues)





   


      VBA            .       .  (    ):



intNum = aintValues(16)

intNum = aintValues(12, 32, 3)





  Variant    


 VBA     Variant,        .      .       Variant   .           ,     (   ), :



Dim aintValues(1 To 3) As Integer

Dim varArray

'     Variant

varArray = aintValues

'    

varArray(1) = 1

varArray(2) = 2

varArray(3) = 3


     Variant       VBA       ,      .

  ,     Variant ,    IsArray,   :



IsArray()


     Boolean: True         ,  False    .




  Array   


 VBA      .      Array.   :



Array(_)


      ,  .      ,     Variant.      Array:



Dim varArray

'   

varArray = Array(1, 2, 3, 4, 5)









 (    )  ,        .      ,         ,       .    VBA     . ,   Workbooks        Workbook   ,   Worksheets       Worksheet     ..

 VBA      Collection.   Collection        :



Dim col As New Collection






Dim col As Collection

Set col = New Collection





 


       Add,   :



. Add  [, ][, _][, _]


    Add    .        ,  ,  .      ,       .       Variant.

       .        _  _,         ,         .       .




  .


   .

   ,     (https://www.litres.ru/aleksey-gladkiy/excel-truki-i-effekty/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


