Results 1 to 10 of 10

Thread: [RESOLVED] Excel - Method Value of Object Range Failed

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Resolved [RESOLVED] Excel - Method Value of Object Range Failed

    I've got some Excel 97 code that has worked for years.
    A procedure just Errored. FWIW: I believe this is the first time this App has been run since the last Win10 update was installed.
    What is most interesting is this error occurs randomly on different rows it is pulling from the Access database. I've rebuilt and compacted the Access DB and also done a fresh reboot for the computer. What is also interesting is that prior to getting the "Method Value of Object Range Failed" I was receiving an Automation error of "Method of Object Failed" until I changed from using:

    With rsQuery
    End With

    to defining each method with just rsQuery thinking I was nested with to many With/End Withs.

    Here's the code:
    Code:
    Private Sub ProcessFees(ByVal dtmDateBeg As Date, ByVal dtmDateEnd As Date)
    
       #If kDEBUGON Then
           Debug.Print "Begin ProcessFees"
       #End If
    
       On Error GoTo Error_ProcessFees
       
       '-----------------
       
       Dim i As Integer
       Dim rec_count As Long
       
       'Objects
       Dim rsQuery As Recordset
    
       '*******
       'STARTUP
       '*******
       Set rsQuery = Query_GetFees(dtmDateBeg, dtmDateEnd)
       
       If rsQuery.BOF And rsQuery.EOF Then
          Exit Sub
       Else
          rsQuery.MoveLast
          rec_count = rsQuery.RecordCount
          rsQuery.MoveFirst
       End If
    
       '*****
       'MAIN
       '*****
    '   With rsQuery
       
          For i = 0 To (rec_count - 1)
             
             row = row + 1
             
             With oxlWB.ActiveSheet
             
                '----------
                'Set Format
                '----------
                With .Cells(row, C_BS)
                    .HorizontalAlignment = xlGeneral
                    .VerticalAlignment = xlBottom
                    .WrapText = False
                    .Orientation = 0
                    .ShrinkToFit = False
                    .MergeCells = False
                End With
                
                '----------
                'Enter Data
                '----------
                .Cells(row, C_BS).Value = "Fees"
                .Cells(row, C_DATE).Value = rsQuery!fldDate
                
                'NOTE Change of sign (+/-) with column change
                .Cells(row, C_TBILL_EXP).Value = rsQuery!fldFees
    
     
             End With
             
             'Update Running Total (WATCHOUT - changed sign for passed parameter)
             Call SetCashEquity(-rsQuery!fldFees)
    
             rsQuery.MoveNext
             
          Next
          
          rsQuery.Close
    '   End With
       
       '*******
       'WRAPUP
       '*******
       If rec_count > 0 Then
          row = row + 1
       End If
       
       #If kDEBUGON Then
           Debug.Print "End ProcessFees"
       #End If
    
       Exit Sub
    
    Error_ProcessFees:
    
       With TError
          .Type = ERR_CRITICAL
          .Src = mstrModule & "ProcessFees"
          .Action = MsgAndLog
       End With
       
       Call DoError
    
    End Sub

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Excel - Method Value of Object Range Failed

    You do see that your loop-variable i is "As Integer", and rec_count is "As Long"?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel - Method Value of Object Range Failed

    With .Cells(row, C_BS)
    what are the values of row and c_bs when the error occurs?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Excel - Method Value of Object Range Failed

    Quote Originally Posted by westconn1 View Post
    what are the values of row and c_bs when the error occurs?
    C_BS looks like a constant, my money is on row, since in E97 rowcount is limited to 32k (IIRC, or was it 64K?)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Excel - Method Value of Object Range Failed

    Thanks for all the ideas.
    Zvoni: Thanks for the Integer / Long catch. Should never make a difference as maximum records s/b always be 12.

    With shutdown and complete reboot the AM, still have issues. Here's the debug.Print line I used and result set.
    Like I said random. Sometimes get 12 records for 2019, and fails 2020, other times fails on 2019 records.
    Each year should just have 12 records, including 2020 with a 0 (zero) as value.

    2020 by itself
    Code:
    Debug.Print oxlWS.Name, row, rec_count, i, rsQuery!fldDate, rsQuery!fldFees, Err.Number 
    2020           217           12            0            1/1/2020       0            -2147417851
    2019, and 2020
    Code:
    2019           347           12            0            1/1/2019       1.25 
    2019           348           12            1            2/1/2019       1.25         -2147417851 
    2020           217           12            0            1/1/2020       0 
    2020           218           12            1            2/1/2020       0 
    2020           219           12            2            3/1/2020       0 
    2020           220           12            3            4/1/2020       0 
    2020           221           12            4            5/1/2020       0 
    2020           222           12            5            6/1/2020       0 
    2020           223           12            6            7/1/2020       0            -2147417851
    2018, 2019, 2020
    Code:
    2018           156           12            0            1/1/2018       11.25 
    2018           157           12            1            2/1/2018       1.25 
    2018           158           12            2            3/1/2018       1.25 
    2018           159           12            3            4/1/2018       11.25 
    2018           160           12            4            5/1/2018       11.25 
    2018           161           12            5            6/1/2018       11.25 
    2018           162           12            6            7/1/2018       12.5 
    2018           163           12            7            8/1/2018       11.25        -2147417851 
    2019           347           12            0            1/1/2019       1.25 
    2019           348           12            1            2/1/2019       1.25 
    2019           349           12            2            3/1/2019       1.25 
    2019           350           12            3            4/1/2019       1.25 
    2019           351           12            4            5/1/2019       0 
    2019           352           12            5            6/1/2019       0 
    2019           353           12            6            7/1/2019       0 
    2019           354           12            7            8/1/2019       0 
    2019           355           12            8            9/1/2019       0 
    2019           356           12            9            10/1/2019      0 
    2019           357           12            10           11/1/2019      0 
    2019           358           12            11           12/1/2019      1.25 
    2020           217           12            0            1/1/2020       0 
    2020           218           12            1            2/1/2020       0            -2147417851
    Last edited by vb6forever; Jun 26th, 2020 at 06:34 AM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel - Method Value of Object Range Failed

    .Cells(row, C_BS).Value = "Fees"
    .Cells(row, C_DATE).Value = rsQuery!fldDate

    'NOTE Change of sign (+/-) with column change
    .Cells(row, C_TBILL_EXP).Value = rsQuery!fldFees
    as far a i can see these are the only lines that use the value method, which line does the error occur? is it always the same line?

    your error handling stops the code from continuing to find out if the error occurs only once or multiple times for each recordset

    as a guess i would say it is a recordset problem, some value in the recordset can not be assigned to a cell (range) rather than a range error
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Excel - Method Value of Object Range Failed

    westconn1: Thanks for your input.

    As indicated Random, no specific recordset, line, or row.

    as a guess i would say it is a recordset problem, some value in the recordset can not be assigned to a cell (range) rather than a range error
    That was my initial thought. So printed all values. Error-ed once with
    a record where debug.print for that variable showed No value, but after multiple runs hasn't reoccurred. Also using currency, and did get one vary large value past the decimal, but did NOT error this line.


    Found this interesting article, I'm pursing (it refers after first run, which in my case first and after). Thought I'd go and reference all objects instead of With/End With for this proc and the one preceding and after as a starter.

    https://support.microsoft.com/en-us/...time-code-runs

    Other reference suggestions:
    https://stackoverflow.com/questions/...sheet-activate

    https://stackoverflow.com/questions/...t-in-excel-vba
    Last edited by vb6forever; Jun 26th, 2020 at 01:00 PM.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel - Method Value of Object Range Failed

    Set rsQuery = Query_GetFees(dtmDateBeg, dtmDateEnd)
    we do not see the sql to populate the recordset

    With oxlWB.ActiveSheet
    as you have a problem i would avoid this and specify the actual sheet, either by name or index

    For i = 0 To (rec_count - 1)
    another thing to try, change to
    Code:
    do until rsQuery.eof
    can you post a sample workbook and database (zip first) that demonstrate the problem?

    Thought I'd go and reference all objects instead of With/End
    as rsquery is (or should be) a valid object, it should not be a problem in a with block
    Last edited by westconn1; Jun 26th, 2020 at 08:33 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Excel - Method Value of Object Range Failed

    The Query:
    Code:
    Private Function Query_GetFees(ByVal dtmDateBeg As Date, ByVal dtmDateEnd As Date) As Recordset
    
    
       #If kDEBUGON Then
           Debug.Print "Begin Query_GetFees"
       #End If
    
       On Error GoTo Error_Query_GetFees
       
       '-----------------
       
       'Objects
       Dim qd As QueryDef
    '   Dim rsQuery As Recordset
    
       '*******
       'STARTUP
       '*******
       
       '*****
       'MAIN
       '*****
       Set qd = DaoDb.QueryDefs("qryFeesDatafeed")
    
       With qd
          .Parameters("pDateBeg").Value = ExtractDate(dtmDateBeg)
          .Parameters("pDateEnd").Value = ExtractDate(dtmDateEnd)
       End With
    
       'Execute Query
    '   Set rsQuery = qd.OpenRecordset(dbOpenSnapshot) '(dbOpenForwardOnly)
    
       'Execute Query and Return Recordset
       Set Query_GetFees = qd.OpenRecordset(dbOpenSnapshot) '(dbOpenForwardOnly)
       
       '*******
       'WRAPUP
       '*******
       #If kDEBUGON Then
           Debug.Print "End Query_GetFees"
       #End If
    
       Exit Function
    
    Error_Query_GetFees:
    
       With TError
          .Type = ERR_CRITICAL
          .Src = mstrModule & "Query_GetFees"
          .Action = MsgAndLog
       End With
       
       Call DoError
    
    End Function
    as you have a problem i would avoid this and specify the actual sheet, either by name or index
    Already did this, used:
    oxlWB.Sheets(oxlWS.Name).Cells {whatever}

    ========
    Will try your other two suggestions.

    Need to get to work on other issues, so have to differ further action on this till tonight.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    858

    Re: Excel - Method Value of Object Range Failed

    westconn1: First thank you for all your suggestions/input on my behalf.

    This was a bear to troubleshoot.
    When I stepped through code (F8) sometimes it would work other times NOT.
    Why now, maybe with the latest Win-10 upgrade, this error-ed is unknown.
    What I firmly believe was the problem, was this:
    1) rsQuery!fldFees is declared as a Single in the database.
    2) The parameter in this function is declared as Currency.
    Call SetCashEquity(-rsQuery!fldFees)
    3) While a single should fit into Currency, the redefining and passing rsQuery!fldFees from positive to negative
    and at the same time trying to convert it from a Single to Currency, for whatever reason appears to be the cause.
    I went ahead and declared a variable as currency and used that variable as the parameter and the issue went away.
    At least for the several test runs I've done.
    Last edited by vb6forever; Jun 26th, 2020 at 05:29 PM.

Posting Permissions

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



Click Here to Expand Forum to Full Width