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

Hide Tamura の Excel VBA ノート

Excel-VBA ExcelからAccessのデータ(テーブル, クエリ)をCSVファイルに書き出す

AccessにCSVを書き出す為のプログラム「CSV書き出し」を書き、それをExcel側のプログラムで実行する例

progani

Access側にCSVを書き出すプログラム書く
Const strFileName As String = "体重管理"

Sub CSV書き出し()
    DoCmd.TransferText TransferType:=acExportDelim, TableName:="tblデータ", _
        FileName:=Application.CurrentProject.Path & "\" & strFileName & ".csv"
End Sub

カレントデータベースと同じフォルダに書き出ししたい場合は、CurrentProject.Path を指定しないと、
「ツール」-「オプション」-「全般」の『既定のデータベースフォルダ』に書き出される

構文
式。 TransferText (TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

第3引数 TableName テキスト データのインポート、エクスポート、リンクを行う Microsoft Access テーブルの名前、または結果をテキスト ファイルにエクスポートする Microsoft Access クエリの名前を、文字列式で指定します。

第5引数 HasFieldNames  は、テキスト ファイルの 1 行目をフィールド名として使用する場合は、True (–1) を使います。テキスト ファイルの 1 行目をデータとして処理する場合は、False (0) を使います。この引数を指定しないと、False (既定値) が使われます。


Excel側で、Accessに書いたCSV書き出しプログラムを実行する
Const DB_Name As String = "\MY体重管理.accdb"

Sub CSV()
    Dim strPath As String
    Dim objACS As Object
    
    strPath = ThisWorkbook.Path & DB_Name
    
    Set objACS = CreateObject("Access.Application")
    
    With objACS
        .Visible = False  'アプリケーションを非表示
        .OpenCurrentDatabase (strPath)
        .Run ("CSV書き出し") 'Accessに書いたサブルーチン
        .CloseCurrentDatabase
        .Quit
    End With
    
    Set objACS = Nothing
    
    MsgBox "CSVファイルの書き出しが完了しました", vbInformation
    

Excel VBA教室 5 関数を作る(ユーザー定義関数)

bizvba_160Excelには、関数が存在するが、「ユーザー定義関数」として自分で関数を作ることができる

ユーザー定義関数の作成は、とても簡単です。

VBE を開いて(Excel で Alt + F11)、標準モジュールに、例として次のコードを入力してみてください。


Function 損益分岐点売上高(ByVal 固定費 As Long, ByVal 限界利益率 As Double)

    損益分岐点売上高 = Int(固定費 / 限界利益率)
    
End Function


Function 目標達成売上高(ByVal 目標利益 As Long, ByVal 固定費 As Long, ByVal 限界利益率 As Double)
    
    目標達成売上高 = Int((固定費 + 目標利益) / 限界利益率)
    
End Function

 ↓ こんな感じです。

vbe

通常、Sub ~ End Sub の間にプログラムを書きますが、ユーザー定義関数を作る時は、
Function ~ End Function の間に書きます。

Function の後ろのプロシージャ名が関数名になります。上記例では、「損益分岐点売上高」と「目標達成売上高」が関数名になります。

関数なので、プロシージャには引数が必要になります。引数で値をユーザーから受け取って、プログラムで計算して、計算結果を返します。
上記「損益分岐点売上高」のプロシージャでは、引数として「固定費」と「限界利益率」を受け取って計算しています。

プログラムした計算結果が、最終的にプロシージャ名に代入されるようにします。「=」で、プロシージャ名に代入しています。

これで、作成完了です。


では、Excel側でユーザー定義関数を使ってみましょう。

Excelに戻って、入力したいセルを選択してから、数式バーの 「fx」マークを押して「関数の挿入」ダイアログボックスを表示して、関数の分類を「ユーザー定義関数」にします。(下図)

dialog1

すると、関数名に、損益分岐点売上高と、目標達成売上高が表示されます。

まずは、損益分岐点売上高を選択してみます。
「関数の引数」ダイアログボックスが表示されるので、固定費に「13,600」を、限界利益率に「0.12」と入力してみます。結果は、113,333となります。(下図)

dialog2

セルには、「=損益分岐点売上高(13600,0.12)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。



次は、目標達成売上高を選択した場合です。
「関数の引数」ダイアログボックスが表示されるので、目標利益に「10,000」、固定費に「13,600」を、限界利益率に「0.18」と入力してみます。結果は、131,111となります。(下図)

dialog3

セルには、「=目標達成売上高(10000,13600,0.18)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。


ユーザー定義関数を作ることで簡単に計算できるようになり、また、セルに入力する算式も短くなるメリットがあります。VBAで、その他いろいろな関数を作ってみてください。





Excel-VBA シート上のテキストボックス内の文字を取りだす

Sub test2()

    MsgBox ActiveSheet.Shapes.Range(Array("TextBox 1")).TextFrame2.TextRange.Characters.Text
    MsgBox ActiveSheet.Shapes.Range(Array("TextBox 3")).TextFrame2.TextRange.Characters.Text
    
End Sub

環境によって異なると思いますので、マクロの記録機能を使って、
記録を開始し、シート上のテキストボックスを選択し、文字を書いてみると、

    ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "入力文字"

のような部分がありますので、この2行を繋げて書いています

ActiveSheet.Shapes.Range(Array("TextBox 1")).TextFrame2.TextRange.Characters.Text = "入力文字"

Excel-VBA ユーザーフォームのTextBoxに入力した文字を、別ブックのユーザーフォームのTextBoxでも表示する(テキストファイルに書き出し&読み込み)

ユーザーフォームのTextBoxに入力した文字を、別ブックのユーザーフォームのTextBoxでも表示する

progani


下は、UserForm3 の TextBox12 に入力した文字を、別ブックでも利用できるように、「更新情報.txt」というテキストファイルに書き出しています。
このテキストファイル(ここでは、更新情報.txt)は用意しておく必要はなく、自動で作成されます。
下のコードを、ユーザーフォームの「OK」ボタンや「Close」ボタンに書いておけばよいでしょう。


    '更新情報.txtを書き出し用に開く
    Open ThisWorkbook.Path & "\更新情報.txt" For Output As #1
    
    'UserForm3.TextBox12の値をファイルに書き込む
    Write #1, UserForm3.TextBox12.Value
    
    '更新情報.txtを閉じる
    Close #1


次に、別ブックのユーザーフォームでも表示されるように、下のコードを、ユーザーフォームのイニシャライズに書き込んでおきます。
ここでは、UserForm3 の TextBox12 に、「更新情報.txt」テキストファイルから読み込み、表示しています。
(もし「更新情報.txt」が無ければ、何もしません)


    Dim strFName As String
    Dim myText As String

    '読み込むテキストファイル名
    strFName = ThisWorkbook.Path & "\更新情報.txt"

    'ファイルが無ければ何もしない
    If Dir(strFName) = "" Then
    Else
        
        '読み込むテキストファイルを開く
        Open strFName For Input As #1
        
        'ファイルに書かれている文字を myText に取得
        Input #1, myText
        
        'myText の文字をユーザーフォームのテキストボックスにセット
        TextBox12.Value = myText
        
        'テキストファイルを閉じる
        Close #1
    
    End If
    

これによって、別ブックでも、ユーザーフォームのテキストボックスに同じ文字が表示されます。

また、テキストボックスの文字を書き換えた場合も、テキストファイルの文字が書き換わりますので、別ブックを開いても同じ文字に更新されます。



Excel-VBA 「自動保存」オンの時は「名前を付けて保存」のタイミングに注意

自動保存

上の図の左上にあるのが「自動保存」の機能。

これがオンになっている時には「名前を付けて保存」のタイミングに注意です。
これはVBAに限ったことではありません。

この機能がないExcelや、この機能がオフになっている場合は、データを消去してから「名前を付けて保存」すれば、元のブックにはデータが残りました。

しかし、「自動保存」オンになっている場合は、データを消去した時点でブックが保存されてしまいます。それに気づかずに「名前を付けて保存」すると、元のブックからデータが無くなっている事に慌てます。


例えば、『年度更新』の処理を行う場合、年度をひとつ上げてデータを消去すれば翌年用のブックになります。

この処理を「名前を付けて保存」してから行えば、元のブックからデータが消えることはありません。

しかし、この処理を行ってから「名前を付けて保存」してしまうと、自動保存により元のブックのデータも消えてしまうとになります。


使用者のExcelの環境により「自動保存」がオンになっている場合がありますので注意が必要です。というより、オンになっていると考えましょう。

今まで問題がなく使えていたVBAによる『年度更新』も、上記の理由から、突然データが消える可能性がありますので気を付けなくてはいけません。「どうせ別名で保存するんだから、年度更新前のバックアップなんて必要ない。」なんて思っていると大変なことになります。

Excel-VBA OneDriveとの同期で ActiveWorkbook.Path, ThisWorkbook.Path が変わるので注意

OneDriveのクラウドと同期されているデスクトップやフォルダなどで「名前を付けて保存」する場合には注意が必要です。

ここに Excelブックがあります。

ActiveWorkbook.Path は、

C:\Users\User名\OneDrive\デスクトップ\AAA

です。

同じ AAAフォルダ内に、「名前を付けて保存」します。

その「名前を付けて保存」したExcelブックを閉じずに ActiveWorkbook.Path を調べると、

https://d.docs.live.net/~/デスクトップ/AAA

になっています。

同じ AAAフォルダへのパスだと思って ActiveWorkbook.Path を使用しているとエラーになることがありますのでご注意を。

もちろん、ThisWorkbook.Path でも同じことになります。

「名前を付けて保存」したファイルを一度閉じて、再び開き直すことで、元のパスに戻ります。

Excel-VBA 列幅が足りないとRange.Findメソッドで検索できない

Range.Find メソッド (Excel) を使ってサーチする際、サーチ先の範囲の列幅が狭いと検索できない

例えば、以下はマイクロソフトのサイトに表示されている使用例ですが、下の例では、a1~a500 の範囲にある数値の2を検索しています。

この時 a列の幅が狭く、数値が ### と表示されていると、検索にかからないので注意が必要です。

With Worksheets(1).Range("a1:a500") 
    Set c = .Find(2, lookin:=xlValues) 
    If Not c Is Nothing Then 
        firstAddress = c.Address 
        Do 
            c.Value = 5 
            Set c = .FindNext(c) 
        Loop While Not c Is Nothing And c.Address <> firstAddress 
    End If 
End With 

そこで!
サーチ範囲の列幅が狭くなってしまっいる場合を想定して、次のような対策をすることにしました。

    '---------------------------------------------
    '科目一覧シートの科目コード列の幅を広げる
    '幅が狭く###になると、Find での sarchができなくなる為
    
    Worksheets(1).Columns("A:A").EntireColumn.AutoFit
    
    '---------------------------------------------


Excel-VBA Application.Run で実行時エラー'1004':

Application.Run を使って別ブックのマクロを実行する時のエラーです

progani


セルC17には、「出納帳.xlsm」というブック名が入っています。

    strSuitoName = Sheets(Sht_設定).Range("C17").Value
        Application.Run strSuitoName & "!UF3表示"

今までは、この書き方で、別ブック「出納帳.xlsm」に書かれている「UF3表示」というマクロを実行することができていました。

ところが、ある時から、エラーが発生するようになりました。

実行時エラー'1004':
マクロ '出納帳.xlsm!UF3表示'を実行できません。このブックでマクロが使用できないか、またはすべてのマクロが無効になっている可能性があります。

原因としては、Application.Run の後ろの書き方に問題があることが分かりました。
(パスを含む)別ブック名を、シングルクォートで挟み、次のように書き直すとエラーが解消されました。

        Application.Run "'" & strSuitoName & "'!UF3表示"


Excel-VBA オートフィルターによる非表示セルには、Findメソッドによる検索は通用しない。WorksheetFunction.CountIf

これまでは、条件に当てはまるセルがあるかどうかを検索する為に、Findメソッドを利用していました。

例えば、ある表の列にデータナンバーが入っていて、その列に検索したいデータナンバーがあるかどうかを調べる時など。

ところが、その表にオートフィルターを使用してしまうと、非表示になったセルに含まれるデータナンバーは、Findメソッドでは検索されないという問題が発生しました。

使用しているデータナンバーの最大値を調べる為、ワークシート関数も使用していましたが、こちらは非表示セルがあっても問題なく正しい結果が表示されています。ワークシート関数なら大丈夫なようです。


今回は、あるデータナンバーを検索し、そのセル番地を知りたいのではなく、既に使用しているかデータナンバーかどうかを調べたかったので、Findメソッドの使用は中止し、ワークシート関数の COUNTIFを使ってみました。

COUNTIF関数は、条件を満たすセルの個数を求める時に使用できます。つまり、検索したいデータナンバーが0にならなければ、既に使用しているナンバーという判断が可能になります。

WorksheetFunction.CountIf(Range("C:C"), lngNumb) = 0

となれば、使用されていないデータナンバーであることが判定できます。(lngNumbは、検索したい数値が入っている変数です)


セル番地まで調べたい場合は、非表示セルも含めたコピー方法で表を別の場所に移動させてから処理したり、表に行番号列を加えてからワークシート関数VLOOKUPを使うなども考えられます。

Excel-VBA 第一日曜日が何日かを調べる Weekday  

日付をWeekday 関数で曜日を調べます。vbSunday なら日曜日。

Sub FstSunday()
    Dim i As Long
    Dim lngYear As Long
    Dim lngMonth As Long
    Dim strDate As String
    Dim myWeekDay As String
    
    lngYear = Val(Range("C5").Value) '年
    lngMonth = Val(Range("C6").Value) '月
    
    For i = 1 To 7
    
        strDate = lngYear & "/" & lngMonth & "/" & i
        
        myWeekDay = Weekday(strDate)
        
        If myWeekDay = vbSunday Then
        
            Range("C8").Value = i '第一日曜日の日
            
            Exit For
        
        End If
    
    Next i

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

記事をうまくカテゴリー分け出来ていない事が多いので、↓下の記事検索で、キーワード検索してください。
記事検索
ExcelVBAマクロ作成代行


様々な企業、会計事務所、病院からマクロ作成代行を請け負っております。マクロ作成でお役に立てたら嬉しいです。まずはお気軽にご相談ください。
最新記事
Excel VBA おすすめ書籍
楽しくVBAを学びたければ、一押しは「大村あつし」さんの書いたVBA書籍です。とにかく分かりやすく、VBAが好きになると思います。



土屋和人さんの Excel VBA パーフェクトマスターは、これ一冊で広く学べる良い本です。全て読めば、かなり詳しくなります。辞書のように調べやすいので、困った時に助かります。


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コードなどを記録しコピペして使う為に作りました。お役に立てるようでしたら、お使いください。

アクセスカウンター