[Solved] Symbol already defined differently

Creating a macro - Writing a Script - Using the API

[Solved] Symbol already defined differently

Postby jpways » Tue Dec 03, 2019 11:05 pm

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
Else
    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
Next
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
        Else
            Counter = Counter + 1 'So we only do even numbers
        End If
    Next
   
    '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)
    Else
        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
    Else
     
        ThisComponent.Sheets.getByName()("Slips").getCellByPosition((12 + X), (2 + Y) = 0 'Charge 0
    End If
           
Next
   
End Sub
Last edited by jpways on Thu Dec 05, 2019 8:08 pm, edited 2 times in total.
Openoffice 4.1.6 on Windows 10 64 Bit
jpways
 
Posts: 2
Joined: Tue Dec 03, 2019 10:49 pm

Re: [Error[ Symbol already defined differently

Postby FJCC » 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)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7742
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Error[ Symbol already defined differently

Postby JeJe » Wed Dec 04, 2019 2:33 am

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))
next



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
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1137
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Error[ Symbol already defined differently

Postby jpways » Thu Dec 05, 2019 8:08 pm

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.
Openoffice 4.1.6 on Windows 10 64 Bit
jpways
 
Posts: 2
Joined: Tue Dec 03, 2019 10:49 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests