+ Reply to Thread
Results 1 to 4 of 4

VBA Working in Excel 2010 and not 2013

  1. #1
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2013
    Posts
    8

    VBA Working in Excel 2010 and not 2013

    Hi All,

    I am having issues with the code below. The code works in 2010, but not in 2013. Can anyone tell me why it will not work in 2013?

    I am running a 64 bit machine, the 2010 machine is a 32 bit. Not sure if that has anything to do with the problem.



    Public Function GetPriceInfo(IndexNum As String, Optional PubMonth As String, Optional BeginDate As String, Optional EndDate As String, Optional Refresh As Integer)

    'Need to have Microsoft DAO 3.6 Object Library referenced, Microsoft Jet and Replication Objects 2.6 library, Microsoft ActiveX Data Objects 6.1 library, MIrcosoft Access 15.0 Object Library

    '************************************************************************************************************************************
    '************************************************ List of Index Numbers stored in \\enogexdata\WHEEERRRREE ************************************************
    '************************************************************************************************************************************

    Dim sql As String
    Dim rtnArray(0) As Variant
    Dim db As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    Dim DBPath As String
    Dim sconnect As String


    'Dim FuncName As String
    'Dim FuncDesc As String
    'Dim Category As String
    'Dim ArgDesc(1 To 5) As String

    'FuncName = GetPriceInfo
    'FuncDesc = "Returns the Inside FERC or average gas daily price for a given time period"
    'Category = 1
    'ArgDesc(1) = "Index Number from MRC Price Database."
    'ArgDesc(2) = "First day of month for Inside FERC prices" ' Was previously Flow Month
    'ArgDesc(3) = "Beginning flow date for GDD prices"
    'ArgDesc(4) = "Ending flow date for GDD prices"
    'ArgDesc(5) = "1 for refresh"

    'Body of function


    DBPath = "\\enogexdata\Share\Nerve Center\Commodity Management\Market Risk Committee\MRC Price Database.mdb"
    sconnect = "Provider= Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & DBPath & ";"
    db.Open sconnect

    'Set db = OpenDatabase("\\enogexdata\Share\Nerve Center\Commodity Management\Market Risk Committee\MRC Price Database.mdb", , True)


    'Inside FERC SQL
    If BeginDate = "" Then

    sql = "SELECT Price as IndPrice FROM [tblDailyPrices] "
    sql = sql & "WHERE PubDate = #" & PubMonth & "# "
    sql = sql & "AND DailyIndexID = " & IndexNum & " "

    'GDD SQL
    Else

    sql = "SELECT AVG(Price) as IndPrice FROM [tblDailyPrices] "
    sql = sql & "WHERE FlowDate >= #" & BeginDate & "# AND FlowDate <= #" & EndDate & "# "
    sql = sql & "AND DailyIndexID = " & IndexNum & " "

    End If

    mrs.Open sql, db, adOpenStatic, adLockReadOnly

    If mrs.EOF Then
    rtnArray(0) = ""
    Else
    rtnArray(0) = mrs!IndPrice
    End If
    mrs.Close
    db.Close
    GetPriceInfo = rtnArray(0)


    End Function

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Working in Excel 2010 and not 2013

    Hi,

    Can you define 'will not work'?
    Does it run at all?
    If not what's the error code and which line does it Debug at?

    Have you checked that the VBE has all the same Reference objects on both PCs?

  3. #3
    Registered User
    Join Date
    05-15-2014
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VBA Working in Excel 2010 and not 2013

    Thank you for the response! We stumbled across what we think was the issue. We had an add-in with the same function name with the old code. I made the change an it is running now.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Working in Excel 2010 and not 2013

    Well spotted and thanks for the update.

    These things are always somewhat opaque. One to file away as no doubt someone else will stumble across something similar in due course.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel 2010 to Excel 2013 Eroor? PivotTable, ListBox VBA not working.
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2014, 07:49 AM
  2. [SOLVED] Macro works in 2010&2013 but not Excel 2003
    By Mattiac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2014, 03:12 AM
  3. 2010 macro not working in 2013 via button
    By PaulieNZ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2013, 01:27 PM
  4. Save as fail with excel 2010 and 2013
    By graiggoriz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-19-2013, 08:18 AM
  5. Replies: 3
    Last Post: 03-29-2013, 12:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1