Excel VBA Tips

はじめに

• Excel VBAに関するTipsです。
• たまにしかExcel VBAを書かないので備忘録を兼ねています。
• 必要に応じて、都度更新していきます。

基本的な操作

ブック

シートのコピー

標準モジュール_(Main)

Sub Main()
  ActiveSheet.Copy
End Sub

シート

シートの追加

標準モジュール_(Main)

Sub Main()
  Dim i as Long

  For i = 1 To 12
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = i & "月"
  Next i

End Sub

セル

その他

• 和暦に変換する

cnvJYear

Private Function cnvJYear (yyyy as Date) as String
  Dim JYear as String

  '2017/7/5 -> "g": H, "gg": 平, "ggg": 平成, "e": 29
  JYear = Format(yyyy, "g") & Format(yyyy, "e")
  cnvJYear = JYear
End Function

• オートシェイプを挿入する

標準モジュール_(Main)

Sub Main()
  With Range("B2:D5")
    ActiveSheet.Shapes.AddShape msoShapeRectangle, _
      .Left, .Top, .Width, .Height
  End With

End Sub 

• 枠線の色を設定する

標準モジュール_(Main)

Property Let LineColor(sh as shape)
  sh.Line.ForeColor.RGB = RGB(0, 0, 0)
  'ActiveSheet.Shapes(1).Line.ForeColor.RGB = RGB(0, 0, 0)
End Property

• オートシェイプの塗りつぶしを設定する

標準モジュール_(Main)

Property Let FillColor(sh as shape)
  With sh.Fill
  .ForeColor.RGB = RGB(255, 255, 255)
  .Solid
End Property

プログラミング

変数

広域変数

標準モジュール_(Main)

Private mVar1 as String
Dim mVar2 as String
Public gVar1 as String

Sub Main()
  '...
End Sub

プロシージャ変数

標準モジュール_(Main)

Sub Main()
  Dim var3 as String
  '...
End Sub

定数

• 値の再代入は出来ない

標準モジュール_(Main)

Sub Main()
  Const PI as Long = 3.14
  '...
End Sub

配列

標準モジュール_(Main)

Sub Main()
  Dim arr(2) as String
  arr(0) = "Excel"
  arr(1) = "Word"
  arr(2) = "Power Point"
  '...
End Sub

動的配列

標準モジュール_(Main)

Sub Main()
  Dim dinamicArr() as String
  ReDim dinamicArr(2) as String
  dinamicArr(0) = "Excel"
  dinamicArr(1) = "Word"
  dinamicArr(2) = "Power Point"

  '既存の値を保持して、要素数だけを変更
  ReDim Preserve dinamicArr(3)
  dinamicArr(3) = "Access"
  '...
End Sub

静的変数

• プロシージャ内でのみ宣言が可能

標準モジュール_(Main)

Sub Main()
  Static counter as Long
  counter = counter + 1
  MsgBox counter
End Sub

ユーザ定義変数

標準モジュール_(Main)

Sub Main()
  Type Animal
    name as String
    age as Long
  End Type

  Dim Dog(2) as Animal
  '...
End Sub

制御構文

If

標準モジュール_(Main)

Sub Main()
  Dim ans as Long
  ans = 10

  If ans < 5 Then
    MsgBox "ans is small."
  Else
    MsgBox "asn is big."
  End If
End Sub

For

For Each

---

JScript

run.bat

@echo off
rem CScript //nologo //E:JScript xlsx2csv.vbs
CScript //nologo //E:JScript xlsx2csv.vbs test.xls Sheet1 test.csv
pause

xlsx2csv.vbs

var EXCEL = WScript.CreateObject("EXCEL.Application");
var xlCSV = 6;

var args = WScript.Arguments;
var fso = new ActiveXObject("Scripting.FileSystemObject");
var cwd = fso.getParentFolderName(WScript.ScriptFullName);

var srcFile;
var sheet;
var distFile;

WScript.Echo(args.length);

for(i=0; i < args.length; i++) {
    WScript.Echo(WScript.Arguments(i));
}

switch(args.length) {
    case 0:
        srcFile = 'C:\\Users\\X\\Downloads\\SA.xls';
        sheet = 'P0001';
        distFile = 'C:\\Users\\X\\Downloads\\SA.csv';
        break;
    case 3:
        srcFile = cwd + '\\' + WScript.Arguments(0);
        sheet = WScript.Arguments(1);
        distFile = cwd + '\\' + WScript.Arguments(2);
}

WScript.Echo(srcFile);

var book = EXCEL.workbooks.Open(srcFile);
book.Worksheets(sheet).Activate();
book.SaveAs(distFile, xlCSV);
book.Close(false);