VBAを使用してExcelワークシートの日付を修正する方法

12.26.2016年26月12日または2016年26月XNUMX日（英国形式）として提供されたスプレッドシートで日付を取得する頻度はどれくらいですか。日付が無効であるか、XNUMXか月目が​​ないことが通知されます。 この記事では、TRIM、LEFT、RIGHT、およびMID関数を使用して、VBAで日付を修正する方法について説明します。

この記事は、読者が開発者リボンを表示していて、VBAエディターに精通していることを前提としています。 そうでない場合は、Googleの「Excel開発者タブ」または「Excelコードウィンドウ」をご覧ください。

この演習のxlsmはダウンロードできます こちら.

私たちの問題ではありません！

問題を解決するのに最適な場所はソースです。 ただし、この場合、12.26.1994年XNUMX月XNUMX日が有効な日付ではないことを給与部門に納得させることはできません（一部の東ヨーロッパ諸国のコンピューターのコントロールパネルでそのように構成されている場合を除く）。

実際、機械可読ではないことを証明できます。 たとえば、日付に7日を追加します。

"=01.01.2017 + 7" = #VALUE. 

"=2017.01.01 + 7" = #VALUE.

一方…

"=2017-01-01 + 7" = 2017/01/08.

彼らがそれが彼らの問題ではないと示唆していると仮定しましょう。

日付形式

最初に確認する必要があるのは、日付が米国形式か国際形式かです。

この例では、米国での使用、つまり国際形式のDMYではなくMDYを検討していることが明確になっています。

ソースを確立したら、Excelが国際的であろうと米国内であろうと、データ形式を理解できるようにデータ形式を変更する必要があります。

これを行う最良の方法は、日付をyyyymmddに変更することです。これは、修飾を必要としない形式です。

プロセス

ドキュメントの各行を循環し、ソース国に応じて日付を「修正」する関数を呼び出します。 日付が修正されたら、従業員の年齢を計算します。

コード

次のコードを新しいモジュールにコピーします。

Option Explicit

Sub Main()
    Dim strNewFormat As String
    Dim strDate As String
    Sheets("Main").Range("B4").Select
    
    'Cycle through the sheet rows, using IDNumber as an anchor
    'to prevent a premature halt caused by a blank date of birth
    Do While ActiveCell > ""
        If ActiveCell.Offset(0, 2) > "" Then
            strDate = ActiveCell.Offset(0, 2)
            
            'Remove leading or trailing spaces
            strDate = Trim(strDate)
            
            'Call the function
            strNewFormat = ReformatDate(strDate, "USA")
            
            'Write the result from the function ReformatDate to a new column
            ActiveCell.Offset(0, 3) = strNewFormat
            
            'Determine age by subtracting the previous column from today's date
            ActiveCell.Offset(0, 4) = "=(NOW()-RC[-1])/365.25"
            
            'Convert to intger, thus lopping off decimal places
            ActiveCell.Offset(0, 4) = Int(ActiveCell.Offset(0, 4))
        End If
        Range("B" & ActiveCell.Row + 1).Select
    Loop
End Sub

Function ReformatDate(sDate As String, sSource As String)
    Dim yyyy, mm, dd As String
    yyyy = Right(sDate, 4)
    If sSource = "USA" Then
        mm = Left(sDate, 2)
        dd = Mid(sDate, 4, 2)
    Else
        mm = Mid(sDate, 4, 2)
        dd = Left(sDate, 2)
    End If
    ReformatDate = yyyy & "-" & mm & "-" & dd
End Function

フォームにボタンを追加し、それをサブメインに割り当てます。

警告

モジュールに複雑なコードを追加しすぎる前に、Excelは主要なアプリケーション開発で常に安定しているとは限らず、破損したコード自体を回復できない場合が多いことに注意してください。 破損は「保存」で発生するため、結果として唯一のコピーが破損する可能性があります。

頻繁にバックアップし、ツールを使用して修正する Excelファイルの破損.

著者紹介：

フェリックスフッカーは、のデータ復旧の専門家です DataNumen、Inc。は、以下を含むデータ復旧技術の世界的リーダーです。 修理 rar ファイルの破損 およびSQL回復ソフトウェア製品。 詳細については、次のWebサイトをご覧ください。 WWW。datanumen.com