【Excel VBA】フォーム内のモジュール変数とプロシージャ


Warning: Use of undefined constant user_level - assumed 'user_level' (this will throw an Error in a future version of PHP) in /home/take1mg/www/plus1/wp-content/plugins/ultimate-google-analytics/ultimate_ga.php on line 524

勘違いをしたままずっと長いこと思い込んでいることって割とあるのだけれど、それがそんなに不利益になる事柄でなければ大したことがない。
しかし、今回はものすごく損をした気分になったので覚書投稿しておきます。

割と長いことエクセルのVBAをいじってきているけれども、ずっと「標準モジュールからフォームに変数を受け渡しするにはパブリック変数しかない。」と思い込んできた。ところがつい先日それが間違っていたことを知った。「フォームとフォームの間で直接変数を受け渡しできないので、パブリック変数を使いましょう。」ということであったらしい。
いや、これもあまり正確でない。確かに標準モジュールからフォームに変数そのものを渡すにはパブリック変数しかないから。だが、値を受け渡しするだけならばなんのことはない。割と普通にできたのだった。

つまりフォームモジュール内にパブリックで変数を定義してやれば、それは「フォーム名.変数名」でどこからでもアクセスできるということ。そしてそれはフォーム内のプロシージャでも同様であるということ。

以下サンプル。

フォームモジュール

Option Explicit

Public Test As Integer

Public Sub Add(num As Integer)
    Test = Test + num
End Sub

Private Sub CommandButton1_Click()
    Label1.Caption = Test
End Sub

Private Sub UserForm_Initialize()
    Label1.Caption = Test
End Sub

 

標準モジュール

Option Explicit

Sub aa()
    With UserForm1
        .Test = 2
        .Add 8
        .Show
    End With
End Sub

 

この時フォームのInitializeプロシージャは、最初に変数Testに値を放り込んだ時に実行される。よって、フォーム表示初期のラベルの値は「0」。

フォームのInitializeプロシージャが走るタイミングはよく「フォームが表示される時」となっているけれど、厳密にいうと「フォームが内部で呼び出されるとき」に1度だけ実行される。

【Excel VBA】結合セルを詰めてコピーペーストするマクロ


Warning: Use of undefined constant user_level - assumed 'user_level' (this will throw an Error in a future version of PHP) in /home/take1mg/www/plus1/wp-content/plugins/ultimate-google-analytics/ultimate_ga.php on line 524

近頃はマクロ制作のお仕事をよくいただきます。ありがとうございます。

先日いただいたお仕事で資料としてお預かりしたエクセルブックが「エクセル方眼紙」になっていて、セルのデータをコピーペーストしようとすると空欄が盛大に入ってくるので、なんとも扱いにくかったのでこのマクロを作りました。

エクセル方眼紙で作成されたものは大抵セル結合がたっぷり入っています。

そのままコピーしてペーストするとこの通り空欄だらけで、貼り付け先に入力しておいたデータを消してしまうことも…。

上の例のように3列のつもりで扱うと大変なことになってしまいます。

そこでマクロ。まずはメインのコード。

Sub Main()
    
    Dim add
    add = Split(Replace(Selection.Address, "$", ""), ":")
    
    Dim r, col
    r = getMergeRowsCount(add)
    col = getMergeColumnsCount(add)
    
    Dim rowCnt, colCnt
    rowCnt = UBound(r)
    colCnt = UBound(col)
    
    Debug.Print "area start address: ", add(0)
    Debug.Print "area end address: ", add(1)
    Debug.Print "rows count: ", rowCnt
    Debug.Print "columns count: ", colCnt
    Debug.Print
    
    Dim data
    ReDim data(rowCnt, colCnt)
    Dim i, j
    For i = 0 To UBound(r)
        For j = 0 To UBound(col)
            data(i, j) = Cells(r(i), col(j)).Value
        Next j
    Next i
    
    Call putData(data)
End Sub

 

メインから呼び出している3つのプロシージャ。2つは結合の主体となっているセルの行列番号を特定するためのもの。最後の一つはとりあえず新しいシートを追加してデータを貼り付けたのち、それをコピーしてクリップボードに取り込むためのもの。

Private Function getMergeRowsCount(add) As Variant
    Dim r
    ReDim r(0)
    r(0) = Range(add(0)).Row
    Dim inc, j: j = 0
    Do While r(j) <= Range(add(1)).Row
        Debug.Print "rows " & j; ": " & r(j)
        inc = Cells(r(j), Range(add(0)).Column).MergeArea.Rows.Count
        j = j + 1
        ReDim Preserve r(j)
        r(j) = r(j - 1) + inc
    Loop
    If UBound(r) > j - 1 Then
        ReDim Preserve r(j - 1)
    End If
    getMergeRowsCount = r
End Function

 

Private Function getMergeColumnsCount(add) As Variant
    Dim col
    ReDim col(0)
    col(0) = Range(add(0)).Column
    Dim inc, i: i = 0
    Do While col(i) <= Range(add(1)).Column
        Debug.Print "columns " & i; ": " & col(i)
        inc = Cells(Range(add(0)).Row, col(i)).MergeArea.Columns.Count
        i = i + 1
        ReDim Preserve col(i)
        col(i) = col(i - 1) + inc
    Loop
    If UBound(col) > i - 1 Then
        ReDim Preserve col(i - 1)
    End If
    getMergeColumnsCount = col
End Function

 

Private Sub putData(data)
    Worksheets.add
    Dim myName As String
    myName = ActiveSheet.Name
    With Sheets(myName)
        .Activate
        Dim nr, ncol
        nr = Selection.Row
        ncol = Selection.Column
        Dim myRange As Range
        Set myRange = .Range(.Cells(nr, ncol), .Cells(nr + UBound(data, 1), ncol + UBound(data, 2)))
        With myRange
            .Value = data
            .Copy
        End With
    End With
'    Application.DisplayAlerts = False
'    Sheets(myName).Delete
'    Application.DisplayAlerts = True
End Sub

 

最後のところで追加したシートを削除しておこうと思ったんですけど、コピーした後に余計なことをするとコピーペーストモードが解除されてしまって貼り付けできないので、仕方なくそのままです。どこかにデータを貼り付け後に手動でシート削除してください(笑)
このマクロを使うと先ほどの上の画像の貼り付け結果もこの通り。

マクロを使って普通になってくれたデータ

サンプルのファイルはこちらからダウンロードできます。

サンプルファイルのダウンロード

 

キーボードショートカット:その1


Warning: Use of undefined constant user_level - assumed 'user_level' (this will throw an Error in a future version of PHP) in /home/take1mg/www/plus1/wp-content/plugins/ultimate-google-analytics/ultimate_ga.php on line 524

ちょっとキーボードショートカット(Macではこう呼びますが、Windowsではショートカットキーと言いますね。)についてまとめてみることにします。私がよく使うものを中心に、MacとWindowsと両方載せておきます。

表記については、+は「押しながら」、〜は「押してから」って感じです。

全体的な操作に関するキーボードショートカット

操作 Mac Windows
アプリケーションの終了 command ⌘+Q Alt+F4
開く command ⌘+O Ctrl+O
ウィンドウを閉じる command ⌘+W Ctrl+W
保存(上書き保存) command ⌘+S Ctrl+S
すべて選択 command ⌘+A Ctrl+A
プリント(印刷) command ⌘+P Ctrl+P
取り消し(元に戻す) command ⌘+Z Ctrl+Z
検索 command ⌘+F Ctrl+F
コピー command ⌘+C Ctrl+C
カット(切り取り) command ⌘+X Ctrl+X
ペースト(貼り付け) command ⌘+V Ctrl+V
アプリケーションの切り替え command ⌘+TAB Alt+TAB
新しいウィンドウを開く command ⌘+N Ctrl+N
最大化 (なし) Alt+スペースX
ドックにしまう(最小化) command ⌘+M Alt+スペースN
隠す command ⌘+H (なし)

全体的な操作に関するものはこのくらいですかね。どれもよく使います。これらの操作をするときには、ほとんどショートカットキーでやってんじゃないですかね。右手でマウスかトラックパッド、左手でショートカットキーってな感じです。

なんか他にもよく使う組み合わせがあれば、教えていただければ嬉しいです。結構こういう小技系、好きなんですよね。

ワード:白紙のページが印刷される


Warning: Use of undefined constant user_level - assumed 'user_level' (this will throw an Error in a future version of PHP) in /home/take1mg/www/plus1/wp-content/plugins/ultimate-google-analytics/ultimate_ga.php on line 524

 

たまに受ける素朴なご質問に「ワードで印刷するときに白紙のページが最後に出てくるんだけど、なぜだろう?」というのがあります。

結論から言うと、最後のページに文字を入れているからなんですが、ひらがなやカタカナ、漢字などの実際に読む文字ではなくて、ほとんどの場合は改行文字などの編集記号が最終ページに入ってしまっています。

続きを読む

【Excel VBA】「とりあえず保存する」マクロ(アドイン)【配布】


Warning: Use of undefined constant user_level - assumed 'user_level' (this will throw an Error in a future version of PHP) in /home/take1mg/www/plus1/wp-content/plugins/ultimate-google-analytics/ultimate_ga.php on line 524

こんにちは。お久しぶりの更新です。

少し前まで写真集計のプログラムを必死に作っていましたが、それもひとまず完成し少し落ち着いています。落ち着いたのはいいのですが、ちょっとなんだか抜け殻のようになっていたりします。

さて、今日はふと思いついてExcelのアドインを作成してみました。

Wordだと「名前を付けて保存」するときに、自動的に1行目の文字列をファイル名として設定してくれ、まあとにかくとりあえず保存しておくのにファイル名を考えずにすむので便利です。

ところが同じMicrosoftのOffice製品なのに、Excelにはこの機能がありません。仕方ないので、ちょっと焦っているときなど新規作成したときの名前のまま保存したりすると、「Book1.xlsx」がいっぱいできたりします。しかも、同じフォルダ内には同じ名前で保存できないので、いろんなところに「Book1.xlsx」が散らかっていたりします。

特に急いでいないときでもファイル名を考えるのって結構面倒だったりします。わたしの場合は基本的にはA1のセルに入力した文字列がファイル名になってくれるととても便利です。

そこで、ないものは作ろう!ということで作りました。 続きを読む