UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Type Mismatch Error On The Sub..., Office 2013    
 
   
Miguel_A
post Jan 12 2018, 02:43 PM
Post#1



Posts: 231
Joined: 20-January 04



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
Go to the top of the page
 
theDBguy
post Jan 12 2018, 02:50 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,919
Joined: 19-June 07
From: SunnySandyEggo


Hi Miguel,

Which line is giving you the type mismatch error?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Miguel_A
post Jan 12 2018, 02:54 PM
Post#3



Posts: 231
Joined: 20-January 04



Hi

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

Go to the top of the page
 
theDBguy
post Jan 12 2018, 02:57 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,919
Joined: 19-June 07
From: SunnySandyEggo


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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Miguel_A
post Jan 12 2018, 03:29 PM
Post#5



Posts: 231
Joined: 20-January 04



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
Go to the top of the page
 
theDBguy
post Jan 14 2018, 10:44 AM
Post#6


Access Wiki and Forums Moderator
Posts: 71,919
Joined: 19-June 07
From: SunnySandyEggo


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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Miguel_A
post Jan 14 2018, 01:40 PM
Post#7



Posts: 231
Joined: 20-January 04



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
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th April 2018 - 06:44 PM