Function in a loop (1 Viewer)

[Valentine]

So I have 2 functions that return a specific cell location in Excel. One for a start date and the other for the end date. Once I have these 2 dates I will create a range with them to fill it with a color. My current loop looks like this:

    Do While rsLeave.EOF = False
        Set rRange = xlSh.Cells.Find(What:=rsLeave![DoD ID])
        rownum = rRange.Row
        startRange = ""
        endRange = ""
        BeginDate rsLeave![Start Date], rownum
        FinDate rsLeave![End Date], rownum
        With xlSh.range(startRange, endRange)
            .Interior.ColorIndex = 4
        End With
        rsLeave.MoveNext
    Loop

I am getting an error saying Type Mismatch highlighting the first function in the loop, BeginDate. I have rsLeave defined as a DAO.Recordset, and rownum as long. Not sure if it is referring to the actual function arguments:

Private Function BeginDate(rsLeave As DAO.Recordset, rownum) As String

 

[bastanu]

rsLeave![Start Date] will return a date (the actual start date value). Change your functions to have the first argument as Date.

 

[Valentine]

I need it to return a date because in the function it removes the month and the day respectively to get the accurate column in the spreadsheet.

 

[bastanu]

You didn't show the function but it does not matter, you get the error because when calling the function you pass it a date (rsLeave![Start Date]) but the function expects a recordset.....

 

[Valentine]

oh so change the function from recordset to date. I can see the logic in that now thanks. Now I get a Compile error ByRef argument type mismatch.

If you haven't noticed I am very new to using functions.

 

[Valentine]

Oh nevermind about that error I see what my problem was for that, it was a typo. Once I fixed that I get "Application-defined or object-defined error" now highlighting:

        With xlSh.range(startRange, endRange)

could it be that the variables are strings and range wont take a string variable?

 

[Gasman]

I usually specify a range as A2:G20 ?

 

[Valentine]

Yeah there are 2 ways to define a range I have seen: "A2:G20" or "A2", "G20" if I use the colon method I get a syntax error, it is looking for the quotes. the comma method doesn't give me a syntax error but I still get the application defined error.

 

[bastanu]

You do not use the functions after you call them, the startrange and endrange are still=""

 

[bastanu]

That line should probably be something like this:

With xlSh.range(BeginDate(rsLeave![Start Date], rownum), FinDate (rsLeave![End Date], rownum))
      .Interior.ColorIndex = 4
End With

'or cleaner
 startRange = BeginDate rsLeave![Start Date], rownum
 endRange = FinDate rsLeave![End Date], rownum
        
        
 With xlSh.range(startRange, endRange)
     .Interior.ColorIndex = 4
 End With

 

[Valentine]

ok I thought I had to initialize the variable before the functions.

Each of the functions uses the respective variable to create the specific cell. Let me clear up what the functions do cuz they are TOO long to put in code. They take the date and go through a series of IF statements, IF month is 1 then if day is 1 then startRage = "column" & rownum. thus giving me a specific cell for each date.

So I use the variables in the function. Do I need to Dim the variables in the function even though they are defined in the sub?

I tried to do both ways you just commented before I even attempted this thread and neither worked and tried again just now after the changes and still not working.

So if I read right in your comment by calling the functions it doesn't return the value I want? How best to return the value from the function?

 

[bastanu]

You do not have to initialize the variables unless you need a certain initial value, strings will be "", boolean ones will be false, etc.

Put a break after it and see what this line shows:

startRange = BeginDate rsLeave![Start Date], rownum

 

[bastanu]

So I use the variables in the function. Do I need to Dim the variables in the function even though they are defined in the sub?

You could make startRange and endRange variables public (declaring them as Public at the top of standard module, not inside the sub) or better use the syntax I showed you in post #10.

 

[Valentine]

The thing holding me back from doing what you posted in 10 is that I get a compile error "Expected: end of statement" highlighting rsLeave.

Edit: I changed it slightly to read startRange = BeginDate(rsLeave![Start Date], rownum) to get around the compile error. I did the stop and found out that the variables are not getting filled they are just ""

Edit2: I put watch on rsLeave![Start Date] and rsLeave![End Date], they are grabbing the right date, rownum is grabbing the right row. So my functions aren't returning a value.

 

[bastanu]

Please show what you have (take a screen shot with the highlighted row and error), hard to guess .
startRange = BeginDate (rsLeave![Start Date], rownum)

 

[Valentine]

the functions are this over and over for every day of the year:

Private Function BeginDate(rsLeave As Date, rownum) As String

    
If Month(rsLeave) = "1" Then
    If Day(rsLeave) = "1" Then
        startRange = "CR" & rownum
    ElseIf Day(rsLeave) = "2" Then
        startRange = "CS" & rownum

 

[bastanu]

Because the function has no idea what startRange is, select the entire function code and replace startRange with BeginDate.

 

[Valentine]

the highlighted line after i hit debug is:

With xlSh.range(startRange, endRange)

 

[bastanu]

Have a look at this:

VBA Variables - Lifetime and Scope

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

 

[Valentine]

If I change htat then would the range line be:

With xlSh.range(BeginDate, FinDate)