Application-defined or object-defined error

j_bayat194

New Member
Joined
Jul 24, 2019
Messages
1
Dear all;
How can I solve this error:
Sincerely.

"Application-defined or object-defined error"

I have two sheets, "SETUP", "RESULTS". Setup contains the information.

Sub Mine()
Dim out() As Double
Dim date_a() As Date
Dim dt As Double
Dim I, start, n, j As Integer
Dim startdate, date_, date1 As Date
Dim rr, dd As String
dt = 1
n = 730
startdate = 1/1/2018
date1 = 1/1/2013
date_ = startdate
start = start_index(startdate, date1)
ReDim out(2 To n - strart + 2, 2 To 20) As Double
ReDim date_a(2 To n - strart + 2, 1 To 1) As Date
j = 1
For I = start To n
date_ = date_ + dt
date_a(j + 1, 1) = date_
Next
dd = "a2:a" & n - start + 2
Sheets("RESULTS").Range(dd).Value = date_a
End Sub


Function start_index(startdate, date1) As Integer
start_index = MAX(DateDiff("d", date1, startdate) + 1, 1)
End Function


Function MAX(a, b)
If a > b Then MAX = a Else MAX = b
End Function
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
welcome to the board

There's a few changes we can make that will help to identify the problem

Add the line Option Explicit to the top of every code module, this forces you to declare all variables, and would show you that your variable "strart" is in fact a misspelling. To get VB to add this line of code by default, select
Tools > options > Require Variable Declaration

With this change, your code will run although I don't think it does what you want it to...

Also note that in the line Dim I, start, n, j As Integer, only j is declared as an integer. All the others are variants as they have not been given a type in their own right. What you wanted to write was Dim I As Integer, start As Integer, n As Integer, j As Integer

Show the Immediate window and the Locals window, you can use these to explore the values of different variables during run time. Debug.print variable_name will write the current value of any variable to the immediate window. I also find that it's easier to work with long values instead of dates, then simply change the formatting of the cell in which you display the answer. This helps me in the UK because it prevents corruption caused by unplanned swapping between UK & US date formats, but it also keeps things simple


 
Upvote 0
Try this

Code:
Option Explicit

Sub Mine()

 Dim out() As Double
 Dim date_a() As Date
 Dim dt As Double
 Dim I As Integer, start As Integer, n As Integer, j As Integer
 Dim startdate As Date, date_ As Date, date1 As Date
 Dim rr As String, dd As String
 
    dt = 1
    n = 730
    startdate = 1 / 1 / 2018
    date1 = 1 / 1 / 2013
    date_ = startdate
    start = start_index(startdate, date1)
    ReDim out(2 To n - start + 2, 2 To 20) As Double
    ReDim date_a(2 To n - start + 2, 1 To 1) As Date
    j = 1
    
    For I = start To n
        date_ = date_ + dt
        date_a(j + 1, 1) = date_
    Next I
    
    dd = "a2:a" & n - start + 2
    
    Sheets("RESULTS").Range(dd).Value = date_a
 End Sub


 Function start_index(startdate, date1) As Integer
    start_index = MAX(DateDiff("d", date1, startdate) + 1, 1)
 End Function


 Function MAX(a, b)
    If a > b Then MAX = a Else MAX = b
 End Function

Also, note I made some changes:



I added Option Explicit

It saves a ton of headaches to use option explicit, because when I did, I went right to your problem

Your variable "start" is misspelled as "strart"

ReDim out(2 To n - strart + 2, 2 To 20) As Double
ReDim date_a(2 To n - strart + 2, 1 To 1) As Date

When VBA goes to redim, you get that error.

I also changed your dim statements

if you do not use the "as" statement after EVERY declaration, you are actually creating a variant.


Dim I, start, n, j As Integer

translates to:

Dim I as variant, start as variant, n as variant, j as integer


You need to declare each one explicitly

Dim I As Integer, start As Integer, n As Integer, j As Integer
Dim startdate As Date, date_ As Date, date1 As Date

Also, try to avoid single character variables, they make maintaining the code very difficult, and it's a bad habit to get into.
 
Upvote 0
Baitmaster G

option explicit revealed he had misspelled "start" as "strart" that will cause the error when it tries to redim
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top