VBAで書いたExcelユーザー定義関数が上手く動きません

Question

WinXPSP2 Office2003 を使用しています。
プログラムは、本やネットで調べただけの素人です。
具体的には、大量のascファイルにファイル自身の情報をヘッダとして書き込む作業をします。
そのために、ファイルの情報などを返すユーザー定義関数を作り、それを用いた一連の書き込み作業をマクロにして保存し、アドイン化してしまおうと考えました。
作業をマクロにしてアドイン化することはできるようになりましたが、肝心のユーザー定義関数を作るＶＢＡが良くわかりません。

たとえば以下のように、ファイルの作成日時を返すユーザー定義関数を書いて標準モジュールに張ってみたのですが、上手く動きませんでした（具体的には”#VALUE!”になる）。

Function CreationDate()
     CreationDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value
End Function

次の、ファイルの名前を返すユーザー定義関数は上手く動きました。

Function FileName() As String
  FileName = Application.ActiveWorkbook.Name
End Function

なぜ後者が動いて前者が動かないのか、よく理解できません。
どのように変えればよいのか、教えていただけないでしょうか？
よろしくお願いします。

Wendy02 · Accepted Answer

こんばんは。

もう少し具体的に説明していただくと良いのですが。もう一度、読み直してみると、Microsoft Office とは書かれてあっても、ソフトウェアは、Excelでよろしいのですね。

#4 のお礼側には、何も、Excelの名前の登場する部分が出てきていませんが、コードから、Excelと判断しました。ただ、どこかで、話が抜けているのか、関数を埋め込んだ時点で、Ascii ファイルではなくなるはずです。それでは、話のつじつまが合わないです。

それから、こちらが言うのは変ですが、今のご質問の話の展開では、アドインを使うような流れになっていません。確かに、ユーザー定義関数を使って、タイムスタンプやファイル名が出てくることは面白い、とは思いますが、もし、お仕事でしたら、そのようなことは、まったく方向性が違ってしまいます。

大量のAsciiファイルでは、今の私の書いたようなスタイルさえ、本来は追いつかないはずです。本来、きちんとした情報が得られれば、まったく違う回答にはなっていたと思いますが、アドインであるかどうかは別として、ファイルを開いて、テキストであれ、関数であれ埋め込み型にすると、回答の範囲がかなり限定してしまいます。

それに、今回の場合は、「Creation Date(作成日）」ですから、それはひとつしかありません。変わりようのないものですから、関数の必要性がありません。

ポイントとしては、「ヘッダ」というものは、たぶん、タイトルか、ファイル情報のことだと思います。その情報を、ファイルのどこかに書き込むことではないかと思います。そして、それは、どこにどのように入れるか、ということだと思います。

>３）ソフトＢでヘッダを付けた Ascii ファイル を処理

話が良く分かりませんが、Excelの出力の段階でも、テキスト形式になるという意味だと思います。あまり、端折ってしまうと、その間の状態が分からないのです。それに、単に、関数を埋め込んでも再び、Ascii にしてしまうなら、エラーは出ませんが、それはあまり意味がありません。(保存の際に、たぶん、情報を失うというような、メッセージが出るように思います）

以下では、A1 の場所にタイムスタンプを置くというスタイルになっています。

それを任意の場所にするなら、Range("A1")の代わりに、ActiveCell で良いと思います。

それを、ショートカットに登録すれば、関数を書く手間もいりません。
ファイル名も入れるなら、Sample2 に付け加えればよいです。

'----------------------------------------------

Sub Sample2()
　　Dim fn As String
　　Dim sFn As String
　　Dim myDate As String
　　With ActiveWorkbook
　　　　fn = .FullName
　　　　myDate = getDateCreated(fn)
　　　　With .Worksheets(1)
　　　　　　.Range("A1").EntireRow.Insert
　　　　　　.Range("A1").NumberFormatLocal = "yy/mm/dd"
　　　　　　.Range("A1").Value = myDate
　　　　　　'これを入れないと、Asciiでは、タイムスタンプがつぶれるはず
　　　　　　.Columns(1).AutoFit　
　　　　End With
　　End With
End Sub


Function getDateCreated(ByVal fn As String)
'作成日時 取得
Dim objFile As Object
With CreateObject("Scripting.FileSystemObject")
　　Set objFile = .GetFile(fn)
　　getDateCreated = objFile.DateCreated
　　Set objFile = Nothing
End With
End Function

'---------------------------------------
'もちろん、以上で書いたことは、余計な内容でしたら、以下のように関数を切り替えてください。

Function getDateCreated() As String
'作成日時 取得
Dim objFile As Object
Dim myDate As Date
With CreateObject("Scripting.FileSystemObject")
　　Set objFile = .GetFile(ActiveWorkbook.FullName)
　　myDate = objFile.DateCreated
　　getDateCreated =Format$(myDate,"yy/mm/dd")
　　Set objFile = Nothing
End With
End Function

Wendy02 · Answer

こんにちは。
#2の回答者です。

> BuiltinDocumentProperties
というのは、Microsoft Office のファイルに含まれる DocumentProperty のひとつですから、Ascii ファイル(一般的にはテキストファイル）から取れません。Application ソフトで一旦キャッシュに入れば、そこには、あたかも存在しているように見えるだけです。

>最初に生成されるファイルの保存形式に xls などがないんです。

それは、保存しなければxls になりませんが、保存すれば、タイムスタンプは、保存したときになってしまいます。だから、実際は、そのファイル自体のタイムスタンプから取るしかありません。

しかし、
>具体的には、大量のascファイルにファイル自身の情報をヘッダとして書き込む作業をします。
としても、その情報は、そのまま保存するかどうかは別として、残らないはずですね。
単に、印刷のヘッダに置くだけではないでしょうか？

一応、分かち書きしておきますが、以下のようにすればよいと思います。
マクロのSample1 を実行したら、プレビューで確認してください。

Sub Sample1()
Dim fn As String
Dim sFn As String
Dim myDate As Date
　With ActiveWorkbook
　'同じフォルダ内なら、.Name でも、タイムスタンプは取れます。
　fn = .FullName
　myDate = getDateCreated(fn)
　 sFn = .Name 'ファイルの名前
　 With .ActiveSheet.PageSetup
　　　.LeftHeader = sFn & ":" & Format$(myDate, "yy/mm/dd") '左側のヘッダ
　 End With
　End With
End Sub

Function getDateCreated(ByVal fn As String)
'作成日時 取得
Dim objFile
With CreateObject("Scripting.FileSystemObject")
　　Set objFile = .GetFile(fn)
　　getDateCreated = objFile.DateCreated
　　Set objFile = Nothing
End With
End Function

noname#79209 · Answer

#1です。
Function CreationDate()
が
Public Function CreationDate()
でなく
Private Function CreationDate()
になっているとか．．．

Wendy02 · Answer

こんにちは。

>大量のascファイルにファイル自身の情報をヘッダとして書き込む作業をします。
ascii ファイルには、BuiltinDocumentProperties　オブジェクトがありませんね。
 
ひとつの方法としては、Excel 形式で保存して、そこから、BuiltinDocumentProperties　を取るか、
直接、ascii(text) ファイルの日時を取るか、どちらかになると思います。ただ、そのコード自体をユーザー定義関数にする必要性があるのか、ちょっと疑問です。

noname#79209 · Answer

ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value
の
BuiltinDocumentProperties("Creation Date").Value
は
BuiltinDocumentProperties.Item("Creation Date").Value
とか？

VBAで書いたExcelユーザー定義関数が上手く動きません

こんばんは。

こんにちは。

#1です。

この回答への補足

こんにちは。

ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング