Page 1 of 1

[Solved] Symbol already defined differently

PostPosted: Tue Dec 03, 2019 11:05 pm
by jpways
First I'll explain what I'm trying to do then paste in my code

I had written a payroll program for our office about 10 years ago in Excel '07 and I'm trying to convert it to OO because I'm finally getting around to update it since support for my version ended 2 years ago.

I pasted my VBA code into a converter and am trying to debug the converted code.

I'm about 1/4 of the way through and I'm having trouble figuring out how to solve this, the error is on my EmployeeSheet Variable. The EmployeeSheet Variable is used to point to the appropriate hour entry sheets that I have (currently 4, the sheets are called Employee 1, Employee 2, ...)

We're it's bolded is where I am stuck

So here's the subroutine where I'm getting stuck:
Code: Select all   Expand viewCollapse view
Private Sub Rate()
Dim EmployeeSheet(3) As String 'this will make an array of the sheet name so that we may loop through them
        EmployeeSheet(0) = "Employee 1"
        EmployeeSheet(1) = "Employee 2"
        EmployeeSheet(2) = "Employee 3"
        EmployeeSheet(3) = "Employee 4"
Dim ES As Integer 'This variable will be used to loop through the array
Dim Counter As Integer
Dim X As Integer 'This will be used to set the row value when when we have to set thing on the Slips Page
Dim Y As Integer 'This will be used to set the column value when when we have to set thing on the Slips Page
Dim i As Integer
Dim Gross As Single 'place holder for gross pay
Dim OT As Single 'place holder for Overtime add on
Dim Payday As Date 'Variable to calculate Pay Day
Dim Today As Date 'Variable to hold today's date
Dim DayDiff As Integer 'Variable to hold the number of days between today and payday
Dim ThisWeek As Integer 'Variable to hold the position of this week on all employee sheets
Today = Now
DayDiff = DateDiff("d", 8 / 21 / 2010, Today, vbSaturday) Mod 7  'If Today is Saturday,7)
If DayDiff = 0 Then 'If Today is Saturday
    Payday = Today
    Payday = Today + 7 - DayDiff
End If
'Format payday into date only
Payday = FormatDateTime(Payday, vbShortDate)

'Find the row for all Employee Sheet that equals today's date
For Counter = 6 To 57 'For all the rows on the employee sheet that have a date
    If Payday = ThisComponent.Sheets.getByName()("Employee 1").getCellByPosition(Counter, 1) Then
        ThisWeek = Counter
        Exit For
    End If
For ES = 0 To 3

    If ES Mod 2 = 0 Then 'If we are looking at Employee 1 or Employee 3
        Y = 0
    Else 'If we are looking at Employee 2 or Employee 4
        Y = 5
    End If
    If ES > 1 Then  'If we are looking at Employee 3 or Employee 4
        X = 16
    Else 'If we are looking at Employee 1 or Employee 2
        X = 0
    End If
    Set CurCell = ThisComponent.Sheets.getByName()("Slips").getCellByPosition((4 + X), (4 + Y) 'We are comparing everything to todays date
    'First We Set The pay rate
    For Counter = 2 To 8
        If CurCell >= ThisComponent.Sheets.getByName()("Pay Rate-Hospit").getCellByPosition(Counter, (2 + ES)) And (CurCell < ThisComponent.Sheets.getByName()("Pay Rate-Hospit").getCellByPosition(Counter + 2, (2 + ES)) Or ThisComponent.Sheets.getByName()("Pay Rate-Hospit").getCellByPosition(Counter + 2, 2 + ES) = 0) Then
            [b]ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(2, 2) = ubound(ThisComponent.Sheets.getByName()("Pay Rate-Hospit").getCellByPosition(Counter + 1, (2 + ES)) 'Sets pay rate [/b]
            Exit For
            Counter = Counter + 1 'So we only do even numbers
        End If
    'End Pay Rate
    'Next We Calculate gross pay
    If ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(ThisWeek, 2) > 40 Then 'If hours > 40
        OT = (0.5 * (ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(ThisWeek, 2) - 40) * ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(2, 2)
        'This equation read OT =0.5*(Hours-40)*Payrate
        Gross = 40 * ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(2, 2)
        OT = 0 'Clear any previous OT values
        Gross = ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(ThisWeek, 2) * ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(2, 2)
    End If
    ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(ThisWeek, 4) = (Gross + OT)
    'End Gross Pay
    'Finally we do the hospitalization calcuations
    If CurCell >= ThisComponent.Sheets.getByName()("Pay Rate-Hospit").getCellByPosition(10, (2 + ES) Then 'If the Employee is insurance Coverage is Already Active Then
        ThisComponent.Sheets.getByName()("Slips").getCellByPosition((12 + X), (2 + Y) = ThisComponent.Sheets.getByName()("Table of Non-Federal").getCellByPosition(3, 8) 'Charge insurance premium
        ThisComponent.Sheets.getByName()("Slips").getCellByPosition((12 + X), (2 + Y) = 0 'Charge 0
    End If
End Sub

Re: [Error[ Symbol already defined differently

PostPosted: Tue Dec 03, 2019 11:49 pm
I am not sure what you meant by this part of the line
Code: Select all   Expand viewCollapse view
ThisComponent.Sheets.getByName()(EmployeeSheet(ES).getCellByPosition(2, 2)

My guess is you want
Code: Select all   Expand viewCollapse view
ThisComponent.Sheets.getByName(EmployeeSheet(ES)).getCellByPosition(2, 2)

Everywhere you use the .getByName method, you need to put the string of the name inside the parentheses.

I also suggest that you define a variable to refer to ThisComponent.Sheets, so you do not have to keep repeating that.
Code: Select all   Expand viewCollapse view
oSheets = ThisComponent.Sheets

Then the line I suggested above becomes
Code: Select all   Expand viewCollapse view
oSheets.getByName(EmployeeSheet(ES)).getCellByPosition(2, 2)

Re: [Error[ Symbol already defined differently

PostPosted: Wed Dec 04, 2019 2:33 am
by JeJe
You could simplify your code using an array of sheet objects instead of sheet names:

Code: Select all   Expand viewCollapse view

Dim EmployeeSheet(3) As object
sheets= thiscomponent.sheets
for i = 0 to 3
EmployeeSheet(i)= sheets.getbyname("Employee " & (i+1))

so instead of ThisComponent.Sheets.getByName(EmployeeSheet(ES)). becomes just EmployeeSheet(ES). everywhere.

And similarly declare a variable for ThisComponent.Sheets.getByName("Pay Rate-Hospit") and so on

Re: [Error[ Symbol already defined differently

PostPosted: Thu Dec 05, 2019 8:08 pm
by jpways
It's starting to make sense, after fixing some errors that are occurring because of the differences between VBA and OO Basic I'm working through previous code.