Dynamic Array Problem
 

Hope i can get some help on this,

Below are two sub routines, lastfridays and ReturnsMissing the latter is in a
differant module called missing.

My problem is that i want the values of the variant array missed to be used
in returnsmissing. when i run this all is well with the cde until i try to
pass to missing.returnsmissing as i get a "type mismatch error array or
user defined expected" error

The code does open a db but error is produced before code runs?

Can someone enlighten me please I have looked at threads on this here and
tried some solutions suggested to no avail. I am reasonablly ok with VBA
though still a novice. Arrays are still a bit of a mystery to me which i am
making progress on but have a long way to go.

Thanks in advance


Paul

Option Explicit
Public Sub LastFridays()
Dim validationdate As Date
Dim weekending As Date
Dim fromdate As Date
Dim missingdate()
Dim todate
Dim misseddate() As Variant
Dim Trustcheck
Dim arraycount
Dim chkdate
Dim LastRowval
Dim datechk
Dim i As Long
Dim missedcount
Dim completed
With CheckReturns
fromdate = .ufFromDays & "/" & .UFFromMonths & "/" & .UFfromYear
todate = .ValDate
validationdate = todate
Trustcheck = .UFTrustNAme
arraycount = 0
missedcount = 0
ReDim misseddate(missedcount + 1)
ReDim missingdate(arraycount + 1)
For chkdate = fromdate To todate Step 7
missingdate(arraycount) = chkdate
arraycount = arraycount + 1
ReDim Preserve missingdate(arraycount + 1)

MsgBox missingdate(arraycount - 1) & " " & arraycount - 1

Next
MsgBox arraycount & " " & missingdate(arraycount - 1)
DataBase.openDB
LastRowval = LastRow()

For datechk = 0 To arraycount
MsgBox " Next Date " & missingdate(datechk)
completed = False
For i = 2 To LastRowval
weekending = Range("Week_Ending").Offset(rowoffset:=i - 1)
If StrComp(weekending, missingdate(datechk)) = 0 Then
If StrComp(Range("trust").Offset(rowoffset:=i - 1), Trustcheck) = 0
Then
completed = True
GoTo nextdate
End If
End If
Next
If completed = False Then
' Do the stuff
MsgBox "Whoops No return for " & Trustcheck & " " & " Validation
Date " & missingdate(datechk) & " " & " DB Date " & weekending
misseddate(missedcount) = missingdate(datechk)
missedcount = missedcount + 1
ReDim Preserve misseddate(missedcount + 1)
End If
nextdate:
Next
'put dates missing onto user form
'this produces error "Type Mismatch array or user defined expected"
missing.ReturnsMissing (misseddate())
End With
End Sub

Public Sub ReturnsMissing(ByRef misseddate() As Variant)
With CheckReturns
lowerb = LBound(misseddate)
upperb = UBound(misseddate)
For i = lowerb To upperb
MsgBox misseddate(i)
Next
End With
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Dynamic Array Problem
 

It fails at the first hurdle of testing, Checkreturns isn't defined, what is
it?

--
__________________________________
HTH

Bob

"pjbur2005 via OfficeKB.com" <u18722@uwe wrote in message
news:8a4c6717c2494@uwe...
Hope i can get some help on this,

Below are two sub routines, lastfridays and ReturnsMissing the latter is
in a
differant module called missing.

My problem is that i want the values of the variant array missed to be
used
in returnsmissing. when i run this all is well with the cde until i try
to
pass to missing.returnsmissing as i get a "type mismatch error array or
user defined expected" error

The code does open a db but error is produced before code runs?

Can someone enlighten me please I have looked at threads on this here and
tried some solutions suggested to no avail. I am reasonablly ok with VBA
though still a novice. Arrays are still a bit of a mystery to me which i
am
making progress on but have a long way to go.

Thanks in advance


Paul

Option Explicit
Public Sub LastFridays()
Dim validationdate As Date
Dim weekending As Date
Dim fromdate As Date
Dim missingdate()
Dim todate
Dim misseddate() As Variant
Dim Trustcheck
Dim arraycount
Dim chkdate
Dim LastRowval
Dim datechk
Dim i As Long
Dim missedcount
Dim completed
With CheckReturns
fromdate = .ufFromDays & "/" & .UFFromMonths & "/" & .UFfromYear
todate = .ValDate
validationdate = todate
Trustcheck = .UFTrustNAme
arraycount = 0
missedcount = 0
ReDim misseddate(missedcount + 1)
ReDim missingdate(arraycount + 1)
For chkdate = fromdate To todate Step 7
missingdate(arraycount) = chkdate
arraycount = arraycount + 1
ReDim Preserve missingdate(arraycount + 1)

MsgBox missingdate(arraycount - 1) & " " & arraycount - 1

Next
MsgBox arraycount & " " & missingdate(arraycount - 1)
DataBase.openDB
LastRowval = LastRow()

For datechk = 0 To arraycount
MsgBox " Next Date " & missingdate(datechk)
completed = False
For i = 2 To LastRowval
weekending = Range("Week_Ending").Offset(rowoffset:=i - 1)
If StrComp(weekending, missingdate(datechk)) = 0 Then
If StrComp(Range("trust").Offset(rowoffset:=i - 1), Trustcheck) = 0
Then
completed = True
GoTo nextdate
End If
End If
Next
If completed = False Then
' Do the stuff
MsgBox "Whoops No return for " & Trustcheck & " " & " Validation
Date " & missingdate(datechk) & " " & " DB Date " & weekending
misseddate(missedcount) = missingdate(datechk)
missedcount = missedcount + 1
ReDim Preserve misseddate(missedcount + 1)
End If
nextdate:
Next
'put dates missing onto user form
'this produces error "Type Mismatch array or user defined expected"
missing.ReturnsMissing (misseddate())
End With
End Sub

Public Sub ReturnsMissing(ByRef misseddate() As Variant)
With CheckReturns
lowerb = LBound(misseddate)
upperb = UBound(misseddate)
For i = lowerb To upperb
MsgBox misseddate(i)
Next
End With
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Dynamic Array Problem
 

Bob thanks for your reply, sorry Check returns is a user form most of the
control on it are prefixed with "UF" ie .UFTrustNAme though a couple dont
have this. this code worked fine right up to the point i tried to pass the
array to "missing.ReturnsMissing (misseddate())" take this out at it works.
i have also tried putting the Sub ReturnsMissing in the same module but get
the same error.

I could do what i want without using another routine but once i have sussed
out the logic i intend to create it as a function. Also I want to learn why
its not working and how to resolve. My journey with VBA has been all about
solving the problems i have encountered and improving my code along the way.
this site has been an excellent resource for me and i value all contributors
to it. I have followed a lot of your work Bob and am pleased you replied.

many thanks
Paul


Bob Phillips wrote:
It fails at the first hurdle of testing, Checkreturns isn't defined, what is
it?

Hope i can get some help on this,

[quoted text clipped - 96 lines]
End With
End Sub

--
Message posted via http://www.officekb.com