Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed of fixed array versus dynamic array | Excel Programming | |||
Dynamic Array Lbound not working when only one value in array | Excel Programming | |||
Dynamic array | Excel Programming | |||
Dynamic Array problem | Excel Programming | |||
Dynamic array problem | Excel Programming |