My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() 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 |
![]() 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 |
![]() 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... ![]() |
![]() 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 |
![]() 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 |
![]() 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 |
![]() 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 |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 20th April 2018 - 06:44 PM |