« フィルターの結果をリストボックスに表示させる | トップページ | エクセルで球を描いてみる »

2014年1月30日 (木)

素人が素人に教える?ExcelVBAの基本と使い方

素人が素人に教える?ExcelVBAの基本と使い方

注)几帳面な方には不満足な説明です。 
  几帳面になれない人、”だいたい”で行動してしまう人向けの説明です。
  以上、ご了承ください。

●エクセル・マクロ言語                                        
連続的な集計作業のフローを明示化でき、作業条件が変わらなければ、マクロはそのまま継続使用できる。                                       
ただ、キー記録のみに頼っていると、微小な条件が変わっただけで対応できず誤った集計結果を出力したり、集計方法の検証もせず、<なんとなく合っているらしい>ことで作業が進められる可能性がある。                                       

なので、キー記録主体のマクロではなく、マクロ言語主体のマクロを作成していくべき。

マクロ言語とは?                                       
http://excelvba.pc-users.net/index.html                                       
http://www.eurus.dti.ne.jp/~yoneyama/Excel/Exl-_zen.htm#vba_jitu                                       

VBA画面の説明                                        
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_vbe.html                                       

VBA for Excelの特徴                                        
 ・構文の次群が自動表示され、また実行エラー箇所を示してくれる。
 ・ネット上に、山と言うほどの説明があるし、回答者が多くいる。 (頼りになります。)                                       

●マクロ言語の基本文                                        
     ①セル・シート・ブックの表現
      セルA1 ⇒ range("a1")            
      sheet1 ⇒ sheets("sheet1")               
      book ⇒ workbooks("abc.xlsx")    
     マクロが保存されているブック ⇒ Thisworkbook            
     アクティブになっているブック 
         ⇒ Activeworkbook : キーボード・マウスが対象とするブック 

    

  ②セルに値を代入する。(右から左に代入)                                   
      セルA1に10を代入する                                    
          range("a1") = 10                               
      sheet2のシートのB1に、「住所」を代入する。                                    
          sheets("sheet2").range("b1") = "住所"                               
      セルA1の値をセル10に代入する。                                    
          range("a10")=range("a1")  or range("a10")=range("a1").value                               
      セルA1にSUM関数を代入する                                    
          range("a1") = "=sum(b1:b10)"                               
      セルA1からセル A100を選択する                                    
          range("a1:a100").select                               
      セルA1からA100までに、一律5を入力する                                    
          Range("a1:a100") = 5                               

      もし、abc.xlsxのシート1がアクティブで、セルA1に100を代入するなら、 
      range("a1") = 100 で良いが、                                    
      sheet2がアクティブである場合に、sheet1のA1に100を入れるなら、 
      sheets("sheet1").range("a1") = 100   とする。      

      2つのブックをオープンしている場合なら、                                    
      workbooks("abc.xlsx").sheets("sheet1").range("a1") = 100
      としないと、どのブックに代入するのか不明確となり、意に反する動きとなる。                                    

  ③変数を使う(処理上仮に値を入れるもの)                                   
      まず、使うだろう変数を定義する。                                    
          dim i as long  ' 変数iは慣習、longは数値型。
              ’文字を入れる変数なら、stringとする。                               
          i=range("a1")  ' ← A1の値は数値であること。でないと、エラーとなる。                               
         range("b1")=i                               
         range("b2")=i                               

     ④コピー&ペースト                                    
          range("a1:a100").copy range("h2")
          dim 範囲 as range            'セル範囲として 範囲と言う語を変数定義する。
          set 範囲=range("a1:a1000")     'セル範囲を"範囲"に代入する。(range型のみsetを使う。))                           
          範囲.copy range("h1")    '"範囲"をコピーして、H1セルに貼り付ける。   

●基本処理                                       

     (1)繰り返し処理                                   

    ①for~next                                   
     セルA1からA100までに、1,2,3と連番を入力する。                                    
        dim i as long                               
       for i=1 to 100                               
            range("A" & i)=i                           
        next                               

     セルA10から上方向に、10をカウントダウンした数値を代入する。                                    
        dim i as long                               
       for i=10 to 1 step -1                               
            range("a" & i)=10-i                           
       next                                

   ②do While ~ loop                                   
      セルのA1~A10に1~10を入力する                                    
        i = 1                                    
        Do While i < 11                                    
               Worksheets("Sheet1").range("a" & i).Value = i                               
            i = i + 1 
   '  for~nextの場合は自動で変数iはカウントアップするが、do~loopの場合はしない。
        Loop                                   

                                       

    (2)条件文                                    

    ①if~end if 条件文 
                                 
    A1セルが10だったら、A1セル値を2倍にした数値をB1セルに代入する。                                    
        if range("a1")=10 then range("b1")=range("a1")*2                               

    A1セルが5だったら、B1セルにA1セル値を代入する。                                    
    if range("a1")=5 then                                   
            range("b1")=range("a1").value                               
    end if                                   

    A1セルが5以上だったら、A1セルに10を加算した値をB1セルに代入し、
    そうでなかったら、A1セルに10をマイナスした値をB1セルに代入する。                                    
    if range("a1")>=5 then                                    
            range("b1")=range("a1")+10                           
       else                               
            range("b1")=range("a1")-10                           
    end if                                   

    (3)for~nextの繰り返し文とif文を組み合わせる                                    
        ①A1からA100で、値が○だったら、B列に●を代入する。
                               
            dim i as long                           
            for i=1 to 100                           
                if range("a" & i)="○" then range("B" & i)="●"                        
            next                           

        ②A1からA100で、A列が○で、かつB列が●だったら、C列に×を代入する。                                
            dim i as long                           
           for i=1 to 100                           
                if range("a" & i)="○" and range("B" & i)="●" then range("C" & i)="×"
           next                           

    (4)マクロ言語の中でワークシート関数を使うには                                    
         セルA6からa4400,000までの可視セル(フィルター実行した結果)の数をカウントし、A1セルに代入する。                               
        range("a1") = Application.WorksheetFunction.Subtotal(3, Range("a6:a400000"))                               
        もし、A1セルに関数式を代入するなら、
        Range("a1") = "=Subtotal(3, a6:a400000)" とする。                               

        たとえば、マクロからシートに関数式を代入し、その結果をまたマクロで利用することも可能。                               
        B1セルに、A1セル値を参照するvlookup関数があったとして、 
                 range("a1")=10                        
                 msgbox range("b1")                        
        この場合のiには、vlookup関数の結果の値が入る。                               
        計算が複雑であれば、結果が出る時間とマクロが動作する時間差が生まれて、
        変数iに何も入らないかもしれないが、30万件のvlookupでも一瞬に計算される。
        おそらく、通常のオフィスでの計算では、時間が掛かり過ぎるという計算はないと 
        思われる。あるとすれば、計算ではなく、シート選択やシート上の値の分類、
    フィルター、ピボットやブック選択などの処理だと思われる。                               

     (5)キー記録を参考にしてコードを作る。(キー記録のアレンジ)                                   
    例:                                    
    L6~L13を選択し、コピーし、N6をクリックし、行列を入れ替えて貼り付ける処理をキー記録したら、                                    
    Range("L6:L13").Select                                    
        Selection.Copy                                    
        Range("N6").Select                                    
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _   
    False, Transpose:=True                                    
        Range("L6").Select                                    
        Application.CutCopyMode = False                                    

    操作を忠実に記録しているが、無駄も多い。                                    
    マクロ言語では、いちいちセルをクリックする必要がないので、                                    
    Range("L6:L13").copy でコピーが完了する。                                    
    copyの次にセルを指定すると、貼付となるので、                                    

    Range("L6:L13").copy                                    
    Range("N6").PasteSpecial Paste:=xlPasteAll, Transpose:=True                                    
    Range("l6").Select                                    
    Application.CutCopyMode = False                                    
    とすることができる。                                    

                                          

    (6)データの最終行を知る                                   
    マクロでデータを集計する場合に、どの行に対して行うのかを知る必要が出てくる。                                    
    特に、データを追加する場合はよくある。                                    
    お約束のコードは、                                    
    http://officetanaka.net/excel/vba/tips/tips130.htm                                    
     Cells(Rows.Count, 1).End(xlUp).Select                                    
    意味:A列のシート最終行からCTRL+↑キーで最初に値が見つかったセルを選択する。                                    

    cells(行、列)の表記となり、range(列行)とは反対となる。                                    
    range(列行)は、A1,B1と分かり易いが、cells(行、列)となっているが、列についても変数が使える利点がある。                                    
    cells(1,1)は、range("a1")と同じ。                                    

    Cells(Rows.Count, 1).End(xlUp).Select+1 とすれば、最終行の次の行となり、追加すべき行となる。                                    

  (7)指定フォルダーのファイル選択画面を表示し、人が選択したエクセルブックを開く。                                    
    http://officetanaka.net/other/extra/tips15.htm                                    
    Sub 部門フォルダーから_open()                                    
     ' ネットワークフォルダーを開くように準備する。                                    
         With CreateObject("WScript.Shell")                               
            .CurrentDirectory = "\\●●●●\●●●●\"                           
         End With                               

        Openfilename = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")                               
        If Openfilename <> "False" Then  ' 選択内容が空でなかったら、                               
                Workbooks.Open Openfilename  ' 選択したファイルを開く                           
        End If                               
        End Sub                               

    (8)ボタンとマクロ(モジュール)との関連付け                                   
    ボタンは図形から選んで、シート上に作成する。                                    
    ボタンを右クリックして、マクロの登録を選択。                                    
    既に、モジュールを作成していれば、モジュール名=マクロ名が表示されているので、                                    
    それを選択して、OKボタンクリックで、関連付け完了となる。                                    
    これで、ボタンをクリックすると、マクロが実行される。                                    

    この関連付けは、オブジェクトであればできるので、テキストボックスにも、                                    
    グラフにもマクロとの関連付けを行える。                                    

    (9)処理途中にメッセージを表示させる                                    
    マクロが勝手に処理を行い、いつ終了したのかわからないので、                                    
    メッセージを表示させることができる。                                    

    msgbox "処理が終了しました。"                                    

    atai=msgbox("次の操作に移りますか?",vbokcancel,"title")                                    
    if atai=vbok then msgbox "yes selected "                                    


●続きは、こちらで、どうぞ。
             http://engi-seishi.la.coocan.jp/VBA_de_tukuru.html                           


|

« フィルターの結果をリストボックスに表示させる | トップページ | エクセルで球を描いてみる »

「ExcelVBA」カテゴリの記事

コメント

コメントを書く



(ウェブ上には掲載しません)




« フィルターの結果をリストボックスに表示させる | トップページ | エクセルで球を描いてみる »