[X]My Assistant

[Miguel_A]

Hi

Can you guys explain to me why it's giving the error "Type mismatch: array or user-defined type expected" when running the script and/or if this is the correct way of doing this ?

this being:

a_piq refers to the active picket or group if you prefer
status(x) will refer to the status of that particular group ...

The idea is to rotate all status between all groups and the groups will have always different status...

Thanks in advance

CODE

Sub tester()
    Dim result As String
    Dim a_piq As Integer
    Dim status(1 To 4) As Integer
    
    a_piq = 1
    status(1) = 1
    status(2) = 3
    status(3) = 4
    status(4) = 2
    result = check_status(a_piq, status(a_piq))
End Sub

Public Function check_status(a_piq As Integer, status() As Integer) As String
    MsgBox (status(a_piq))
    If status(a_piq) = 1 Then
        status(a_piq) = 2
        'This refers to group being in the Day
        check_status = "D"
    ElseIf status(a_piq) = 2 Then
        status(a_piq) = 3
        'This refers to group being in the Night
        check_status = "N"
    ElseIf status(a_piq) = 3 Then
        status(a_piq) = 4
        'This refers to group being in the Day Off 1
        check_status = "F1"
    ElseIf status(a_piq) = 4 Then
        status(a_piq) = 1
        'This refers to group being in the Dat Off 2
        check_status = "F2"
    End If
End Function

[theDBguy]

Hi Miguel,

Which line is giving you the type mismatch error?

[Miguel_A]

Hi

This is the image error that it's giving ...
I think it's on the sub line but...

[theDBguy]

Hi,

Try changing the highlighted line to this and see if it gets rid of the error.

result = check_status(a_piq, status)

Hope it helps...

[Miguel_A]

Hi

Yehh it worked ... thanks
Does this means that i need always to send the entire array to the function ?
In this case the array will be small, but what if it was a huge array ?

And how about the method i am using, is it good ?

Thanks in advance

[theDBguy]

Hi Miguel,

I'm not really sure what you're trying to do but no, you don't have to pass the whole array to the function; and besides, you're not really passing the array to the function; rather, you're technically just passing the memory address to it.

So, in the code you posted, I don't understand the need for a function at all. It seems to me you can determine the result of function from within the sub, so the function can be eliminated. Either that or just move the array declaration inside the function if it applies to multiple subs.

Just my 2 cents...

[Miguel_A]

Hi

Well the idea of this function is to rotate the groups between the shift's ...
There are 4 groups that need to be rotated between 4 types of shifts.

The a_piq defines the group name that in this particular case can be 4.
The status array is to define the actual group status for the group ( status(1) for group 1, etc)

Well the code is almost entirely done i can place it here to see what you guys think about it and see what can be improved ...

CODE

Sub WorkCalendar()
    Dim p_ln As Integer, p_col As Integer
    Dim a_year As Integer, a_month As Integer, a_day As Integer
    Dim x As Integer
    Dim a_piq As Integer
    Dim status(1 To 4) As Integer
        
    a_year = 2018
    a_month = 1
    a_day = 1
    p_ln = 1
    p_col = 1
    
    'Initial status of each group
    status(1) = 3 'Group A working Day
    status(2) = 2 'Group B incomplete day off
    status(3) = 4 'Group C complete day off
    status(4) = 1 'Group D working Night
    
    For a_month = 1 To 12
        'Headline with the month name
        'Range(Cells(p_ln, p_col), Cells(p_ln, Day(DateSerial(a_year, a_month + 1, 0)) + 1)).MergeCells = True
        Cells(p_ln, p_col).Value = MonthName(a_month)
        'Cycle of the day's and shifts of each group
        Do While a_month = Month(DateSerial(a_year, a_month, a_day))
            'Day on the numeric format
            Cells(p_ln + 1, p_col + 1).Value = Day(DateSerial(a_year, a_month, a_day))
            Call cell_format(DateSerial(a_year, a_month, a_day), p_ln + 1, p_col + 1)
            'Groups
            For a_piq = 1 To 4
                If a_piq = 1 Then
                    If p_col = 1 Then
                        'Group name
                        Cells(p_ln + a_piq + 1, p_col).Value = "A"
                    End If
                End If
                If a_piq = 2 Then
                    If p_col = 1 Then
                        'Group name
                        Cells(p_ln + a_piq + 1, p_col).Value = "B"
                    End If
                End If
                If a_piq = 3 Then
                    If p_col = 1 Then
                        'Group name
                        Cells(p_ln + a_piq + 1, p_col).Value = "C"
                    End If
                End If
                If a_piq = 4 Then
                    If p_col = 1 Then
                        'Group name
                        Cells(p_ln + a_piq + 1, p_col).Value = "D"
                    End If
                End If
                Cells(p_ln + a_piq + 1, p_col + 1).Value = check_status(a_piq, status)
                Call cell_format(DateSerial(a_year, a_month, a_day), p_ln + a_piq + 1, p_col + 1)
            Next a_piq
            'Day in a short format
            Cells(p_ln + 6, p_col + 1).Value = Format(Day(DateSerial(a_year, a_month, a_day)), "ddd")
            'Cell format
            Call cell_format(DateSerial(a_year, a_month, a_day), p_ln + 6, p_col + 1)
            
            a_day = a_day + 1
            p_col = p_col + 1
        Loop
        'Range(Cells(p_ln, 1), Cells(p_ln, p_col)).MergeCells = True
        p_ln = p_ln + 7
        a_day = 1
        p_col = 1
    Next a_month
End Sub

Public Function check_status(a_piq As Integer, status() As Integer) As String
    If status(a_piq) = 1 Then
        status(a_piq) = 2
        'This refers to group being in the Day
        check_status = "D"
    ElseIf status(a_piq) = 2 Then
        status(a_piq) = 3
        'This refers to group being in the Night
        check_status = "N"
    ElseIf status(a_piq) = 3 Then
        status(a_piq) = 4
        'This refers to group being in the Day Off 1
        check_status = "F1"
    ElseIf status(a_piq) = 4 Then
        status(a_piq) = 1
        'This refers to group being in the Day Off 2
        check_status = "F2"
    End If
End Function

Sub cell_format(a_data As Date, a_ln As Integer, a_col As Integer)
    Cells(a_ln, a_col).RowHeight = 15
    Cells(a_ln, a_col).ColumnWidth = 5
    Cells(a_ln, a_col).HorizontalAlignment = xlCenter
    Cells(a_ln, a_col).VerticalAlignment = xlCenter

    If Format(Day(a_data), "ddd") = "sáb" Or Format(Day(a_data), "ddd") = "dom" Then
        Cells(a_ln, a_col).Interior.Pattern = xlPatternGray8
        Cells(a_ln, a_col).Interior.Color = RGB(255, 255, 255)
    Else
        If a_data = DateSerial(Year(a_data), 1, 1) Then
            Cells(a_ln, a_col).Interior.Color = RGB(0, 255, 0)
        Else
            Cells(a_ln, a_col).Interior.Pattern = xlPatternNone
            Cells(a_ln, a_col).Interior.Color = RGB(255, 255, 255)
        End If
    End If
End Sub

Has you can see the idea of the function is to rotate between D to N to F1 to F2 for all groups individually.

note:The code it self is almost entirely done, i just need to add some holiday's and color for the holiday's and that etc etc