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

  • 質問

  • 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のままです。


    2013年7月18日 7:56

回答

  • お疲れ様でした。

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

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

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

    ・毎回コネクションを作成するので遅いです。
    速度が気になるなら変更が必要です。
    2013年8月6日 8:02
  • おそらく何かのひょうしで、 Microsoft ActiveX Data Objects x.x Library の参照設定が外れてしまったのでしょう。
    もう一度 参照設定 してみて下さい。
    2013年10月30日 3:00

すべての返信

  • これだと思います。

    DAO コードを ADO に変換する

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

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

    2013年7月18日 8:58
  • 回答ありがとうございます。

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

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

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

    (Windows7は64bit、Excel、Accessは32bit環境です。)

    2013年7月18日 9:33
  • DAOを使っているかどうかや、バージョンが解らない場合はいったん前のレスを忘れてください。

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

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

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


    Sub Test()
        住所取得 1003131
    End Sub


    • 編集済み hihijiji 2013年7月22日 9:23 文字の大きさ変更
    2013年7月22日 9:17
  • 回答ありがとうございます。

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

    2013年7月22日 10:00
  • まず、アドインを使える状態にする必要がありそうですね。

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

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

    2013年7月23日 2:38
  • 回答ありがとうございます。

    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(仕分番号)が割り当てられてます。

    2013年7月23日 8:50
  • 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 住所取得(新番号)

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

    ※見当違いだったらスイマセン
    • 編集済み aviator__ 2013年7月23日 10:02 ※部を追記
    2013年7月23日 10:02
  • 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側でも動く(予定)です。
    • 編集済み hihijiji 2013年7月23日 11:00 補足説明
    2013年7月23日 10:37
  • 返信ありがとうございます。#VALUE!が表示されます。
    2013年7月24日 2:32
  • 返信ありがとうございます。

    「コンパイルエラー

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

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

    2013年7月24日 2:36
  • 何をどう試したら、何処でどうなった位は書いて頂かないと、状況が解りません。
    2013年7月26日 7:48
  • 言葉足らずで申し訳ありません。

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

    「コンパイルエラー

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

    と表示されました。

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

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

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

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

    2013年7月26日 11:08
  • 遅くなりまして申し訳ありません。

    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で実行するのがマズイのでしょうか。

    2013年7月31日 5:55
  • すみません。
    参照設定をするライブラリの名前を間違えて記述しました。

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

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

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

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

    かえって混乱を招いて申し訳ありません。
    2013年7月31日 8:48
  • 遅くなりまして申し訳ありません。

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

    DAOのチェックを外す。

    Access97をアンインストール。

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

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

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

    2013年8月2日 6:33
  • 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

    2013年8月2日 8:25
  • お気遣いありがとうございます。

    1~7実施しました。

    8.

    実行時エラー-214746725980004005

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

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

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

    丸投げ状態で恐縮です。

    2013年8月2日 8:57
  • バグが有りました 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



    • 編集済み hihijiji 2013年8月3日 2:40
    2013年8月2日 10:02
  • お手数をおかけします。

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

    1~7実施しました。

    8.

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

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

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


    2013年8月5日 10:26
  • POST3.mdbの 全国.郵便番号 は文字列だったのでしょうか?

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


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


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

    にしてみてください。
    2013年8月6日 4:32
  • 文字列です。(Access2013では整数【数値】で登録できませんでした。)説明不足で申し訳ありません。

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

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

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

    を削除。

    2)終了してエクセルへ戻る。

    3)アドインに名前を付けて保存。


    2013年8月6日 5:38
  • お疲れ様でした。

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

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

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

    ・毎回コネクションを作成するので遅いです。
    速度が気になるなら変更が必要です。
    2013年8月6日 8:02
  • ありがとうございました。

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

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

    2013年8月6日 8:57
  • ルール違反でしたら申し訳ありません。

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

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

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

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


    2013年10月30日 2:46
  • おそらく何かのひょうしで、 Microsoft ActiveX Data Objects x.x Library の参照設定が外れてしまったのでしょう。
    もう一度 参照設定 してみて下さい。
    2013年10月30日 3:00
  • ご指摘の通りでした。ありがとうございました。


    2013年10月30日 5:17