-
Jun 25th, 2020, 09:34 PM
#1
Thread Starter
Fanatic Member
[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
-
Jun 26th, 2020, 02:04 AM
#2
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
-
Jun 26th, 2020, 03:59 AM
#3
Re: Excel - Method Value of Object Range Failed
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
-
Jun 26th, 2020, 04:50 AM
#4
Re: Excel - Method Value of Object Range Failed
Originally Posted by westconn1
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
-
Jun 26th, 2020, 06:11 AM
#5
Thread Starter
Fanatic Member
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.
-
Jun 26th, 2020, 07:47 AM
#6
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
-
Jun 26th, 2020, 08:02 AM
#7
Thread Starter
Fanatic Member
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.
-
Jun 26th, 2020, 08:28 AM
#8
Re: Excel - Method Value of Object Range Failed
Set rsQuery = Query_GetFees(dtmDateBeg, dtmDateEnd)
we do not see the sql to populate the recordset
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
-
Jun 26th, 2020, 08:39 AM
#9
Thread Starter
Fanatic Member
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.
-
Jun 26th, 2020, 05:25 PM
#10
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|