Excel2013にアップグレード後、自作関数が使用出来なくなりました。

Question

Officeフォーラムで質問しましたが、こちらのフォーラムを案内されましたので。再度修正して、投稿致します。

Excel2003、access97を利用して、下記の関数（関数名：住所取得）を作成して、郵便番号から運送会社の仕分番号を出していますが、Windows7、office2013(Excel2013、access2013）にアップグレード後、利用できなくなりました。（#VALUE!が表示されます。）

対処方法を教えていただけないでしょうか？

■Excelの関数（アドインとして登録しています）

Function 住所取得(新番号)

    Set Dbs = OpenDatabase("C:\TEMP\POST3.mdb")

    Set QDef = Dbs.CreateQueryDef("")

    QDef.Sql = _

     "SELECT 全国.* FROM 全国 WHERE(((全国.郵便番号)= " _

            & 新番号 & "));"

    Set rs = QDef.OpenRecordset()

    住所取得 = rs.住所1

    rs.Close

    Dbs.Close

End Function

■accessのテーブル（　テーブル名：全国、ファイル名POST3.mdb）

郵便番号 住所1 備考

1003131 123 東京都千代田区

5390000 456 大阪市中央区

… … …

全ての郵便番号約14万件につき、住所1（仕分番号）が割り当てられてます。

元々は、Excel97、access97で利用していて、Excel2000、2003と利用可能でした。accessは97のままです。

Answer 1

これだと思います。

DAO コードを ADO に変換する

上記リンク先から引用:Microsoft Access 2013 では、DAO 3.6 以前のオブジェクト ライブラリを提供およびサポートしていません。

64bit環境ならデータプロバイダにJetが使えないので、「Microsoft.ACE.OLEDB.12.0」以降へ変更する必要もあります。

Answer 2

回答ありがとうございます。

リンク先を参照したのですが、変換の仕方が分かりません。

具体的にどのように変更すればいいのでしょうか？

せっかく回答いただいたのに申し訳ありません。

（Windows7は64bit、Excel、Accessは32bit環境です。）

Answer 3

DAOを使っているかどうかや、バージョンが解らない場合はいったん前のレスを忘れてください。

普通に原因をつきとめましょう。

セルからユーザ定義関数を呼び出してもエラーで止まってくれないので、エラー箇所を特定する必要があります。

以下のように関数を呼び出すマクロを書いて、呼び出せばエラーで止まると思います。

Sub Test()
    住所取得 1003131
End Sub

Answer 4

回答ありがとうございます。

「コンパイルエラー　SubまたはFunctionが定義されていません。」と表示されます。

Answer 5

まず、アドインを使える状態にする必要がありそうですね。

アドインを追加または削除する

Office ドキュメントのマクロを有効または無効にする

Answer 6

回答ありがとうございます。

Sub Test()
    住所取得 1003131
End Sub

をVisual　Basicに貼り付け、アドイン登録、実行しましたが、

「コンパイルエラー　SubまたはFunctionが定義されていません。」と表示されます。

何度も申し訳ありません。よく分かっていないようです。

下記のどのように修正したらいいのか教えていただけるますと幸いです。

Function 住所取得(新番号)

    Set Dbs = OpenDatabase("C:\TEMP\POST3.mdb")

    Set QDef = Dbs.CreateQueryDef("")

    QDef.Sql = _

     "SELECT 全国.* FROM 全国 WHERE(((全国.郵便番号)= " _

            & 新番号 & "));"

    Set rs = QDef.OpenRecordset()

    住所取得 = rs.住所1

    rs.Close

    Dbs.Close

End Function

■accessのテーブル（　テーブル名：全国、ファイル名POST3.mdb）

郵便番号 住所1 備考

1003131 123 東京都千代田区

5390000 456 大阪市中央区

… … …

全ての郵便番号約14万件につき、住所1（仕分番号）が割り当てられてます。

Answer 7

Excelのバージョンによってスコープ未指定時の判定が違うって事は無いですかね、 

Function 住所取得(新番号)

    Set Dbs = OpenDatabase("C:\TEMP\POST3.mdb")

    Set QDef = Dbs.CreateQueryDef("")

    QDef.Sql = _

     "SELECT 全国.* FROM 全国 WHERE(((全国.郵便番号)= " _

            & 新番号 & "));"

    Set rs = QDef.OpenRecordset()

    住所取得 = rs.住所1

    rs.Close

    Dbs.Close

End Function

この先頭部を

Public Function 住所取得(新番号)

としても変わりませんか？

※見当違いだったらスイマセン

Answer 8

aviator__さんので上手く行かなかったら試してみてください

'ADO を使ったFunctionの例
'ActiveX Data Object の参照設定必要
Function 住所取得Test(postalCode As Long) As String
    
    Dim sql As String 'クエリ
    sql = "SELECT 全国.* FROM 全国 WHERE(((全国.郵便番号)= " & postalCode & "));"
    
    Dim cs As String '接続文字列 ACE.OLEDBのバージョンは要確認
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "DataSource=C:\TEMP\POST3.mdb;"
    
    Dim conn As ADODB.Connection 'ActiveX Data Object のコネクション
    Set conn = New ADODB.Connection '毎回コネクションを作成する(実用的ではない)
    With conn

        .Open cs
        
        Dim rs As ADODB.Recordset 'ActiveX Data Object のレコードセット
        Set rs = New ADODB.Recordset
        With rs
        
            .Open sql, conn, adOpenForwardOnly, adLockReadOnly
        
            If Not .EOF Then
                住所取得Test = rs("住所1")
            Else
                住所取得Test = "該当なし"
            End If
            
            .Close
        End With 'rs
        
        .Close
    End With 'conn
    
End Function

デバグすらしてませんがExcel側でも動く（予定）です。

Answer 9

返信ありがとうございます。#VALUE!が表示されます。

Answer 10

返信ありがとうございます。

「コンパイルエラー

ユーザ定義型は定義されていません。」

と表示され、再計算とタスクバーに表示されフリーズします。

Answer 11

何をどう試したら、何処でどうなった位は書いて頂かないと、状況が解りません。

Answer 12

言葉足らずで申し訳ありません。

Visual　Basicに貼り付け、アドイン登録、実行しました。

「コンパイルエラー

ユーザ定義型は定義されていません。」

と表示されました。

タスクバーに「再計算」と表示されフリーズしました。

※excel2003、Access2013で実行したら#VALUE!表示されますので、Access側の問題でしょうか？

Answer 13

おそらくコンパイルエラーは ADODB.Connection の所で出ていると思います。
その場合は、ActiveX Data Object(ADO)の参照設定が必要です。
VBEのメニューの[ツール]-[参照設定]で
Microsoft ADO x.x Object Library(x.xは一番大きい数字)
にチェックを入れてください。

Microsoft.ACE.OLEDB.12.0が無いとメッセージが出たらMicrosoft Access データベース エンジン 2010 再頒布可能コンポーネントをインストールして下さい。

Answer 14

遅くなりまして申し訳ありません。

Microsoft ADO x.x Object LibraryではなくMicrosoft DAO x.x Object Libraryがありました。

3.51にチェックがはいっていましたが、3.6が別にありましたので3.51のチェックを外して3.6にチェックを入れて実行しましたが、

ADODB.Connection の所でコンパイルエラーとなります。

※Access97をインストール、Access97のPOST3.mdbをTEMPフォルダにコピーしてみましたが、

excel2013、excel2003で実行しても#VALUE!表示されます。

※Access97でテーブルを開こうとしても何も動作しない状況です。

Windows7で実行するのがマズイのでしょうか。

Answer 15

すみません。
参照設定をするライブラリの名前を間違えて記述しました。

誤)Microsoft ADO x.x Object Library(x.xは一番大きい数字)

正)Microsoft ActiveX Data Objects x.x Library(x.xは一番大きい数字)

DAOは別の物ですので、参照を外してください。

Access97 は Windows 7 ではおそらく動かないと思います。
別のトラブルの原因になりかねませんので、アンインストールしたほうが良いかもしれません。

かえって混乱を招いて申し訳ありません。

Answer 16

遅くなりまして申し訳ありません。

Microsoft ActiveX Data Objects 6.1 Libraryにチェック。

DAOのチェックを外す。

Access97をアンインストール。

POST3.mdb をAccess2013で新規作成。（mdb、accdb形式でそれぞれ作成）

実行後どちらの形式でも#VALUE!表示されます。

コンパイルエラーにはなりませんでした。

Answer 17

xxxxxxxx-xxxxxxxxxxxx　さんのペースで良いですよ

最初からやって見ましょう。

1.Excel2013にて、新しいExcelブックを開いてください。
2.[Alt] + [F11] を押してVBEを開いてください。
3.Microsoft ActiveX Data Objects x.x Library　を参照設定してください。
4.新しく標準モジュールを作成してください。
5.標準モジュールに下のコードを張り付けてください。
6.[デバッグ]-[VBAProjectのコンパイル]　をして下さい。
7.Testを実行してください。
8.エラーが出たらエラーが出た個所とメッセージを正確に教えて下さい。
9.上手くいったらアドインに出来ますが、その前に一部修正したほうが良いかもしれない場所があります。

Sub Test()
    MsgBox 住所取得ADO(1003131)
End Sub

'ADO を使ったFunctionの例(Function名だけ変えました)
'ActiveX Data Object の参照設定必要
Function 住所取得ADO(postalCode As Long) As String
    
    Dim sql As String 'クエリ
    sql = "SELECT 全国.* FROM 全国 WHERE(((全国.郵便番号)= " & postalCode & "));"
    
    Dim cs As String '接続文字列 ACE.OLEDBのバージョンは要確認
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "DataSource=C:\TEMP\POST3.mdb;"
    
    Dim conn As ADODB.Connection 'ActiveX Data Object のコネクション
    Set conn = New ADODB.Connection '毎回コネクションを作成する(実用的ではない)
    With conn

        .Open cs
        
        Dim rs As ADODB.Recordset 'ActiveX Data Object のレコードセット
        Set rs = New ADODB.Recordset
        With rs
        
            .Open sql, conn, adOpenForwardOnly, adLockReadOnly
        
            If Not .EOF Then
                住所取得Test = rs("住所1")
            Else
                住所取得Test = "該当なし"
            End If
            
            .Close
        End With 'rs
        
        .Close
    End With 'conn
    
End Function

Answer 18

お気遣いありがとうございます。

1～7実施しました。

8．

実行時エラー‘-2147467259（80004005）’

インストール可能なISAMドライバーが見つかりませんでした。

「デバッグ」をクリックしましたら、

.Open csが黄色く表示されました。

丸投げ状態で恐縮です。

Answer 19

Microsoft Access データベース エンジン 2010 再頒布可能コンポーネントをインストールして下さい。

Answer 20

バグが有りました m(__)m (もう一つ)

x　住所取得Test
○　住所取得ADO
接続文字列にも間違いがありました
× DataSouce
○ Data Souce

Sub Test()
    MsgBox 住所取得ADO(1003131)
End Sub

'ADO を使ったFunctionの例(Function名だけ変えました)
'ActiveX Data Object の参照設定必要
Function 住所取得ADO(postalCode As Long) As String
    
    Dim sql As String 'クエリ
    sql = "SELECT 全国.* FROM 全国 WHERE(((全国.郵便番号)= " & postalCode & "));"
    
    Dim cs As String '接続文字列 ACE.OLEDBのバージョンは要確認
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "Data Source=C:\TEMP\POST3.mdb;"'←Data Souceのスペースが抜けてました
    
    Dim conn As ADODB.Connection 'ActiveX Data Object のコネクション
    Set conn = New ADODB.Connection '毎回コネクションを作成する(実用的ではない)
    With conn

        .Open cs
        
        Dim rs As ADODB.Recordset 'ActiveX Data Object のレコードセット
        Set rs = New ADODB.Recordset
        With rs
        
            .Open sql, conn, adOpenForwardOnly, adLockReadOnly
        
            If Not .EOF Then
                住所取得ADO = rs("住所1")
            Else
                住所取得ADO = "該当なし"
            End If
            
            .Close
        End With 'rs
        
        .Close
    End With 'conn
    
End Function

Answer 21

お手数をおかけします。

Microsoft Access データベース エンジン 2010 再頒布可能コンポーネントをインストール　（32bit）

1～7実施しました。

8．

実行時エラー’2147217913（80040e07）’
抽出条件でデータ型が一致しません。

「デバッグ」をクリックしましたら、

「.Open sql, conn, adOpenForwardOnly, adLockReadOnly」が黄色く表示されました。

Answer 22

POST3.mdbの 全国.郵便番号 は文字列だったのでしょうか？

以下のSQLを変更してみましょう
sql = "SELECT 全国.* FROM 全国 WHERE(((全国.郵便番号)= " & postalCode & "));"


POST3.mdbの 全国.郵便番号 が 文字列なら
sql = "SELECT 全国.* FROM 全国 WHERE 全国.郵便番号 = '" & postalCode & "';"


全国.郵便番号 が 数値なら
sql = "SELECT 全国.* FROM 全国 WHERE 全国.郵便番号 = " & postalCode & ";"

にしてみてください。

Answer 23

文字列です。（Access2013では整数【数値】で登録できませんでした。）説明不足で申し訳ありません。

ありがとうございます。メッセージボックスに希望の結果がでました。

このまま以下の手順でxlaまたはxlamとして、保存すればいいのですね。

１）Sub Test()
    MsgBox 住所取得ADO(1003131)
End Sub

を削除。

２）終了してエクセルへ戻る。

３）アドインに名前を付けて保存。

Answer 24

お疲れ様でした。

手順は大丈夫だと思います。

但し2点ほど注意が必要です。

・0から始まる郵便番号を考慮してません。
例として「010-0000」の郵便番号を"100000"として登録するなら問題ありません。
"0100000"として登録する場合は変更が必要です。

・毎回コネクションを作成するので遅いです。
速度が気になるなら変更が必要です。

Answer 25

ありがとうございました。

速度は1000件の住所で1分ぐらいの速さですので、大満足です。

長期間おつきあい頂きましてありがとうございました。

Answer 26

ルール違反でしたら申し訳ありません。

「住所1」以外のフィールド（フィールド2～10まであり、名前を定義していません）から別の項目を表示させたかったため、下記の「住所1」を「グループ」または「フィールド10」にしましたが、（フィールド名を変更しないといけないと思い「グループ」としました。

「コンパイルエラーユーザ定義型は定義されていません。」となり、conn As ADODB.Connectionが黄色く表示されます。

大変お手数ですが再度おつきあい願いませんでしょうか？

 If Not .EOF Then
                住所取得ADO = rs("住所1")　
            Else

Answer 27

おそらく何かのひょうしで、 Microsoft ActiveX Data Objects x.x Library の参照設定が外れてしまったのでしょう。
もう一度 参照設定 してみて下さい。

Answer 28

ご指摘の通りでした。ありがとうございました。