常にマクロが有効なファイルに保存する

投稿日: 2020年2月9日 作成者: ooooo

現在のExcelは、ファイルの拡張子がxlsmでないとマクロが消えてしまう。うっかりxlsxで保存して作ったマクロが無くならないように保存するファイルの種類を常に「Excelマクロ有効ブック(*.xlsm)」にする。Excel本体のメニュー/ファイル/オプションで設定する。

この設置で新規にExcelを起動して保存すると、拡張子がxlsmに変わる。なお、Excel97-2003のブックは同じ拡張子でマクロが保存でる。

昔は良かった。ファイルを見ただけではマクロを書いているのがわからない。「だから危険だったんよね」

カテゴリー: 1 「開発」タブとVBE | コメントする

「IF関数を書く」チョット待った！！

投稿日: 2020年2月9日 作成者: ooooo

ワークシート関数IFが使えるということは「条件分岐」OKです。ならVBAでIf　Thenも書けます。

優良可の第二弾。IF関数と比較もします。

問題：設備、取扱、法規の３つの問題があり全体の合計が６割以上で合格。ただし、３つのどれか40より低い点数があると不合格になる。

以下は数式バーに書いたIF関数の計算式です。テーブルを使っているのでセル番地ではなく項目名になります。それにしてもセルの中に入れる時に何度もセルの指定が必要ですね。

=IF(SUM(テーブル[@[設備]:[法規]])<180,”不”,IF([@設備]<40,”不”,IF([@取扱]<40,”不”,IF([@法規]<40,”不”,”合”))))

以下は、標準モジュールに書いたユーザー定義関数/カスタム関数/自作関数を使っている状態、数式バーの状態です。セルの指定は３つですから使いやすい。

=hanntei([@設備],[@取扱],[@法規])

以下を標準モジュールに書いています。文書になっているので読みやすい。コメントも簡単に書けます。

Function hanntei(setu, tori, hou)
    If setu + tori + hou < 180 Then
        hanntei = "不"
    Else
        hanntei = "合" '以下の判定前にとりあえず合
        If setu < 40 Then hanntei = "不" 'Thenの内容が簡素なら一行の方が見やすい
        If tori < 40 Then hanntei = "不"
        If hou < 40 Then hanntei = "不"
    End If
End Function

書いたユーザー定義関数がなくならないように、マクロが有効なブックで保存が必要です。

以下は、サンプルの表の作成とIF関数の入力をするマクロ

Sub 表作成()
   If VBA.MsgBox("選択しているワークシートに上書きします！！！", vbOKCancel) = VBA.vbCancel Then Exit Sub

   If ActiveSheet.ListObjects.Count = 1 Then ActiveSheet.ListObjects(1).Delete

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "名前"
    ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "設備"
    ActiveCell.Characters(1, 2).PhoneticCharacters = "セツビ"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "取扱"
    ActiveCell.Characters(1, 2).PhoneticCharacters = "トリアツカイ"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "法規"
    ActiveCell.Characters(1, 2).PhoneticCharacters = "ホウレイ"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "判定"
    ActiveCell.Characters(1, 2).PhoneticCharacters = "ハンテイ"

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "名前１"
    ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A８"), Type:=xlFillDefault
    Range("A2:A10").Select
    
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "56"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "87"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "87"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "76"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "76"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "89"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "54"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "67"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "34"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "90"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "89"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "78"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "67"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "56"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "45"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "67"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "48"
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "68"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "43"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "23"
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "67"
    Range("D8").Select
    ActiveCell.FormulaR1C1 = "87"
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "98"
    
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$e$８"), , xlYes).Name = _
        "テーブル"
        
    Range("e2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUM(テーブル[@[設備]:[法規]])<180,""不"",IF([@設備]<40,""不"",IF([@取扱]<40,""不"",IF([@法規]<40,""不"",""合""))))"
'エラーになったら＜の4箇所を半角文字にして下さい。
End Sub

カテゴリー: 9 セルの中で使うユーザー定義関数の作成　＜VBA＞ | コメントする

myTemplate　配列=Range代入

投稿日: 2020年2月8日 作成者: ooooo

配列数式で扱う２次元配列の処理は、For Eachで処理することが多いが初めから配列に入れて処理する方法もある。

'配列にRangeのデータを入れる
Function aa(r As Range)
    Dim ar()
    ar = r 'セルの値が入る
    Dim i As Integer, j As Integer
    For i = 1 To UBound(ar, 1) '２次元配列の１番目、行。１から始めるのはｒがRangeだから
        For j = 1 To UBound(ar, 2) '２次元配列の２番目、列
            ar(i, j) = i & "," & j & "," & ar(i, j)
        Next
    Next
    aa = ar
End Function

For i=1から始まることに注意。配列の要素番号は0から始まるのが普通だが、動的配列ar()にRangeを入れると要素番号/インデックス番号は1から始まる。

データベースのような表からデータの取り出しは行単位になる。ある条件で取り出して別の配列arrに入れる。行単位に処理するのでFor文の列の処理の前にIf条件文を入れることになる。

'配列にRangeのデータを入れる
Function myTenplate(r As Range)
    Dim ar() 'Rangeの範囲のデータを入れる動的配列
    ar = r 'セルの値が入る
    Dim i As Integer, j As Integer
    j = 1 'For文の中で使い始めで0だと飛ぶため。
    Dim arr(1 To 100, 1 To 10) '関数値用。スタートを１にして列のjをそのまま使う。1000くらいにやると重く、飛ぶことも。
    Dim ii As Integer 'arrの行番号用、列はjを使う
    For i = 1 To UBound(ar, 1) '２次元配列の１番目、行。１から始めるのはｒがRangeだから
        If i = 1 Or i = 3 Then '条件、１と３の行だけ
            ii = ii + 1 'スタートが１なので先に１にしておく
            For j = 1 To UBound(ar, 2) '２次元配列の２番目、列
               arr(ii, j) = i & "," & j & "," & ar(i, j)
            Next
        End If
    Next
    myTenplate = arr
End Function

２次元配列の処理は頭の中で処理できるほど簡単では無い。難しくしているのがi,jなどの変数とそれが位置を意味しており、ar(i,j)で一つのデータ。２次元配列の音からデータが２つあると勘違いしてはいけない。私だけかもしれないが。

時間はかかってもワークシート関数を駆使して「やっぱできない」ってことはプログラムの世界ではありませんし、使った時間はスキルアップになります。

カテゴリー: 9 セルの中で使うユーザー定義関数の作成　＜VBA＞ | コメントする

日付とシリアルナンバー

投稿日: 2020年2月7日 作成者: ooooo

シリアルナンバーとは、日付を意味する数値。単なる数値。シリアルナンバーを求める関数は、2種類ある。

  1. Serial：引数に、年月日を数値で渡す。関数名、DateSerial、TimeSerial
  2. Value：引数に、日付の文字列を渡す。関数名、DateValue、TimeValue

Serialの例。年、月、日、時、分、秒からシリアルナンバー/連番を得る。以下は、イミディエイトウィンドウ。？から始まる行は入力している。「’」から始まる文字列はコメント（月末）

?vba.DateSerial(2018,1,1)
2018/01/01 
?vba.TimeSerial(1,5,2)
1:05:02
?dateserial(2020,4,1)-1    '月末
2020/03/31

Valueの例。文字列からシリアルナンバー/連番を得る。

?vba.DateValue("2000年5月1日")
2000/05/01 
?datevalue("2000-3-4")
2000/03/04 
?datevalue("2000/3/4")
2000/03/04 
?vba.TimeValue("１２時")
12:00:00 
?vba.CDbl(vba.TimeValue("12時"))
 0.5

見た目の変更（書式設定）をするVBAライブラリのFromat関数。

a=vba.DateSerial(2020,4,1)
?vba.Format(a,"aaa")
水
?format(a+7,"d/m(aaa)")
8/4(水)
?format(now,"yyyy/m/d h:m:s")
2018/10/10 23:0:28

ワークシート関数でも、日付に関する関数は同様にあるが、一部違う点がある。ワークシート関数には、DateSerialとTimeSerialが無い。引数はヒントが出るので覚える必要が無いが比較しておく。

ワークシートに日付を文字列として入れるとシリアルナンバーになって入る。表示形式が日付や時間でもデータの中身を確認する必要がある。セルに日付の文字列として「平成３０年１月１日 １０時５０分」などと入れる場合は、セルの入力の先頭「’」を入れてから入力する。

カテゴリー: 3 習熟のためにイミディエイウィンドウを使う | コメントする

Z出力って？Rangeコレクションに慣れる

投稿日: 2020年2月7日 作成者: ooooo

複数のセルの扱いは、 プロシージャ作りの基本です。Rangeコレクションを配列のように扱う？のです。メモリ上で隣り合わせに連続にあるんでおんなじようなもんです。開始番号が配列が０、コレクションは１というのが違う。

だから、Z入力の次はZ出力です。

Offsetを使ってRangeを配列？みたいに使いましょう。

Offset、For Eachに慣れましょうね。覚えましょう、学習しましょう。じゃなくて書いて実行してみましょう。

カテゴリー: 9 セルの中で使うユーザー定義関数の作成　＜VBA＞ | コメントする