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

質問

WinXPSP2 Office2003 を使用しています。
プログラムは、本やネットで調べただけの素人です。
具体的には、大量のascファイルにファイル自身の情報をヘッダとして書き込む作業をします。
そのために、ファイルの情報などを返すユーザー定義関数を作り、それを用いた一連の書き込み作業をマクロにして保存し、アドイン化してしまおうと考えました。
作業をマクロにしてアドイン化することはできるようになりましたが、肝心のユーザー定義関数を作るＶＢＡが良くわかりません。

たとえば以下のように、ファイルの作成日時を返すユーザー定義関数を書いて標準モジュールに張ってみたのですが、上手く動きませんでした（具体的には”#VALUE!”になる）。

Function CreationDate()
CreationDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value
End Function

次の、ファイルの名前を返すユーザー定義関数は上手く動きました。

Function FileName() As String
FileName = Application.ActiveWorkbook.Name
End Function

なぜ後者が動いて前者が動かないのか、よく理解できません。
どのように変えればよいのか、教えていただけないでしょうか？
よろしくお願いします。

回答No.5

Wendy02 · ベストアンサー

こんばんは。

もう少し具体的に説明していただくと良いのですが。もう一度、読み直してみると、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さん

何とか目的の作業ができそうです。
丁寧に説明くださって、ありがとうございます。
たしかにExcelとは書いてませんでした。
色々と言葉足らずで申し訳ありませんでした。

測定機からデータがAscii ファイルで出てくるのですが、そのデータを解析ソフトが読むためには前述のように「ヘッダ」と呼んでいる情報を書き込む必要があって、その作業をExcelにやらせようと思ったのでした。
なので埋め込んだ関数は「ヘッダ」を書き込んで保存する段階で数値データになって良いのです。
「ヘッダ」の書式は決まっていますから、マクロを実行して書き込み作業をExcelにやらせようと考えました。
もちろん、ちゃんとしたプログラムを書ける方なら「ヘッダ」を書き込むソフトなどを作ってしまうのかもしれませんが、何ぶん、そこまでは追いつかないので。。。

今回、色々と教わったことで、何だか面白くなってきました。
ちょっと本格的に勉強してみようと思います。
ありがとうございました。

回答No.4

Wendy02

こんにちは。
#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

お礼

Wendy02 さん
回答ありがとうございます。
お詳しいのですね。
勉強になります。

私の言葉足らずで申し訳ありません。
ヘッダというのは、印刷のときのものではなく、取得した Ascii ファイル を他のソフトで読み込むために必要な情報でして、具体的には

１）Ascii ファイルをソフトＡで作成
２）取得した Ascii ファイル を開き、必要な情報（これをヘッダと呼んでいます）を何行かに渡ってデータシートに書き込む
３）ソフトＢでヘッダを付けた Ascii ファイル を処理

という作業になります。
作業２）のときにファイル自身の作成日時などを書き込む必要があったので、関数を作ろうと考えたのでした。

なにぶん素人なので、用語の使い方が不適切だと思いますが、お許しください。

お気づきの点がありましたら、これに懲りずご教授ください。

回答No.3

noname#79209

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

お礼

vizzar さん

間違って、補足に書き込んでしまいました。。。（恥）

補足

vizzar さん

早速ありがとうございます！

Public Function CreationDate()
CreationDate = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation Date").Value
End Function

と言う意味ですよね？
でもアドイン化したら”#VALUE!”が返ってきました。。。

回答No.2

Wendy02

こんにちは。

>大量のascファイルにファイル自身の情報をヘッダとして書き込む作業をします。
ascii ファイルには、BuiltinDocumentProperties　オブジェクトがありませんね。

ひとつの方法としては、Excel 形式で保存して、そこから、BuiltinDocumentProperties　を取るか、
直接、ascii(text) ファイルの日時を取るか、どちらかになると思います。ただ、そのコード自体をユーザー定義関数にする必要性があるのか、ちょっと疑問です。

お礼

Wendy02さん
回答ありがとうございます！
なるほど、もしかして ascii ファイルが BuiltinDocumentProperties　オブジェクトを持たないために、ファイル名とシート名が同じになるんでしょうか。。。

＞Excel 形式で保存して、そこから、BuiltinDocumentProperties　を取る

困ったことに、最初に生成されるファイルの保存形式に xls などがないんです。

＞直接、ascii(text) ファイルの日時を取る

そんなコードがあるんですか！
よろしかったら教えてください。

＞そのコード自体をユーザー定義関数にする必要性があるのか、ちょっと疑問

つまり直接マクロを書くってことでしょうか？
もちろん、もっと良い方法があればありがたいんですが。。。

回答No.1

noname#79209

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

お礼

vizzarさん
早速のお返事ありがとうございました！
なるほど「.Item」が抜けていたのですね。
試したところ、上手く動きました！

しかしアドイン化して、他のファイルで動かすと”#VALUE!”が返ってきました。。。
ファイルの名前を返すユーザー定義関数はアドイン化しても上手く動くのですが。。。