3日がかりのその仕事、3分で終わらせる方法教えます!
パソコンスキルの心技体

複数セルに値を入れる処理を高速化したい – Excelマクロ・VBA

2012年1月5日
  • このエントリーをはてなブックマークに追加
  • follow us in feedly

エクセルマクロ・VBA達人養成塾 小川です。

キューバ旅行記、その74です。

キューバ屈指の外人向けリゾート地、バラデロから、シュノーケリング・ダイビングのポイント、「Playa El Coral」にて。

エクセルマクロ達人養成塾塾長ブログ-貨物船が見えます。港湾現場出身の僕には興味深い。

貨物船が見えます。港湾現場出身の僕には興味深い。
ということで、着替えて海へ。

ダイビングでは、特に支障もなく。
1時間20分ほど潜っていました。水深11メートルまで潜ったのだとか。

水中の写真とかはありません。
魚にえさをやっている写真ところの記念写真はあるのですが、たいした写真ではないのと、PCにまだ入れてないので、まあそのうちに。

エクセルマクロ達人養成塾塾長ブログ-というわけでダイビングの後、帰ってきました。

というわけでダイビングの後、帰ってきました。
送り迎えしてくれた車を、記念にパチリ。

ところで、このツアーのみなさん、なんかすごい良心的な人たちでして。

この日、更衣室にタオルを忘れてきたのですが。

「なくなった」とも何とも言っていないのに、翌日、代理店さんに届けてくれました。

物資のないキューバなのに。「ここは、日本か?」と言いたくなるくらい。すっげーカンドーしました。

エクセルマクロ達人養成塾塾長ブログ

ということで。
夕方にはさらにスカイダイビングの申し込みをしているのですが、こちらは現金でなければダメということらしいので、両替に向かいます。

旅行代理店で自転車を貸してもらえたので、自転車に乗ってウロウロ。しかし、暑い!

エクセルマクロ達人養成塾塾長ブログ

エクセルマクロ達人養成塾塾長ブログ

そして、道中見かけたトカゲ。

エクセルマクロ達人養成塾塾長ブログ

トカゲ拡大。
ついつい、見かけると、撮ってしまうんですよね。そして、アップしたくなる(汗

この、がんばって生きている感漂う表情がなんともカワイイ ヾ(´ー`)ノ

エクセルマクロ達人養成塾塾長ブログ

目抜き通り近くにあるショッピングモール。

エクセルマクロ達人養成塾塾長ブログ

そして、どこで気まぐれに海岸線に向かってもこんな感じのビーチ!
まさに天国 ヾ(´ー`)ノ

塾長のキューバ旅行記、最初から読みたい方はこちらから


複数セルに値を入れる処理を高速化したい

12月中旬くらいから継続して添削依頼をいただいていたある受講生の方から、大晦日に、こんなメールをもらいました。

小川先生:

こんばんわ、○○です。

休日は追いつくチャンスっていうことで、この正月休みに1日中マクロの勉強しています。
(昔、僕は休日は追い越すチャンスだと思ってました。逆だったんですね。。。)

ここ2日間は、上級DVDでの勉強と今まで添削依頼をお願いしていたマクロの改良に時間を注いでいます。
subプロシージャをFunctionプロシージャで書き換えたり引数付きsubプロシージャを用いたり、動的配列を連想配列にしたりなどなど。。

今回添削依頼をお願いするスケジュール作成のマクロも小川先生からのご指摘がありましたように、動的配列を連想配列に書き換え、いくつか入力制限もかけました。
以前添削依頼しました粒子径解析プログラムも上級セミナーの演習がてらFunctionプロシージャや引数付きsubプロシージャで書き換えたりしています。

今回添削依頼をお願いするマクロは前回と比較して以下の点を改良しました。

・カウンター変数の名前の付け方
・動的配列を連想配列に変換
・入力制限を実施

お忙しい中大変申し訳ありませんがどうぞよろしくお願い致します。

どういうマクロかというと。

簡単に言うと。

○あらかじめ、祝日の日付と祝日名を記載したリストがある
○年月を指定すると、その年月の土日、祝日には印のついたカレンダーが作られる
○ただし、その印は、ひとつの日付あたり、複数箇所に作られる

という感じのもの。

その制作過程で出てきた、ちょっと知っておくとおもしろいかな、と思える箇所がいくつかあったので、ご紹介します。

例によって、超初心者、初心者向けではないかもしれませんが。。
(超初心者、初心者の方は、イチから説明すると時間がかかるので、達人養成塾のセミナーを受けに来てください

まずは、簡単なところで、土日だけ所定の記号を入力する、というところまで。

いただいたコードの中の、ポイントになる部分だけを取り出してご紹介。

いただいたものは、こんな感じでした。「スケジュール」という名前のワークシートを作って、そのシートを開いた状態で実行してみてください。

Sub BaseCode()
    Dim wsSche As Worksheet
    Dim dtTo As Date
    Dim cHiduke As Long
    Dim cName As Long
    dtTo = #1/1/2012#
    Set wsSche = Worksheets(“スケジュール”)
    Do While Month(dtTo) = 1
        cHiduke = Day(dtTo)
        ‘入力月の日付を入力
        wsSche.Range(“C10”).Offset(, cHiduke).Value = Day(dtTo)
        ‘曜日を入力
        wsSche.Range(“C11”).Offset(, cHiduke).Value = WeekdayName(Weekday(dtTo), True)
        For cName = 13 To 43 Step 3
            If Weekday(dtTo) = 1 Or Weekday(dtTo) = 7 Then
                With wsSche.Range(“C” & cName).Offset(, cHiduke)
                    .Value = “○”
                    .Font.Size = 11
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
                End With
            End If
        Next
        dtTo = DateAdd(“d”, 1, dtTo)
    Loop
End Sub

まず、If文とFor Next構文については、順序は逆のほうが好ましいです。
(比較をするステップが実際何回発生するかを考えれば、自明)

ということで、以下。

Sub KaihenCode1()
    Dim wsSche As Worksheet
    Dim dtTo As Date
    Dim cHiduke As Long
    Dim cName As Long
    dtTo = #1/1/2012#
    Set wsSche = Worksheets(“スケジュール”)
    Do While Month(dtTo) = 1
        cHiduke = Day(dtTo)
        ‘入力月の日付を入力
        wsSche.Range(“C10”).Offset(, cHiduke).Value = Day(dtTo)
        ‘曜日を入力
        wsSche.Range(“C11”).Offset(, cHiduke).Value = WeekdayName(Weekday(dtTo), True)
        If Weekday(dtTo) = 1 Or Weekday(dtTo) = 7 Then
            For cName = 13 To 43 Step 3
                With wsSche.Range(“C” & cName).Offset(, cHiduke)
                    .Value = “○”
                    .Font.Size = 11
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
                End With
            Next
        End If
        dtTo = DateAdd(“d”, 1, dtTo)
    Loop
End Sub

さらに言うと、For cName = 13 To 43 Step 3 … Next の部分も、かったるい。

どうせ Offset を使うなら、僕なら、こうします。

Sub KaihenCode2()
    Dim wsSche As Worksheet
    Dim dtTo As Date
    Dim cHiduke As Long
    Dim cName As Long
    dtTo = #1/1/2012#
    Set wsSche = Worksheets(“スケジュール”)
    
    Dim rB As Range
    Set rB = wsSche.Range(“C13,C16,C19,C22,C25,C28,C31,C34,C37,C40,C43”)
    
    Do While Month(dtTo) = 1
        cHiduke = Day(dtTo)
        ‘入力月の日付を入力
        wsSche.Range(“C10”).Offset(, cHiduke).Value = Day(dtTo)
        ‘曜日を入力
        wsSche.Range(“C11”).Offset(, cHiduke).Value = WeekdayName(Weekday(dtTo), True)
        If Weekday(dtTo) = 1 Or Weekday(dtTo) = 7 Then
            With rB.Offset(, cHiduke)
                    .Value = “○”
                    .Font.Size = 11
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
            End With
        End If
        dtTo = DateAdd(“d”, 1, dtTo)
    Loop
End Sub

解説すると。

まず、以下のコードを実行してみてください。そうすると、以下の[1]の部分で言っていることが分かるでしょう。

Sub sample1()
    Dim rB As Range
    Set rB = wsSche.Range(“C13,C16,C19,C22,C25,C28,C31,C34,C37,C40,C43”) ‘[1]

    rB.Select
End Sub

.Offsetプロパティは、横方向に移動するときに便利。

以下で、理解できるかと。

Sub sample2()
    Dim rB As Range
    Set rB = Range(“C13,C16,C19,C22,C25,C28,C31,C34,C37,C40,C43”)

    Dim c As Long
    For c = 1 To 10
        rB.Offset(, c).Select
    Next
End Sub

あとは、ここで、For Next構文の中にIf文を入れ、土日かどうかを判定した結果が真のときだけ具体的な処理をするように記述しただけ。

当初受講生から受け取ったコードだと、その、「具体的な処理」を記述する箇所で、やたらループがくり返されていたので処理が重たかった。

それが、複数セルに一気に値を入れるようにしたことで、一気に処理が簡易化した。

キーワード

コメント

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

最新の記事

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド