0

I've been using this forum more times that I can count, this is my first time asking a question here, currently my work makes me use excel and excel vba even for breakfast, so even thought that I'm nowhere near to be called a pro, I've made various rudimentary projects in my time.

This problem is driving my crazy even to the point of despair, just can't see why, Don't know how to highlight the problem happens in the * part *

Function WAGESPERHOUR(TSHOUR As Date, TEHOUR As Date, _
     Optional TDAY, Optional TWEEK)

Dim tableWages(10000) As dataSchedule
Dim nSheet As String, inRow As Integer, inCol As Integer, _
weekCol As Integer, dayCol As Integer, dateCol As Integer, _
sShiftCol As Integer, eShiftCol As Integer, hoursWCol As Integer, _
wageTCol As Integer, itemsCount As Integer, n As Integer

Application.Volatile

Erase tableWages
WAGESPERHOUR = 0
nSheet = "input schedule"
inRow = 5
inCol = 1
weekCol = 2
dayCol = 1
dateCol = 3
sShiftCol = 6
eShiftCol = 7
hoursWCol = 10
wageCol = 11
itemsCount = 0

For n = 0 To 9999
    If Sheets(nSheet).Cells(inRow + n, dateCol) = "" Then
        itemsCount = n - 1
        Exit For
    End If
    tableWages(n).day = Sheets(nSheet).Cells(inRow + n, dayCol)
    *****tableWages(n).week = Sheets(nSheet).Cells(inRow + n, weekCol)*****
    tableWages(n).startShift = Sheets(nSheet).Cells(inRow + n, sShiftCol)
    tableWages(n).endShift = Sheets(nSheet).Cells(inRow + n, eShiftCol)
    tableWages(n).hoursWorked = Sheets(nSheet).Cells(inRow + n, hoursWCol)
    tableWages(n).wage = (Sheets(nSheet).Cells(inRow + n, wageCol) / 2)

Next

For n = 0 To itemsCount
    If TDAY = "" Then
        tableWages(n).day = TDAY
    End If
    If TWEEK = "" Then
        tableWages(n).week = TWEEK
    End If
    If ((tableWages(n).startShift >= sHOUR And tableWages(n).startShift < eHOUR) _
     Or (tableWages(n).endShift > sHOUR And tableWages(n).endShift < sHOUR) _
     Or (eHOUR > tableWages(n).startShift And eHOUR < tableWages(n).endShift) _
     Or (sHOUR >= tableWages(n).startShift And sHOUR < tableWages(n).endShift)) _
    And tableWages(n).day = TDAY _
    And tableWages(n).week = TWEEK Then
         WAGESPERHOUR = WAGESPERHOUR + tableWages(n).wage
     End If
Next

End Function

The tableWages(n).week is supposed to get "week 1" from the cell (n,2); but it just doesn't; I use inspection and the cell values is correct but when is time to assign the value it stays in (""); the worse part is that it worked before, I close the book open it again and stopped working.

enter image description here I'm also attaching a printscreen, with the inspector showing that the cell does have "Week 1" but for some reason it can't save in the array. Every other variable works.

Thanks for the help

EDIT Print Screen of the cells where it need to get the value. enter image description here

Improve this question
10
  • Where is the code where you define the dataSchedule object? I do not see it as part of the Excel VBA object model so I assume you have a Class module or similar. The problem may be in how you define the Get and Let procedures for that. Commented Jan 13, 2016 at 1:27
  • Hi than you very much, for the response, I always define types this way, is easier for me to use later in the routines/functions Commented Jan 13, 2016 at 1:33
  • Type dataSchedule day As String week As String date As Date staffName As String staffRole As String startShift As Date endShift As Date hoursWorked As Double wage As Double End Type Commented Jan 13, 2016 at 1:33
  • Every other variable in the array get theirs respective cell value correctly, even wekk was before no idea what happened. Commented Jan 13, 2016 at 1:38
  • If you are absolutely certain, perhaps by setting a watch on the expression and a breakpoint, that Sheets(nSheet).Cells(inRow + n, weekCol) is evaluating to something like Week 1, then I would have to look at a workbook demonstrating the problem to figure out what is wrong. It's a simple assignment statement. If the data is correct, there must be something else interefering. Commented Jan 13, 2016 at 1:44

1 Answer 1

Reset to default
1

I do not understand the reason, but somehow, in the tableWages(n).week = Sheets(nSheet).Cells(inRow + n, weekCol) line, the range object is not returning the Value property as it should.

I can get that line to work, by returning the Text property. The proper week is then returned to the array. But I do not understand why.

tableWages(n).week = Sheets(nSheet).Cells(inRow + n, weekCol).Text

I don't have time to research this further this evening, but the above change seems to eliminate the issue you had with week 1 not populating.

Of interest, I think, is this screen shot of the Watch Window taken after the first iteration of assignments to tableWages(0)

enter image description here

It shows that there is no Value being returned, even though the Text property is Week 1.

EDIT/ADD: Further investigation suggests corruption in the Table on input schedule worksheet.

Another fix for your problem, NOT involving altering the code, would be to redo the formulas for the Week column.

On input schedule:

  • Select B6:B100
  • Delete
  • Select B5
  • Place cursor in formula bar at the end of the formula
  • Enter
  • Since this is a table, the formula in B5 will be filled down to B500.
  • Macro now works filling in .week as it should.

A problem here would also explain why this was not a problem until recently. Possibly a formula was altered somewhere in that column, and did not propagate properly to the entire column.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

yeah! thank you solving that problem made me realize I had another one later in the code, wrong variable names is now working thank you very much!
@JoseAntonio Please see the Edit to my answer for a different solution, and possible explanation.
Thank you very much, it was was a combination of 2 thing I guess, fi you look closely in the huge "if "conditional at the end, I made a mistake with variables eHOUR sHOUR, I changed those names in the parameters and forgot to change in the conditional, now I thought everything was wrong because of the week never got the correct value, once I added .text I could go forward and see the REAL mistake; is now working correctly without the ".text"; I have no idea why variables several lines below could cause that odd behavior
if you change that eHOUR for TEHOR and sHOUR with STHOUR, everything will work
@JoseAntonio mistakes like this can be avoided by adding Option Explicit to the top of your code module. This forces you to declare all variables, and will expose spelling mistakes and miss-matched variables/declarations
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.