Excel VBA ノート(サンプルコード)

Hide Tamura の Excel VBA ノート

Excel-VBA ソフト作り ファイルを開く時に自動で動くプログラム Workbook_Open Workbook_BeforeClose Auto_Open Auto_Close

ファイルを開く時に自動で動いて欲しいプログラムがあります。

例えば・・・

ファイルを起動した時には、必ず最初に表示させたいワークシートがある場合。
Enterキーを押した時にセルを移動する方向を、下方向ではなく右方向にしたい場合。
ユーザーフォームを表示させたい場合。
などなど。

下の例では、メッセージボックスを表示させていますが、ここに動かしたい処理を書きます。


ファイルが開く時に自動で動くプログラム(2つあります)

Private Sub Workbook_Open()
    MsgBox "ワークブック オープン"
End Sub

Sub Auto_Open()
    MsgBox "オート オープン"
End Sub

動く順番は、Workbook_Open の次に、Auto_Openです。



ファイルが閉じる時に自動で動くプログラム(2つあります)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "ワークブック クローズ"
End Sub

Sub Auto_Close()
    MsgBox "オート クローズ"
End Sub

動く順番は、Workbook_BeforeClose の次に、Auto_Closeです



Workbook_Open と、Workbook_BeforeClose を書く場所

標準モジュール内ではなく、
「VBAProject」→「Microsoft Excel Objects」→「ThisWorkbook」の中に書きます。

「ThisWorkbook」の上部左のリストから「Workbook」を
「ThisWorkbook」の上部右のリストから「Open」「BeforeClose」を選択します。



Auto_Open と、Auto_Close を書く場所

標準モジュールの中に書きます。
「VBAProject」→「標準モジュール」→「Module1(など)」の中に書きます。



私は、Auto_Open が動作しなかった経験があり、それ以来、Workbook_Open と、Workbook_BeforeClose のみを利用しています。

Excel-VBA トラブル解決方法:Excelを閉じると「VBAProject パスワード」が表示される(DropBox使ってる?)

突然、Excelを閉じると「VBAProject パスワード」が表示されるようになりました。

このトラブルの解決方法について

発生条件 Excel

Excel VBA を保護するため、プロジェクトのプロパティに保護(プロジェクトのプロパティ表示のためのパスワード)をかけている場合。
UserFormを利用している場合。

発生パソコンの共通点

この現象が起きた全てのパソコンに共通して DropBox がインストールされていた。

解決方法

DropBoxを一時的に終了させてみると、この問題は解消しました。
アンインストールまでする必要はありません。

DropBox の終了方法

タスクバーの右に小さいDropBoxのアイコンがありますので、それをクリックして、歯車のようなアイコンからメニューを表示させて、[DropBox を終了] を選択すると、終了します。
再度、DropBox を利用状態にするには、スタートメニューからDropBoxを起動できます。

想像ですが・・・

最近、DropBox フォルダの中の Excelファイルを直接起動してみると、ファイルの右にアイコンが表示されて、なにやら機能が追加されています。DropBox は、Excel に関連するAPIなどを利用しているのかもしれませんね。

プロジェクトのプロパティに保護をかけないという方法

この現象が起きる条件が、「Excel VBA を保護するため、プロジェクトのプロパティに保護(プロジェクトのプロパティ表示のためのパスワード)をかけている場合」 なので、特に必要がなければ、パスワードをかけないこと。
そうれば、DropBox を利用していても問題は発生しませんから。(^^♪


以上です。
 

Excel-VBA CSVファイルを書き出す時にはセル内の改行に注意 Chr(10) Replace

CSVファイルを書き出す時には、セル内に改行がある場合があるので注意が必要です。
CSVファイルの行中の思わぬ場所で改行されてしまいます。

改行は、vbCr  vbCrLf  vbLf  vbNewLine  Chr(10)  など色々な書き方がありますが、セル内の改行を取る時には、Chr(10) を消します。

Chr(10) を取ると、改行が無くなるので、読み難くなる為、スペースに置き換えています。

以下、Replace関数を使って、Chr(10) を " " に置き換えている例です。


Dim i As Long
Dim lngEndRow As Long, r As Long

    r = Application.Rows.Count
    
   'CSV用データで、金額の入っている行を最終行とする
    lngEndRow = Sheets(Sht_work1).Cells(r, "BP").End(xlUp).Row

    For i = 1 To lngEndRow
        
        '摘要中の改行コードを消去して1行に戻す
        Sheets(Sht_CSV).Cells(i, "Y").Value = Replace(Sheets(Sht_CSV).Cells(i, "Y").Value, Chr(10), " ")
    
    Next i


以上
 
 

Excel-VBA VBAのお仕事。VBAで稼ぐ!

bizvba_160このブログを訪問をされた方の多くは、VBAについて調べていて、偶然にこのブログに辿り着いたのでしょう。  っと、いう事は、既にVBA利用者という事ですね。

自分の仕事を楽にする為だったり、職場の仲間に頼まれたり、仕事として依頼を受けているのかもしれませんね。

私の場合は、その全てです。(^^♪

「せっかくVBAを勉強しているのだから、これで少しは稼ぎたい」 と思っている人も多いのではないでしょうか?

私は、仕事(有料)としてもVBAを書いています。

ほとんど紹介で仕事が入ってきていますが、Excel95 だったかな? マクロシートがあった頃からなので、かなり長いことやってますね。(^^♪


仕事でVBAを書いていると言っても、特に詳しいVBAの知識を持ってはいるわけではありません。

きっと、このブログを見ている皆さんと同じように、書籍や、VBE(Visual Basic Editor)のヘルプで調べたり、ネットで調べたり。
(ネットで検索していたら、自分のブログが検索されて、そこに知りたい事が書いてあった事も。 (^^♪  )

VBA以外のプログラムは、全く分かりません。
(少し勉強しましたが、難しくて諦めました。 (^^♪ )



はじめは、私のVBAを使ったファイルを見たある会社からオリジナルソフトをVBAで作りたいと依頼が入りました。

そのソフトは、その会社の顧客(会社)に使わせる物であった為、多くの会社が使うことになりました。その為、それらの会社からも、「自社で使っているファイルもVBAで便利にしてもらいたい」 と、新たな依頼が入るようになりました。

ひとつのソフトが完成するまでの間には、色々な機能追加の要望が依頼者から出てきます。もっと便利に!もっと便利に!と思うのでしょう。ようやく完成した後も、機能追加、改良、関連ソフトの依頼などが入ってきます。

私の経験では、特別なソフトをExcelで作ろうというのでなければ、ご要望の多い機能は、だいたい共通していて、それらのプログラムを一通りマスターすれば、仕事として受けられるようになると思います。(^^♪ 

既にお使いになっているファイルに、VBAを利用することで作業が楽になり、時間も大幅に短縮されたなら、そのプログラムは大変喜ばれます。








   つづく・・・

   のんびり書いてます。
  
   別の記事に続けず、この記事を書き換えて更新します。


Excel-VBA ハイフンとカンマによる数値範囲の取得(例:1-20,30,40-50) 印刷範囲やCSV書き出し範囲で利用 Split

ユーザーフォームの TextBox3 に入力されたデータ
1-20, 30, 40-50, 70,
から、1以上20以下、30、40以上50以下、70 と導き出す事で、印刷範囲やCSV書き出し範囲として利用する
文字や空白、ゼロは無視する事で、余計なカンマにも対応

Sub csvtest()
    Dim tmp As Variant, tmp2 As Variant
    Dim i As Long
    Dim lngS As Long, lngE As Long
    Dim strTextBox As String
    
    Dim lngInStr As Long
    
    '-------------------------
    
    strTextBox = Me.TextBox3.Text
    
    If strTextBox = "" Then
        MsgBox "入力データありません", vbExclamation
        Exit Sub
    End If
    
    '配列に区切り文字「,」のデータを所得
    tmp = Split(strTextBox, ",")
    
    For i = 0 To UBound(tmp)
    
        '-------------------------
        
        'その配列文字を、再度、区切り文字「-」で別の配列に取得
        lngInStr = InStr(tmp(i), "-")
    
        '配列に「-」があったら(範囲指定がある)
        If Not (lngInStr = 0) Then
            
            tmp2 = Split(tmp(i), "-")
        
            lngS = Val(tmp2(0))
            lngE = Val(tmp2(1))
        
            '開始数字より終了数字の方が大きくないといけない
            'ゼロではいけない
            If Not (lngS < lngE) Or lngS = 0 Or lngE = 0 Or UBound(tmp2) > 1 Then
                MsgBox "設定に誤りがあります", vbExclamation
                Exit Sub
            End If
        
            MsgBox lngS & "以上 " & lngE & "以下", vbInformation
        
        '配列に「-」が無かったら(範囲指定がない)
        Else
        
            'ゼロではいけない
            If Not (Val(tmp(i)) = 0) Then
                MsgBox Val(tmp(i)), vbInformation
            End If
        
        End If
    
    Next i

End Sub

 

Excel-VBA 問題・トラブル・バグ 2014年12月10日のWindows Update でActiveXオブジェクトが挿入できない。コマンドボタンが押せない。MSForms.exdを削除! ( MSForms.exd KB2596927 KB2553154 KB2726958 )

2014年12月10日のWindows Update の後、ActiveXオブジェクトが使えなくなった。

「オブジェクトが挿入できません」 と表示されたり、コマンドボタンが押せないなどのトラブルが発生しました。

対応方法を調べ、トラブルを回避できました。私の行った方法を2つ書いておきます。
方法1がダメだった時には、方法2で使えるようになりました。
自己責任で試してみてくださいね。(*^_^*)

方法1:

マイクロソフトのホームページに修正方法が掲載されました。
http://support2.microsoft.com/kb/3025036/ja

実際にやってみました。Temp から、MSForms.exd というファイルを消すという方法になります。

まずは、
スタート>コンピューター>OS(C:)>ユーザー>(ユーザー名)>AppDAta>Local>Temp
の画面に移動します。


スタートから、「コンピューター」を選択します。(下図) 

s


「 OS(C:) 」を選択します。(下図) 

2


「 ユーザー 」を選択します。(下図) 

3


「ユーザー名」を選択(下図) 注意: Hide は私の名前ですので、あなたのPCには無いはず。(^_^)

4


「AppData」を選択(下図)

5


「 Local 」を選択(下図)

6


「Temp」を選択(下図)

7

これで、スタートから、
コンピューター>OS(C:)>ユーザー>(ユーザー名)>AppDAta>Local>Temp
まで、辿り着きました。

右上に、Tempの検索窓があります。ここに、MSFORMS.exd というファイル名を入力して検索します。

8


MSForms.exd ファイルが検索されました。このファイルを削除します。(下図)

下の例では、二つのファイルを検索されました。キーボードのCtrlキーを押しながら選択すると、複数のファイルを同時に選択できます。ファイルが選択された状態で、マウスの右クリックを押し、表示されたメニュから、「削除」を選択実行します。

10

そして、最後に、パソコンの再起動をかけます。


これで、ちゃんと動くようになりました。


それでも、動かなかった時は、方法2で使えるようになりました。

方法2:

☆この方法でも動くようになりましたが、方法1の方が良いと思います。

Windows のスタートから、次のように選択します。

スタート>コントールパネル>プログラム>インストールされた更新プログラムを表示

アップデートの一覧から

Office2007 の場合は KB2596927
Office2010 の場合は KB2553154
Office2013 の場合は KB2726958

( 表示例: Security Update for Microsoft Office 2010 (KB2553154) 32-Bit Edition )

というファイルをアンインストールすると使用可能になるようです。

私の場合は、Office2010 と 2013 の両方がパソコンに共存していますが、Office2010 のファイルだけが見つかりました。 これを削除して使用可能になりました。

「インストールされた更新プログラムを表示」で表示されるファイルは多数ありますが、画面の右上に、検索窓があり、ファイル名で検索が可能です。

後日のWindows Update で再びこの削除したファイルがインストールされてしまいました。なので、上記の方法1が良いと思います。

以上です。 




Excel-VBA ユーザーフォームを「Esc」キーで閉じる CommandButton CancelプロパティをTrue

ユーザーフォームをキーボードの「Esc」キーで閉じるには、

ユーザーフォームに CommandButton を使用して、「閉じる」ボタンを作ります。

このCommandButton のプログラムに、Unload me と書いておきます。

Private Sub CommandButton1_Click()
    Unload Me
End Sub

このボタンで、その他の処理を行う必要があれば、最後の行に書いてください。

この CommandButton のプロパティで Cancel を True にします。

これだけです。

cancel






Excel-VBA セルの値が文字列かどうかの判定 TypeName

Sub Sample6()

    Dim i As Long
    
    For i = 1 To 5
    
        If TypeName(Cells(i, "A").Value) = "String" Then
        
            MsgBox "このセルのデータは文字列です"
        
        End If
    
    Next i
    
End Sub

注意: TypeName(Cells(i, "A").Value) = "String"
.value は省略してはいけない。
StringのSは大文字でなくてはいけない。TypeNameの結果のまま使うこと。

その他のタイプを判定したい場合は、TypeName(Cells(i, "A").Value) の結果をイコールで結んで判定すれば良い。






 

★Excel 2013対応 Excel-VBA ウィンドウ状態を設定 WindowState , DisplayFullScreen

シングルステップでプログラムを動かして、変化を確認してください。

'Excel2013以上のバージョンでテストしてください
'Excel2013では、アプリケーションウインドウの中でブックがxlNormalサイズになることはない
'Excelブックのウインドウとアプリケーションのウインドウは一致
Private Sub Macro2()
    
    'アプリケーションウインドウを 最小、最大、元のサイズ
    Application.WindowState = xlNormal
    Application.WindowState = xlMinimized
    Application.WindowState = xlMaximized
    
    'Excel2013では、アプリケーションウインドウとほぼ同じ動き
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlMinimized
    ActiveWindow.WindowState = xlMaximized
    
    'ウインドウの全画面表示、元のサイズ
    Application.DisplayFullScreen = True
    Application.WindowState = xlNormal

End Sub

'Excel2010以下のバージョンでテストしてください
Private Sub Macro1()
    
    'アプリケーションウインドウを 最小、最大、元のサイズ
    Application.WindowState = xlNormal
    Application.WindowState = xlMinimized
    Application.WindowState = xlMaximized
    
    'ウインドウ内のブックを 最大、最小、元のサイズ
    ActiveWindow.WindowState = xlMaximized
    ActiveWindow.WindowState = xlMinimized
    ActiveWindow.WindowState = xlNormal
    
    'ウインドウの全画面表示、元のサイズ
    Application.DisplayFullScreen = True
    Application.WindowState = xlNormal

End Sub


Excel2013 と Excel2010 以下のバージョンで、同じようにアプリケーションウインドウをフローティング(xlNormal)状態にし、アプリケーションウインドウの中でブックを最大表示にしたい場合は、
ブックを最大にした後、アプリケーションをノーマル(xlNormal)状態にする

Sub Macro3()

    ActiveWindow.WindowState = xlMaximized
    Application.WindowState = xlNormal

End Sub
 

Microsoft Office ブログランキングへ


Excel-VBA ウインドウのサイズと表示位置の設定 Application.width .Height .Top .Left

Sub WindowSet()

    With Application
        .WindowState = xlNormal
        If .Width < 1000 Then .Width = 1000
        If .Height < 700 Then .Height = 700
        .Top = 20
        .Left = 50
    End With

End Sub

 



記事検索
Excel VBA ノートについて(注意事項)
このExcel VBA ノートは、Hide Tamura の個人的なVBAノートです。プログラムを再利用したり、コピペで入力の手間を省く為に作ったネット上のノートです。 その為、詳しい解説は書いておりません。エラー等のトラブルには責任は負いません。利用者の環境に合わせて書き換えてご利用ください。
ExcelVBA教室
Excel VBA 担当 Hide Tamura

Excel95? マクロシートがあった頃からExcelVBAを独学で利用しています。現在は、様々な企業様からVBAによる業務効率UPの為のお仕事を頂いております。既にお使いになっているファイルにVBAを利用することで、作業が楽になったり、時間も大幅に短縮されたなど、大変喜ばれています。

■VBA Expert
VBA Expert Standard Crown
ExcelVBA Standard(Odyssey)
Access VBA Standard(Odyssey)
Excel2002 VBA Standard(日本VBA協会)
■MICROSOFT OFFICE USER SPECIALIST
Microsoft Excel version2002 Expert
Microsoft Excel version2002


最新記事
Excelで作る経営計画
Excelで利益計画を立てましょう!会社にいくらの利益が必要で、その為の売上高は?

Excelで作る経営計画
Excel 関数 ノート
Excel関数を中心に、Excelの便利機能や、意外としらない使い方など書いていきます。

Excel 関数 ノート
Access VBAノート
Hide Tamura の個人的なVBAノートです。

頻繁に使うVBAコードなどを記録しコピペして使う為に作りました。お役に立てるようでしたら、お使いください。

Access VBAノート
アクセスカウンター

    • ライブドアブログ