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

Hide Tamura の Excel VBA ノート

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

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


    Open ThisWorkbook.Path & "\更新情報.txt" For Output As #1
    Write #1, UserForm3.TextBox12.Value
    Close #1



下は、ユーザーフォームを表示する際に、ユーザーフォームのイニシャライズに書き込んでおきます。
ここでは、UserForm3 の TextBox12 に、「更新情報.txt」の1行目から読み込んで表示しています。
(もし「更新情報.txt」が無ければ、何もしません)


    Dim strFName As String
    Dim myPath As String

    strFName = ThisWorkbook.Path & "\更新情報.txt"

    If Dir(strFName) = "" Then
    Else
        Open strFName For Input As #1
        Input #1, myPath
        TextBox12.Value = myPath
        Close #1
    End If
    
End Sub


これによって、どのブックを使っても、UserForm3 の TextBox12 には同じ文字が入ります。

また、TextBox12 の文字を書き換えると、「更新情報.txt」の1行目が書き換わりますので、どのブックを開いても同じ文字が表示されます。

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':

セル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 閏年(うるう年)を判定 Day DateAdd

閏年は、調べたい年の、3月1日の前日の日にちを調べて、29日かどうかで判定する。

Sub 閏年判定()
    Dim lngYear As Long
    Dim lngFebDay As Long
    
    lngYear = Val(Range("C5").Value) '年
    
    lngFebDay = Day(DateAdd("d", -1, lngYear & "/3/1"))

    If lngFebDay = 29 Then
        Range("C9").Value = 1 '閏年
    Else
        Range("C9").ClearContents
    End If

End Sub


Excel-VBA セルに算式が入っているかどうか調べる HasFormulaプロパティ 

セルに算式が入っているかどうか調べたい。

セルの HasFormula プロパティが True なら、セルに算式が入っています

算式が入っていたらユーザーフォームのテキストボックのBackColorを変えてロックをかけたい。
(ロックするのではなく、Enabled = False にしてもいいが、文字が見えにくくなるね・・・)

ユーザーフォームのテキスボックスに1行分のデータを全て表示させるプログラムです。
「lng開始列」変数は、表の左端(開始列)を入れます。
「strLockColer」変数は、セルに数式が入っていた時のBackColor
「strUnLockColer」変数は、セルに数式が入っていなかった時のBackColor


 Sub UFinit()
'行が変わる度に、ユーザーフォームの内容をリフレッシュする

    Dim lngTargetRow As Long
    Dim i As Long, j As Long
        
    lngTargetRow = ActiveCell.Row

    With f

        j = 0
        For i = 1 To lngField
           .Controls("TextBox" & i).Text = Cells(lngTargetRow, lng開始列 + j).Text
           
           If Cells(lngTargetRow, lng開始列 + j).HasFormula = True Then
           
            .Controls("TextBox" & i).BackColor = strLockColer
            .Controls("TextBox" & i).Locked = True
           
           Else
           
            .Controls("TextBox" & i).BackColor = strUnLockColor
            .Controls("TextBox" & i).Locked = False
           
           End If
           
           j = j + 1
        Next i

    End With
    
End Sub

Excel-VBA テキスボックスにTab(タブ)が入ってしまい移動できない

私が困った事なので、自分用のメモとして書いておきます。

次のような問題が何度かありました。
------------------------------
ユーザーフォームに複数のテキスボックスを設置。
テキストボック間をTabキーで移動したいのに、Tabキーを押すと、テキスボックスの文字列にTabスペースが入ってしまう。
------------------------------

そこで、調べると次のような方法で回避できることが分かりました。

ユーザーフォームをNewを使ってひらく。

Public f As UserForm1

Sub ユーザーフォーム表示()

    Set f = New UserForm1
    f.Show

End Sub

ユーザーフォームが開いてからは、UserForm1. ~ と書かずに、f. ~ を使います。
例としては、こんな感じ

With f

    For i = 1 To lngField
        .Controls("TextBox" & i).BackColor = strLockColer
        .Controls("TextBox" & i).Locked = True
    Next i

End With
 
そして、ブックを閉じる時には、
fに、Nothing しています。

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set f = Nothing
End Sub


念のため、TextBoxのAfterUpdateの際に、ReplaceでTabを取り除く処理も入れています。

    'テキスボックスにTABが入ったら取り外す
    strValue = Me.Controls("TextBox" & TBNo).Value
    strValue = Replace(strValue, vbTab, "")
    Me.Controls("TextBox" & TBNo).Value = strValue

TBNoは、テキスボックスの番号が入った変数です

以上です。
 


記事検索
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ノート
アクセスカウンター

    • ライブドアブログ