Module1.tmp

'apos;// Module1Hdr.bas
REM  *****  BASIC  *****
'apos;// Module1Hdr - BadAss Library Module1 header.
'apos;// Module1 (Beta Release) - Local macros for Chase Activity Download Spreadsheet and
'apos;//	transaction processing in Production Alpha Clean Accounting spreadsheet.
'apos;// 8/22/22.	wmk.	'apos;/**/ and // <module-name>.bas bracketing lines added; application
'apos;//				 extended to general banking activity in Accounting subsystem.
'apos;// Legacy Mods.
'apos;//	5/14/20.	wmk.	original code with mods
'apos;//	5/16/20.	wmk.	new functions and subs to span sheets for
'apos;//						transaction mapping to accounts sheets
'apos;//	5/17/20.	wmk.	correct test constant EXPSHEET to match this
'apos;//						document 'apos;Expense Accounts_2'apos;
'apos;//	5/18/20.	wmk.	row data processing constants added.
'apos;//	5/19/20.	wmk.	sheet name constants changed to match production
'apos;//	5/20/20.	wmk.	added OPTION EXPLICIT for code control
'apos;//	5/22/20.	wmk.	added PlaceSplitTrans function.
'apos;//	5/23/20.	wmk.	added BeanField function; bug fix with ASTSHEET
'apos;//	5/25/20.	wmk.	added errhandling.bas and associated subs
'apos;//	5/26/20.	wmk.	errhandling.bas update resinserted
'apos;//	6/28/20.	wmk.	Beta Release to BadAss Library

'apos;// Code protection.
OPTION EXPLICIT				'apos;// every variable must be declared before use

'apos;/**/

'apos;// errhandling.bas
'apos;//---------------------------------------------------------------
'apos;// errhandling.bas - Definitions for development error handling.
'apos;//		wmk. 5/26/20.	17:30
'apos;//---------------------------------------------------------------
'apos;//
'apos;// Header Description.
'apos;// -------------------
'apos;//	errhandling.bas contains definitions that may be inserted in any
'apos;// OOoBasic Module for handing error conditions. This is intended for
'apos;// use with development modules, and should be removed for Production
'apos;// spreadsheets. This will ensure clean code released into the
'apos;// Production environment.
'apos;//
'apos;// error handling interface methods.
'apos;// ErrLogSetup - set up error logging (gbErrLogging, goCellRangeAddress, gsErrModule)
'apos;// ErrLogDisable() - disable error logging by setting gbErrLogging = false
'apos;// ErrLogGetCellInfo(lColumn, lRow) - get error focus cell column, row
'apos;// ErrLogSetCellInfo(lColumn, lRow) - set error focus cell column, row
'apos;//	ErrLogGetCellName() - get error focus cell alphanumeric name
'apos;//	ErrLogGetDisplay() - get error message box enabled status
'apos;//	ErrLogSetDisplay(bDisplayOn) - set error message box enabled status
'apos;//	ErrLogGetModule() - get current module name gsErrModule
'apos;// ErrLogSetModule(sName) - set current module name gsErrModule
'apos;// ErrLogGetRecording - Get error recording status from error log globals
'apos;// ErrLogSetRecording - Set error recording status in error log globals
'apos;// ErrLogGetSheet - Get sheet index from error log globals.
'apos;// ErrLogSetSheet - Set sheet index in error log globals.
'apos;// LogError - make error log entry([goCellRangeAddress], psErrName, psErrMsg)

'apos;// global constants.

'apos;// public constants.
public const ERRLOGSHEET="quot;Error Log"quot;			'apos;// error logging sheet name
public const ERRLOGINSROW=5						'apos;// insertion row for log messages
'apos;// global variables.
'apos;// global variables.
global gbErrDisplay	As Boolean					'apos;// msgBox on/off flag
global gbErrLogging as Boolean					'apos;// logging on/off flag; LogSetup sets/clears
global gbErrRecording As Boolean				'apos;// log sheet recording on/off flag
global gsErrSheet As String						'apos;// name of sheet error thrown on
global gsErrModule As String					'apos;// sub/function throwing error
global gsErrName As String						'apos;// name of error code
global gsErrMsg As String						'apos;// error message
global goErrRangeAddress As Object				'apos;// cell range address

'apos;// public variables.
'apos;//

'apos;// error handling interface methods.
'apos;// ErrLogSetup - set up error logging (gbErrLogging, goCellRangeAddress, gsErrModule)
'apos;// ErrLogDisable() - disable error logging by setting gbErrLogging = false
'apos;// ErrLogGetCellInfo(lColumn, lRow) - get error focus cell column, row
'apos;// ErrLogSetCellInfo(lColumn, lRow) - set error focus cell column, row
'apos;//	ErrLogGetCellName() - get error focus cell alphanumeric name
'apos;//	ErrLogGetDisplay() - get error message box enabled status
'apos;//	ErrLogSetDisplay(bDisplayOn) - set error message box enabled status
'apos;//	ErrLogGetModule() - get current module name gsErrModule
'apos;// ErrLogSetModule(sName) - set current module name gsErrModule
'apos;// ErrLogGetRecording - Get error recording status from error log globals
'apos;// ErrLogSetRecording - Set error recording status in error log globals
'apos;// ErrLogGetSheet - Get sheet index from error log globals.
'apos;// ErrLogSetSheet - Set sheet index in error log globals.
'apos;// LogError - make error log entry([goCellRangeAddress], psErrName, psErrMsg)
'apos;//-----------------end errhandling.bas header--------------------------
'apos;/**/

'apos;// publics.bas
'apos;//---------------------------publics.bas----------------------------------------------
'apos;//		6/6/20.	wmk.
'apos;// publics.bas - module-wide vars for Module1 code in accounts sheets
'apos;// module-wide constants. (used in processing bank download sheets)
'apos;// (mirrored in file publics.bas)

'apos;// Modification History.
'apos;// ---------------------
'apos;//	5/??/20.	wmk.	Original code
'apos;//	5/20/20.	wmk.	Released as Production into Alpha Clean Accounting.ods
'apos;//	5/23/20.	wmk.	ASTSHEET, LIASHEET, INCSHEET, corrected to match
'apos;//						Production spreadsheet; RJUST corrected
'apos;//	5/27/20.	wmk.	DEVDEBUG constant added
'apos;//	5/28/20.	wmk.	ERRUNKNOWN universal error code value added
'apos;//	6/6/20.		wmk.	FMTDATETIME, DATEROW constants added

'apos;//
'apos;// Note. The Module1 functions D and E are referenced in cells for executing test
'apos;// cases against the General Ledger sheet. autocalc MUST BE OFF if these two
'apos;// functions are active (DEVDEBUG=true), since both functions fire on the current
'apos;// user selection. They really go haywire with recalc or autocalc since the current
'apos;// user row/column range may have nothing to do with their intended reference.
'apos;//
'apos;// debugging control.
public const DEVDEBUG=true						'apos;// true if functions D(), E() activated
'apos;public const DEVDEBUG=false					'apos;// false if functions D(), E() de-activated

'apos;// universal error code(s).
public const ERRUNKNOWN=-9999					'apos;// universal unknown error code

'apos;// production sheet names
public const GLSHEET = "quot;General Ledger"quot;			'apos;// General Ledger Sheet.Name
public const ASTSHEET = "quot;Asset Accounts"quot;		'apos;// (Production) Assets Sheet.Name
public const LIASHEET = "quot;Liability Accounts"quot;	'apos;// (Production) Liabilities Sheet.Name
public const INCSHEET = "quot;Income Accounts"quot;		'apos;// (Production) Income Sheet.Name
public const EXPSHEET = "quot;Expense Accounts"quot;		'apos;// Expense Accounts Sheet.Name
'apos;// development sheet names
'apos;public const ASTSHEET = "quot;Assets"quot;				'apos;// Assets Sheet.Name
'apos;Public const LIASHEET = "quot;Liabilities"quot;			'apos;// Liabilities Sheet.Name
'apos;public const INCSHEET = "quot;Income"quot;				'apos;// Income Sheet.Name
'apos;public const EXPSHEET = "quot;Expense Accounts_2"quot;						'apos;// mod051720
public const MON1="quot;January"quot;
public const MON2="quot;February"quot;
public const MON3="quot;March"quot;
public const MON4="quot;April"quot;
public const MON5="quot;May"quot;
public const MON6="quot;June"quot;
public const MON7="quot;July"quot;
public const MON8="quot;August"quot;
public const MON9="quot;September"quot;
public const MON10="quot;October"quot;
public const MON11="quot;November"quot;
public const MON12="quot;December"quot;
public const YELLOW=16776960		'apos;// decimal value of YELLOW color
public const LTGREEN=10092390		'apos;// decimal value of LTGREEN color

'apos;// column index values and string lengths for column data			
public const	INSBIAS=3		'apos;// insert line count bias
public const	COALEN=4		'apos;// length of COA field strings
public const COLDATE=0		'apos;// Date - column A
public const COLTRANS=1	'apos;// Transaction - column B
public const COLDEBIT=2	'apos;// Debit - column C
public const COLCREDIT=3	'apos;// Credit - column D
public const COLACCT=4		'apos;// COA Account - column E
public const COLREF=5		'apos;// Reference - column F
public const DATEROW=1		'apos;// Sheet date row index					'apos;// mod060620

'apos;// cell formatting constants.
public const DEC2=123		'apos;// number format for (x,xxx.yy)			'apos;// mod052020
public const MMDDYY=37		'apos;// date format mm/dd/y						'apos;// mod052020
public const FMTDATETIME=50	'apos;// date/time format mm/dd/yyy hh:mm:ss 	'apos;// mod060620
public const LJUST=1		'apos;// left-justify HoriJustify				'apos;// mod052020
public const CJUST=2		'apos;// center HoriJustify						'apos;// mod052020
public const RJUST=3		'apos;// right-justify HoriJustify				'apos;// mod052320
public const MAXTRANSL=50	'apos;// maximum transaction text length			'apos;// mod052020
'apos;//----------------------end publics.bas---------------------------------------------
'apos;/**/

'apos;// Main.bas
'apos;//---------------------------------------------------------------
'apos;// Main - generic Main sub for module.
'apos;//		wmk. 5/15/20.
'apos;//---------------------------------------------------------------

Sub Main

End Sub
'apos;/**/

'apos;// CheckDoubleEntry.bas
'apos;//---------------------------------------------------------------
'apos;// CheckDoubleEntry - Check double entry transaction fields.
'apos;//		7/3/20.	wmk.
'apos;//---------------------------------------------------------------

public function CheckDoubleEntry(poTransRange As Object, rpsMonth As String,_
					rpsAcct1 As String, rpsAcct2 As String) As Integer

'apos;//	Usage.	iVal = CheckDoubleEntry(oTransRange, rpsMonth, rpsAcct1,
'apos;//						rpsAcct2)
'apos;//
'apos;//		oTransRange = RangeAddress of double-entry transaction
'apos;//		rpsMonth = Month name string (e.g. "quot;January"quot;)
'apos;//		rpsAcct1 = (returned)
'apos;//		rpsAcct2 = (returned)
'apos;//
'apos;// Entry.	&amp;'apos;lt;entry conditions&amp;'apos;gt;
'apos;//
'apos;//	Exit.	iVal = 0 if no error,
'apos;//				   ERRCONTINUE (-1) if error; skip to next transaction
'apos;//				   ERRSTOP (-2) if error; end processing user selction
'apos;//			rpsAcct1 = COA from first line of transaction
'apos;//			rpsAcct2 = COA from 2nd line of transaction
'apos;//
'apos;// Calls.	GetMonthName
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/1/20.	wmk.	original code
'apos;//	6/2/20.	wmk.	error handling setup added
'apos;//	6/3/20.	wmk.	csERRBADDATE constant added
'apos;// 6/5/20.	wmk.	bug fix; was not detecting 2nd line Debit and Credit empty
'apos;//	7/3/20.	wmk.	sERRCOANOTFOUND corrected to csERRCOANOTFOUND
'apos;//	Notes.
'apos;//

const csERRCOANOTFOUND="quot;ERRCOANOTFOUND"quot;
const csERROUTOFROOM="quot;ERROUTOFROOM"quot;
const csERRCOACHANGED="quot;ERRCOACHANGED"quot;

'apos;//----------in Module error handling setup-------------------------------
'apos;// LogError setup snippet.
const csERRUNKNOWN="quot;ERRUNKNOWN"quot;
const sMyName="quot;CheckDoubleEntry"quot;
'apos;// add additional error code strings here...
Dim sErrName as String		'apos;// error name for LogError
Dim sErrMsg As String		'apos;// error message for LogError
'apos;//*---------error handling setup---------------------------
'apos;//	ErrLogGetModule() - get current module name gsErrModule
'apos;// ErrLogSetModule(sName) - set current module name gsErrModule
'apos;// ErrLogGetCellInfo(lColumn, lRow) - get error focus cell column, row
'apos;// ErrLogSetCellInfo(lColumn, lRow) - set error focus cell column, row
'apos;// ErrLogGetSheet - Get sheet index from error log globals.
'apos;// ErrLogSetSheet - Set sheet index in error log globals.
dim iErrSheetIx as Integer
dim lErrColumn as Long
dim lErrRow as Long
dim sErrCurrentMod As String
'apos;//----------end in Module error handling setup---------------------------

'apos;//	constants.
'apos;// process control constants.
const ERRCONTINUE=-1	'apos;// error, but continue with next transaction
const ERRSTOP=-2		'apos;// error, stop processing transactions
const csERRBADDATE="quot;ERRBADDATE"quot;		'apos;// bad date error name

'apos;//	local variables.
dim iRetValue As Integer
dim iBrkPt As Integer		'apos;// easy breakpoint
dim iStatus As Integer		'apos;// general status var


'apos;//------------ CheckDoubleEntry code begins here---------------------------
'apos;//	Usage.	iVal = CheckDoubleEntry(oTransRange, rpsMonth, rpsAcct1,
'apos;//						rpsAcct2)
		dim Doc As Object
		dim oSheets as Object
		dim oGLSheet as Object		'apos;// ledger sheet
		dim iSheetIx As Integer		'apos;// sheet index of ledger
		dim bTransValid As Boolean	'apos;// transaction is valid flag
  		dim bADEmpty As Boolean		'apos;// 1st Debit/2nd Credit empty flag
  		dim bBCEmpty As Boolean		'apos;// 1st Credit/2nd Debit empty flag
		
		'apos;// Cell access objects for first transaction line.
		dim oCellDate as Object		'apos;// Date cell
		dim oCellTrans As Object	'apos;// Transaction cell
		dim oCellDebit As Object	'apos;// Debit cell
		dim oCellCredit As Object	'apos;// Credit cell
		dim oCellAcct As Object		'apos;// COA cell
		dim oCellRef As Object		'apos;// Reference cell
		dim sDate as String			'apos;// Date string
		dim sTrans as String		'apos;// Transaction string
		dim sStyle as String		'apos;// Style property
		dim lDateFormat As Long		'apos;// date format code
		dim sMonth As String		'apos;// month name
		dim lGLCurrRow As Long		'apos;// first row of double entry
		dim lGLNextRow As Long		'apos;// 2nd row of double entry
		dim lGLEndRow As Long		'apos;// end of ledger selection by user
		dim sDebit As String		'apos;// Debit field
		dim sCredit As String		'apos;// Credit field
		dim sAcct As String			'apos;// COA field
		
		'apos;// Cell access objects for 2nd transaction line.
		dim oCellDateB as Object	'apos;// Date cell
		dim oCellTransB As Object	'apos;// Transaction cell
		dim oCellDebitB As Object	'apos;// Debit cell 
		dim oCellCreditB As Object	'apos;// Credit cell
		dim oCellAcctB As Object	'apos;// COA cell
		dim oCellRefB As Object		'apos;// Reference cell
		dim sDateB As String
		dim sTransB As String
		dim sStyleB As String
		dim lDateBFormat As Long
		dim sDebitB As String
		dim sCreditB As String
		dim sAcctB As String
		
	'apos;// code.

'apos;//*------------error handling code initialization---------------
	'apos;// preserve entry error settings.
	iErrSheetIx = ErrLogGetSheet()
	ErrLogGetCellInfo(lErrColumn, lErrRow)
	sErrCurrentMod = ErrLogGetModule()
'apos;//*	
	'apos;// set local error settings.
	ErrLogSetModule(sMyName)
	ErrLogSetSheet(poTransRange.Sheet)
	ErrLogSetCellInfo(COLDATE, poTransRange.StartRow)
	ErrLogSetRecording(true)		'apos;// enable log entries
'apos;//*----------end error handling code initialization-------------

	iRetValue = 0
	iStatus = 0
'apos;		poTransRange.Sheet = iSheetIx of ledger spreadsheet
'apos;		poTransRange.StartColumn = COLDATE
'apos;		poTransRange.StartRow = lGLCurrRow
'apos;		poTransRange.EndColumn = COLREF
'apos;		poTransRange.EndRow = lGLCurrRow + 1
		'apos;// Access ledger sheet from passed information
	iSheetIx = poTransRange.Sheet
	Doc = ThisComponent
	oSheets = Doc.getSheets()
	oGLSheet = oSheets.GetByIndex(iSheetIx)
	lGLCurrRow = poTransRange.StartRow
	lGLEndRow = poTransRange.EndRow
			
		'apos;// load 1st transaction line row from GL sheet
		oCellDate = oGLSheet.getCellByPosition( COLDATE, lGLCurrRow)
		oCellTrans = oGLSheet.getCellByPosition( COLTRANS, lGLCurrRow)
		oCellDebit = oGLSheet.getCellByPosition( COLDEBIT, lGLCurrRow)
		oCellCredit = oGLSheet.getCellByPosition( COLCREDIT, lGLCurrRow)
		oCellAcct = oGLSheet.getCellByPosition( COLACCT, lGLCurrRow)
		oCellRef = oGLSheet.getCellByPosition( COLREF, lGLCurrRow)

		'apos;// extract date &amp;'apos;amp; transaction information from 1st row
		sDate = oCellDate.String
		sTrans = oCellTrans.String
	'apos;//	Ensure nonempty date field before GetMonthName call
	if Len(Trim(sDate)) = 0 then
		sErrName = "quot;ERRBADDATE"quot;
		sErrMsg = "quot;1st Date field empty - skipping"quot;
		iStatus = ERRSTOP
		GoTo DateError
	endif
		

iBrkpt=1
		sStyle = oCellDate.Text.CellStyle
		lDateFormat = oCellDate.Text.NumberFormat
		sMonth = GetMonthName(sDate)		'apos;// get month search name
		
iBrkpt = 1	'apos;// breakpoint line
		
		'apos;// load 2nd transaction line row from GL sheet
		lGLNextRow = lGLCurrRow + 1
		
		'apos;// check that 2nd row is not beyond selection
		if lGLNextRow &amp;'apos;gt; lGLEndRow then
'apos;			iStatus = msgBox("quot;Selection ends in mid-transaction!"quot;_
'apos;					+CHR(13)+CHR(10)+ "quot;Check last row selection of transaction"quot;)	
			oCellDate.Text.CellBackColor = YELLOW
			sErrName = "quot;ERRODDROWS"quot;
			sErrMsg = "quot;Selection ends in mid-transaction!"quot;_
				+CHR(13)+CHR(10)+ "quot;Check last row selection of transaction"quot;
			Call LogError(sErrName, sErrMsg)
			iRetValue = ERRSTOP
			
			GoTo AdvanceTrans
			'apos;exit For
		endif	'apos;// end would advance past last row conditional

		'apos;// load 2nd line transaction values
		oCellDateB = oGLSheet.getCellByPosition( COLDATE, lGLNextRow)
		oCellTransB = oGLSheet.getCellByPosition( COLTRANS, lGLNextRow)
		oCellDebitB = oGLSheet.getCellByPosition( COLDEBIT, lGLNextRow)
		oCellCreditB = oGLSheet.getCellByPosition( COLCREDIT, lGLNextRow)
		oCellAcctB = oGLSheet.getCellByPosition( COLACCT, lGLNextRow)
		oCellRefB = oGLSheet.getCellByPosition( COLREF, lGLNextRow)
'apos;XRay oCellDateB

		'apos;// extract date information from 2nd row of transaction
		sDateB = oCellDateB.Text.String
		
		'apos;//	Ensure nonempty date field before GetMonthName call
		if Len(Trim(sDateB)) = 0 then
			sErrName = "quot;ERRBADDATE"quot;
			sErrMsg = "quot;2nd Date field empty - skipping"quot;
			GoTo DateError
		endif

		sStyleB = oCellDateB.Text.CellStyle
		lDateBFormat = oCellDateB.Text.NumberFormat
'apos;		sMonth = GetMonthName(sDate)		'apos;// use month from 1st line

iBrkpt = 1	'apos;// breakpoint line
		
		'apos;// make sure dates, descriptions, and amounts agree
			'apos;// if not, issue message box and bail out
		iStatus = StrComp(sDate, sDateB)
		if iStatus &amp;'apos;lt;&amp;'apos;gt; 0 then
			sErrName = csERRBADDATE
			sErrMsg = "quot;Transaction date mismatch!"quot;+CHR(13)+CHR(10)_
						+ "quot;Check 1st row selection of transaction"quot;
			GoTo DateError
		endif

		GoTo CheckDesc
		
'apos;//----------Date Error handler-------------------------------
iBrkpt = 1
'apos;//	Date error(s) are terminating errors; iRetVal = -2
DateError:
		if iStatus &amp;'apos;lt;&amp;'apos;gt; 0 then
			'apos;// log error and set Date Cell YELLOW
			oCellDate.Text.CellBackColor = YELLOW
			Call LogError(sErrName, sErrMsg)
			iRetValue = ERRSTOP		'apos;// bail out on return
			GoTo AdvanceTrans		
		endif		'apos;// end any date error conditional

'apos;//-------end Date Error Handler-----------------------------

CheckDesc:
		
iBrkpt = 1	'apos;// breakpoint line

		'apos;// extract transaction information from both rows
		'apos;// make sure that descriptions agree
		
		sTrans = oCellTrans.String
		sTransB = oCellTransB.String
		iStatus = strComp(sTrans, sTransB)
		
		if iStatus &amp;'apos;lt;&amp;'apos;gt; 0 then
'apos;			iStatus = msgBox("quot;Transaction description mismatch!"quot;+CHR(13)+CHR(10)_
'apos;						+ "quot;Check transaction"quot;)
			sErrName = "quot;ERRDESCNOMATCH"quot;
			sErrMsg = 	"quot;Transaction description mismatch!"quot;+CHR(13)+CHR(10)_
						+ "quot;Check transaction"quot;
			Call LogError(sErrName, sErrMsg)
			oCellDate.Text.CellBackColor = YELLOW
			oCellDateB.Text.CellBackColor = YELLOW
			iRetValue = ERRSTOP			'apos;// force stop on return
			GoTo AdvanceTrans
		endif		'apos;// end descriptions not same

		'apos;// extract debit and credit amounts for both halves of transaction
		'apos;// ensure both halves have same amount, if not, bail out
		
		sDebit = oCellDebit.Text.String
		sCredit = oCellCredit.Text.String

		sDebitB = oCellDebitB.Text.String
		sCreditB = oCellCreditB.Text.String

'apos;//-------------------------------------------------------------------------
		'apos;// ensure that is actual double entry; either debit on 1st line and
		'apos;// Credit on second line are same amount, or Credit on 1st line
		'apos;// and Debit on second line are same amount etc.

'apos;//	Debit Credit
'apos;//   A     B			sDebit and sCredit
'apos;//   C     D			sDebitB and sCreditB		
'apos;//   A=D and B=C		correct
'apos;//  if A and D empty, B and C must be nonempty
'apos;//  if B and C empty, A and D must be nonempty
'apos;//-------------------------------------------------------------------------
iBrkpt = 1 
 
'apos; dim bTransValid as boolean
'apos; dim bADEmpty as boolean
'apos; dim bBCEmpty as boolean
  		bTransValid = (StrComp(sDebit,sCreditB) = 0) AND  (StrComp(sCredit,sDebitB)=0)
  		bTransValid = bTransValid AND ((Len(sDebit) &amp;'apos;gt; 0) OR (Len(sCredit) &amp;'apos;gt; 0))_
  								  AND  ((Len(sCreditB) &amp;'apos;gt; 0) OR (Len(sDebitB) &amp;'apos;gt; 0))			'apos;// mod060520
  		bADEmpty = (Len(sDebit)=0) AND (Len(sCreditB)=0)
  		bBCEmpty = (Len(sCredit)=0) AND (Len(sDebitB)=0)
  		bTransValid = bTransValid AND ((bADEmpty AND (Not bBCEmpty))_
  									 OR (bBCEmpty AND (Not bADEmpty)))						'apos;// mod052020
  		if Not bTransValid then
  		'apos;// message, flag both lines, skip
'apos;  			iStatus = msgBox("quot;Invalid transaction...check Debit and Credit values!"quot;+CHR(13)+CHR(10)_
'apos;  							+"quot;Transaction skipped"quot;)
			sErrName = "quot;ERRDEB&amp;'apos;lt;&amp;'apos;gt;CRED"quot;
			sErrMsg = "quot;Invalid transaction...check Debit and Credit values!"quot;+CHR(13)+CHR(10)_
  							+"quot;Transaction skipped"quot;
  			Call LogError(sErrName, sErrMsg)
  			oCellDate.Text.CellBackColor = YELLOW
			oCellDateB.Text.CellBackColor = YELLOW
			iRetValue = ERRCONTINUE
			GoTo AdvanceTrans							
  		endif	'apos;// end invalid transaction
  		    
		'apos;// extract COA account numbers for both halves of transaction
		sAcct = oCellAcct.String
		sAcctB = oCellAcctB.String
iBrkpt = 1
		'apos;// ensure both halves have COA account number; if not, just skip
		if Len( sAcct ) &amp;'apos;lt;&amp;'apos;gt; 4 OR Len(sAcctB) &amp;'apos;lt;&amp;'apos;gt; 4 then
			iStatus = msgBox("quot;Transaction contains invalid COA number(s)!"quot;_
					+CHR(13)+CHR(10)+ "quot;Check 1st row selection of transaction"quot;)	
			sErrName = csERRCOANOTFOUND
			sErrMsg = "quot;Transaction contains invalid COA number(s)!"quot;_
				+CHR(13)+CHR(10)+ "quot;Check 1st row selection of transaction"quot;
			Call LogError(sErrName, sErrMsg)
			oCellDate.Text.CellBackColor = YELLOW
			oCellDateB.Text.CellBackColor = YELLOW
			'apos;// Note. have to do the following rather than use if-then-else to loop
			'apos;// again, since this Basic does not support "quot;Continue For"quot;
			iRetValue = ERRCONTINUE 		'apos;// force Advance to next on return
			GoTo AdvanceTrans
		endif	'apos;// end length not == 4
		
		'apos;// set returned month and COA accounts
		rpsMonth = sMonth
		rpsAcct1 = sAcct
		rpsAcct2 = sAcctB
		
AdvanceTrans:
'apos;//*-----------------restore error handling for caller-----------
	'apos;// restore entry error settings.
	ErrLogSetModule(sErrCurrentMod)
	ErrLogSetSheet(iErrSheetIx)
	ErrLogSetCellInfo(lErrColumn, lErrRow)
'apos;//*----------end error handling setup---------------------------

	'apos;// iRetValue = 0, no error
	'apos;//				ERRCONTINUE (-1) - error; continue with next transaction
	'apos;//				ERRSTOP (-2) - error; stop processing transactions
	CheckDoubleEntry = iRetValue

end function 	'apos;// end CheckDoubleEntry	7/3/20
'apos;/**/

'apos;// CheckInserts.bas
'apos;//------------------------------------------------------------------
'apos;// CheckInserts - Check target COA sheet(s) for insertion available.
'apos;//		7/3/20.	wmk.
'apos;//------------------------------------------------------------------

public function CheckInserts(psMonth As String, psAcct1 As String,_
		psAcct2 As String, rpoCat1Range As Object, rpoCat2Range As Object) as Integer

'apos;//	Usage.	iVal = CheckInserts(sMonth, sAcct1, sAcct2, oCat1Range, oCat2Range)
'apos;//
'apos;//		sMonth = Month name string (e.g. "quot;January"quot;)
'apos;//		sAcct1 = COA from 1st line of transaction
'apos;//		sAcct2 = COA from 2nd line of transaction
'apos;//		rpoCat1Range = (returned)
'apos;//		rpoCat2Range = (returned)
'apos;//
'apos;// Entry.	Spreadsheet has category sheets for all possible COA #s
'apos;//
'apos;//	Exit.	rplIns1Ptr = row for insertion of line 1 transaction
'apos;//			rplIns2Ptr = row for insertion of line 2 transaction
'apos;//
'apos;// Calls.	GetInsRow.
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/1/20.		wmk.	original code; stub
'apos;//	6/2/20.		wmk.	code transported from within PlaceTransM; error handling
'apos;//						set up
'apos;//	6/8/20.		wmk.	bug fix where sCOA1Msg and sCOA2Msg not declared	
'apos;// 7/3/20.		wmk.	bug fix in error handling using lGLCurrRow, needs lErrRow
'apos;//
'apos;//	Notes. Passed paramters are COA #s; they will be used to access the
'apos;// correct account catgory sheets, for the month of the transaction
'apos;//

'apos;//	constants.
'apos;// insert error codes.
const ERRCOANOTFOUND=-1
const ERROUTOFROOM=-2
const ERRNOCOAMONTH=-3
const sERRCOANOTFOUND="quot;ERRCOANOTFOUND"quot;
const sERROUTOFROOM="quot;ERROUTOFROOM"quot;
const sERRNOCOAMONTH="quot;ERRNOCOAMONTH"quot;
const sERRUNKNOWN="quot;ERRUNKNOWN"quot;

'apos;//----------in Module error handling setup-------------------------------
'apos;// LogError setup snippet.
const csERRUNKNOWN="quot;ERRUNKNOWN"quot;
const sMyName="quot;CheckInserts"quot;
'apos;// add additional error code strings here...
Dim sErrName as String		'apos;// error name for LogError
Dim sErrMsg As String		'apos;// error message for LogError
'apos;//*---------error handling setup---------------------------
'apos;//	ErrLogGetModule() - get current module name gsErrModule
'apos;// ErrLogSetModule(sName) - set current module name gsErrModule
'apos;// ErrLogGetCellInfo(lColumn, lRow) - get error focus cell column, row
'apos;// ErrLogSetCellInfo(lColumn, lRow) - set error focus cell column, row
'apos;// ErrLogGetSheet - Get sheet index from error log globals.
'apos;// ErrLogSetSheet - Set sheet index in error log globals.
dim iErrSheetIx as Integer
dim lErrColumn as Long
dim lErrRow as Long
dim sErrCurrentMod As String
'apos;//----------end in Module error handling setup---------------------------

'apos;// process control constants.
const ERRCONTINUE=-1	'apos;// error, but continue with next transaction
const ERRSTOP=-2		'apos;// error, stop processing transactions

'apos;//	local variables.
dim iRetValue As Integer	'apos;// function returned value
dim sMonth As String		'apos;// local month

'apos;//	Object setup variables.
dim oDoc As Object			'apos;// ThisComponent
dim oSheets As Object		'apos;// Doc.getSheets()

'apos;// COA Sheet processing variables.
dim sAcct As String			'apos;// first line COA#
dim sAcctB As String		'apos;// second line COA#
dim sAcctCat1 as String 	'apos;// accounting category of 1st line of transaction
dim sAcctCat2 as String		'apos;// account category of 2nd line of transaction
dim bSameAcctCat as boolean	'apos;// account categories the same flag		
dim oCat1Sheet As Object	'apos;// COA sheet for 1st transaction line
dim oCat2Sheet As Object	'apos;// COA sheet for 2nd transaction line
dim bSameCOA As Boolean		'apos;// COAs match flag
dim lCat1InsRow As Long		'apos;// COA sheet insertion row, line 1
dim lCat2InsRow As Long		'apos;// COA sheet insertion row, line 2
dim sBadCOA As String		'apos;// first COA bad string
dim sBadCOA2 As String		'apos;// second COA bad string
dim iStatus As Integer		'apos;// processing status flag
dim sCOA1Msg As String		'apos;// COA1 error message string
dim sCOA2Msg As String		'apos;// COA2 error message string

	'apos;// code.
	
	iRetValue = 0
	iStatus = 0				'apos;// clear general status
'apos;	rplIns1Ptr = -1			'apos;// set bad pointers for return
'apos;	rplIns2Ptr = -1
	sMonth = psMonth		'apos;// set local copy of passed month
		
'apos;//*------------error handling code initialization---------------
	'apos;// preserve entry error settings.
	iErrSheetIx = ErrLogGetSheet()
	ErrLogGetCellInfo(lErrColumn, lErrRow)
	sErrCurrentMod = ErrLogGetModule()
'apos;//*	
	'apos;// set local error settings.
	ErrLogSetModule(sMyName)
	'apos;// keep entry sheet/cell selctions, since errors will be in GL
'apos;	ErrLogSetSheet(poTransRange.Sheet)
'apos;	ErrLogSetCellInfo(COLDATE, poTransRange.StartRow)
	ErrLogSetRecording(true)		'apos;// enable log entries
'apos;//*----------end error handling code initialization-------------

	
	CheckInserts = iRetValue
'apos;	if true then
'apos;		Exit Function
'apos;	endif
'apos;//---------------end stub code--------------------	
'apos;//-----------------CheckInserts code starts here----------------------------------------
'apos;//
'apos;//	Usage.	iVal = CheckInserts(psMonth, psAcct1, psAcct2, rplIns1Ptr, rplIns2Ptr)
'apos;//
'apos;//		psMonth = Month name string (e.g. "quot;January"quot;)
'apos;//		psAcct1 = COA from 1st line of transaction
'apos;//		psAcct2 = COA from 2nd line of transaction
'apos;//		rplIns1Ptr = (returned)
'apos;//		rplIns2Ptr = (returned)
'apos;//

		'apos;// get appropriate sheet names from COA account numbers
		iStatus = 0			'apos;// clear processing exceptions
		sAcct = psAcct1		'apos;// copy COAs to local vars
		sAcctB = psAcct2
		sAcctCat1 = GetTransSheetName(sAcct)
		sAcctCat2 = GetTransSheetName(sAcctB)
		bSameAcctCat = (StrComp(sAcctCat1, sAcctCat2) = 0)
dim iBrkpt As Integer
iBrkpt = 1

		'apos;// set up oCat1Sheet as sheet object for first half of transaction
		'apos;//			oCat2Sheet as sheet object for second half of transaction
'apos;static oCat1Sheet as Object	'apos;// account sheet object of 1st category in transaction
'apos;static oCat2Sheet as Object	'apos;// account sheet object of 2nd category in transaction
'apos;dim lCat1InsRow as long		'apos;// insert index for 1st category in transaction
'apos;dim lCat2InsRow as long		'apos;// insert index for 2nd cateory in transaction

'apos;XRay Doc
		oDoc = ThisComponent
		oSheets = oDoc.getSheets()

'apos;XRay oSheets
		'apos;// check if same sheet, and just copy instance						'apos;// mod051920
		oCat1Sheet = oSheets.getByName(sAcctCat1)
		if bSameAcctCat then
			oCat2Sheet = oCat1Sheet
		else
			oCat2Sheet = oSheets.getByName(sAcctCat2)
		endif
		
		rpoCat1Range.Sheet = oCat1Sheet.RangeAddress.Sheet
		rpoCat2Range.Sheet = oCat2Sheet.RangeAddress.Sheet
		
'apos;XRay oCat1Sheet		
iBrkpt=1

		'apos;// check here if COA#s different; if not, will bail out later		'apos;// mod051920
		bSameCOA = StrComp(sAcct, sAcctB) = 0								'apos;// mod051920
		lCat1InsRow = GetInsRow(oCat1Sheet, sAcct, sMonth)					'apos;// mod051920

		if bSameCOA then													'apos;// mod051920
			'apos;// Note: might get away with setting same insertion row, since will
			'apos;// insert 2 lines... for now, skipped
			lCat2InsRow = lCat1InsRow										'apos;// mod051920
		else																'apos;// mod051920
			lCat2InsRow = GetInsRow(oCat2Sheet, sAcctB, sMonth)				'apos;// mod051920
		endif	'apos;// end same COA conditional								'apos;// mod051920
				
		'apos;// add code to verify have a row in each sheet before committing to change
'apos;dim lBadRow1 	as long		'apos;// 1st acct insert row return value
'apos;dim lBadRow2	as lont		'apos;// 2nd acct insert row return value

		if lCat1InsRow &amp;'apos;lt; 0 OR lCat2InsRow &amp;'apos;lt; 0 then
			'apos;// issue appropriate message and don'apos;t mark rows as processed
			sBadCOA = "quot;"quot;
			sBadCOA2 = "quot;"quot;
			'apos;// handle transaction line 1 error.
			if lCat1InsRow &amp;'apos;lt; 0 then
				sBadCOA = sAcct
				sCOA1Msg = "quot;"quot;			'apos;// clear line 1 message
				ErrLogSetSheet(oCat1Sheet.RangeAddress.Sheet)
				ErrLogSetCellInfo(COLDATE, lErrRow)
				Select Case lCat1InsRow
'apos;				Case -1
				Case ERRCOANOTFOUND
					sCOA1Msg = "quot;Account "quot;+sBadCOA+"quot; not found"quot;
					sErrName = sERRCOANOTFOUND
'apos;				Case -2
				Case ERROUTOFROOM
					sCOA1Msg = "quot;Account "quot;+sBadCOA+"quot; month "quot;+sMonth+"quot; not enough rows"quot;_
								+"quot; to insert"quot;
					sErrName = sERROUTOFROOM
'apos;				Case -3
				Case ERRNOCOAMONTH
					sCOA1Msg = "quot;Account "quot;+sBadCOA+"quot; month "quot;+sMonth+"quot; not found"quot;
					sErrName = sERRNOCOAMONTH
				Case else
					sCOA1Msg = "quot;Account "quot;+sBadCOA+"quot;Undocumented error"quot;
					sErrName = sERRUNKNOWN
				end Select
				
				sErrMsg = sCOA1Msg
				Call LogError(sErrName, sErrMsg)
				
			endif	'apos;// end error in 1st line conditional
			
			if lCat2InsRow &amp;'apos;lt; 0 then
				sBadCOA2 = sAcctB
				sCOA2Msg = "quot;"quot;			'apos;// clear line 2 message
				Select Case lCat2InsRow
'apos;				Case -1
				Case ERRCOANOTFOUND
					sCOA2Msg = "quot;Account "quot;+sBadCOA2+"quot; not found"quot;
					sErrName = sERRCOANOTFOUND
'apos;				Case -2
				Case ERROUTOFROOM
					sCOA2Msg = "quot;Account "quot;+sBadCOA2+"quot; month "quot;+sMonth+"quot; not enough rows"quot;_
								+"quot; to insert"quot;
					sErrName = sERROUTOFROOM
'apos;				Case -3
				Case ERRNOCOAMONTH
					sCOA2Msg = "quot;Account "quot;+sBadCOA2+"quot; month "quot;+sMonth+"quot; not found"quot;
					sErrName = sERRNOCOAMONTH
	
					sCOA2Msg = "quot;Account "quot;+sBadCOA2+"quot;Undocumented error"quot;
					sErrName = sERRUNKNOWN
				end Select
				
				sErrMsg = sCOA2Msg
				Call LogError(sErrName, sErrMsg)	'apos;// log error and continue
				
			endif		'apos;// end bad line 2 insert row conditional
			
			iStatus = ERRCONTINUE			'apos;// flag error to continue with next transaction
'apos;			msgBox(sCOA1Msg+CHR(13)+CHR(10) + sCOA2Msg)
			'apos;// advance to next transaction
			GoTo AdvanceTrans
			
 		endif	'apos;// end problem with either COA insert conditional
 		
 		'apos;// no problems, set returned insertion rows
'apos; 		rplIns1Ptr = lCat1InsRow
'apos;		rplIns2Ptr = lCat2InsRow
 		rpoCat1Range.StartRow = lCat1InsRow
 		rpoCat2Range.StartRow = lCat2InsRow
 		rpoCat1Range.EndRow = lCat1InsRow
 		rpoCat2Range.EndRow = lCat2InsRow
 		rpoCat1Range.StartColumn = COLDATE
 		rpoCat2Range.StartColumn = COLDATE
 		rpoCat1Range.EndColumn = COLREF
 		rpoCat2Range.EndColumn = COLREF

AdvanceTrans:
'apos;//*-----------------restore error handling for caller-----------
	'apos;// restore entry error settings.
	ErrLogSetModule(sErrCurrentMod)
	ErrLogSetSheet(iErrSheetIx)
	ErrLogSetCellInfo(lErrColumn, lErrRow)
'apos;//*----------end error handling setup---------------------------

	'apos;// iStatus = 0, no error
	'apos;//				ERRCONTINUE (-1) - error; continue with next transaction
	'apos;//				ERRSTOP (-2) - error; stop processing transactions
	iRetValue = iStatus				'apos;// set returned status
	CheckInserts = iRetValue
	
end function 	'apos;// end CheckInserts	7/3/20
'apos;/**/

'apos;// CheckSplitTrans.bas
'apos;//---------------------------------------------------------------
'apos;// CheckSplitTrans - Check split transaction for validity.
'apos;//		7/4/20. 	wmk.	10:45
'apos;/---------------------------------------------------------------

function CheckSplitTrans(poGLSheet as Object, plSplit1stRow as Long )_
		as Long

'apos;//	Usage.	lSplitEndRow = CheckSplitTrans(oGLSheet, lSplit1stRow)
'apos;//			call PlaceSplitTrans( oGLSheet, lSplit1stRow )
'apos;//
'apos;//		oGLSheet = GL sheet object
'apos;//		lSplit1stRow = row index of first row of split transaction
'apos;//
'apos;// Entry.	GL sheet processing has found row with "quot;split"quot; as the
'apos;//			.Text.String value in COLSPLIT. The split entry contains
'apos;//			multiple rows until another row is found with "quot;split"quot; as the
'apos;//			.Text.String value in COLSPLIT. The second row of the split
'apos;//			transaction MUST contain the COA target and the total amount
'apos;//			of the split, regardless of whether it is a Debit or Credit.
'apos;//			Subsequent rows contain the amounts split into the opposing
'apos;//			COAs, and their value sums MUST equal the COA target amount.
'apos;//
'apos;//	Exit.	lSplitEndRow = last row of split, "quot;split"quot; in COLSPLIT
'apos;//						&amp;'apos;lt; 0 - error in split transaction
'apos;//						-1 - (ERRNOTSPLIT) first row not "quot;split"quot; in COLSPLIT
'apos;//						-2 - (ERRBADTALLY) split values don'apos;t add to total
'apos;//						-3 - (ERRROWSEXCEEDED) too many/few rows before second "quot;split"quot;
'apos;//						-4 - (ERRBADVALS) bad Debit/Credit values
'apos;//						-5 - (ERRBADDATE) Date field too short
'apos;//						-6 - (ERRDATENOTSAME) Date field doesn'apos;t match others
'apos;//						-7 - (ERRSPLITDESC) "quot;split"quot; descriptions don'apos;t match
'apos;//
'apos;// Calls.	ErrLogGetModule, ErrLogGetCellInfo, ErrLogGetSheet,
'apos;//			ErrLogSetSheet, ErrLogSetModule, ErrLogSetCellInfo,
'apos;//			LogError
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/22/20		wmk.	original code
'apos;//	5/23/20		wmk.	bug fix where not tallying properly; fix bug where
'apos;//						empty Date field within loop range checking rows
'apos;//	5/24/20.	wmk.	documentation added on .Type cell property; modify
'apos;//						bad row Date cell with own content to update color
'apos;//	5/27/20.	wmk.	include new error handling capability
'apos;//	5/28/20.	wmk.	error handling improvements and documentation
'apos;//	6/5/20.		wmk.	added check to ensure that transaction desc
'apos;//						matches in both "quot;split"quot; lines; added check to
'apos;//						ensure that all dates are the same in all
'apos;//						transaction lines; cell addresses updated for
'apos;//						all error reporting conditions
'apos;//	7/4/20.		wmk.	double check tally value mismatch with strings to
'apos;//						correct issue where 9.62 did not match the sum
'apos;//						of 4.81 and 4.81
'apos;//
'apos;//	Notes.
'apos;// ensure current row has "quot;split"quot; in COLSPLIT field; return -1 if not
'apos;// determine how many rows are in the "quot;split"quot;, searching for next
'apos;//  row with "quot;split"quot;; set some sort of maximum so don'apos;t search forever
'apos;//  if maximum exceeded return -3
'apos;// ensure that total values form split rows = total in 1st row
'apos;//  if not, return -2
'apos;// set up cat1 sheet to point to total'apos;s category/account sheet
'apos;// loop processing split rows, setting up cat2 sheet to point to
'apos;//   each category/account sheet
'apos;// on successful exit, point to last row with "quot;split"quot; in COLSPLIT field
'apos;// Programming notes. oCell.Type returns the cell type {EMPTY,VALUE,TEXT
'apos;// FORMULA). The Value, String, and Formula properties are used to
'apos;// set the values of a cell.

'apos;//	constants.
'apos;// local contants. (common with PlaceSplitTrans)
const MAXSPLITROWS=10	'apos;// maximum rows in "quot;split"quot; transaction
const MINROWLIMIT=1		'apos;// 0-based index minimum total row index
const MINDATELEN=6		'apos;// minimum Date string length
const ERRNOTSPLIT=-1	'apos;// first row not "quot;split"quot; in COLSPLIT
const ERRBADTALLY=-2	'apos;// split values don'apos;t add to total
const ERRROWSEXCEEDED=-3	'apos;// too many/few rows before second "quot;split"quot;
const ERRBADVALS=-4		'apos;// bad Debit/Credit values
const ERRBADDATE=-5		'apos;// Date field too short
const ERRDATENOTSAME=-6	'apos;// Date field doesn'apos;t match others			'apos;// mod060520
const ERRSPLITDESC=-7	'apos;// "quot;split"quot; descriptions don'apos;t match		'apos;// mod060520
const COLSPLIT=5		'apos;// "quot;split"quot; column
const csSplit="quot;split"quot;	'apos;// split row identifier string


'apos;//	local variables.
dim oCellSplit	as Object	'apos;// cell from COLSPLIT column
dim oGLSheet	as Object	'apos;// local ptr to poGLSheet
dim oCellCredit as Object		'apos;// credit field
dim oCellDebit 	as Object		'apos;// debit field		

'apos;// other local vars.
dim iBrkPt as integer		'apos;// easy breakpoint reference
dim lRetValue as long		'apos;// returned value
dim bBadSplit as Boolean	'apos;// bad split flag
dim sSplit As String		'apos;// COLSPLIT .Text.String
dim lSplitCount as long		'apos;// split row count
dim i as integer			'apos;// loop counter
dim lGLCurrRow as Long			'apos;// GL current row
dim bFoundSplit as boolean	'apos;// found "quot;split"quot; 2nd time
dim lTrans1stRow as long	'apos;// transaction 1st row
dim dTransTotal as Double		'apos;// split entered total
dim dSplitTally as Double		'apos;// split tallied total
dim dDebitVal as Double		'apos;// Debit value
dim dCreditVal as Double	'apos;// Credit value
dim bDebitSplits as boolean		'apos;// first line is Debit flag
dim sDebit as String		'apos;// debit value string
dim sCredit as String		'apos;// credit value string
dim lGLStartRow as Long		'apos;// start row
dim dAddAmt	as Double		'apos;// amount to add to tally
dim sSourceAcct as String	'apos;// source account being split
dim oCellDate as Object		'apos;// cell Date from transaction row
dim sDate As String			'apos;// cell Date string from transaction	'apos;// mod060520
dim sDateSplit As String	'apos;// Date field from first "quot;split"quot; line	'apos;// mod060520
dim oCellTrans As Object	'apos;// cell Transaction desc from row		'apos;// mod060520
dim sTrans As String		'apos;// transaction from end "quot;split"quot; line	'apos;// mod060520
dim sTransSplit As String	'apos;// Transaction from 1st "quot;split"quot; line	'apos;// mod060520
dim bDateMatched As Boolean	'apos;// Date field(s) all match flag		'apos;// mod060520
dim sErrMsg as String		'apos;// error message
dim sErrCode As String		'apos;// error code string

	'apos;// code.

	lRetValue = ERRNOTSPLIT	'apos;// set error return

'apos;//*---------end error handling setup---------------------------
'apos;//	ErrLogGetModule() - get current module name gsErrModule
'apos;// ErrLogSetModule(sName) - set current module name gsErrModule
'apos;// ErrLogGetCellInfo(lColumn, lRow) - get error focus cell column, row
'apos;// ErrLogSetCellInfo(lColumn, lRow) - set error focus cell column, row
'apos;// ErrLogGetSheet - Get sheet index from error log globals.
'apos;// ErrLogSetSheet - Set sheet index in error log globals.
dim iErrSheetIx as Integer
dim lErrColumn as Long
dim lErrRow as Long
dim sErrCurrentMod As String
'apos;//*
	'apos;// preserve entry error settings.
	iErrSheetIx = ErrLogGetSheet()
	ErrLogGetCellInfo(lErrColumn, lErrRow)
	sErrCurrentMod = ErrLogGetModule()
'apos;//*	
	'apos;// set local error settings.
	ErrLogSetModule("quot;CheckSplitTrans"quot;)
	ErrLogSetSheet(poGLSheet.RangeAddress.Sheet)
	ErrLogSetCellInfo(COLSPLIT, plSplit1stRow)
'apos;//*----------end error handling setup---------------------------

	'apos;// ensure current row has "quot;split"quot; in COLSPLIT field;
	'apos;//	 return ERRNOTSPLIT if not
	lGLCurrRow = plSplit1stRow
	lGLStartRow = plSplit1stRow	
	lTrans1stRow = lGLCurrRow + 1	'apos;// transaction first data row
'apos;	oGLSheet = poGLSheet
	oCellSplit = poGLSheet.getCellByPosition(COLSPLIT, lGLCurrRow)
	sSplit = oCellSplit.Text.String
	bBadSplit = (StrComp(sSplit, csSplit) &amp;'apos;lt;&amp;'apos;gt; 0)
	lRetValue = ERRNOTSPLIT			'apos;// set initial error	
iBrkPt=1

	if bBadSplit then
		lRetValue = ERRNOTSPLIT
		GoTo Bailout
	endif	'apos;// end not "quot;split"quot;

	'apos;// preserve Date field from "quot;split"quot; 1st row					'apos;// mod060520
	oCellDate = poGLSheet.getCellByPosition(COLDATE, lGLCurrRow)	'apos;// mod060520
	sDateSplit = oCellDate.String									'apos;// mod060520
	'apos;// date must be at least #/#/## long							'apos;// mod060520
	if Len(sDateSplit) &amp;'apos;lt; MINDATELEN then							'apos;// mod060520
ErrLogSetCellInfo(COLDATE, lGLCurrRow)									'apos;// mod060520
		lRetValue = ERRBADDATE										'apos;// mod060520
		GoTo BailOut												'apos;// mod060520
	endif	'apos;// end date too short conditional						'apos;// mod060520
	
	bDateMatched = true		'apos;// for AND in loop to test failure		'apos;// mod060520

	'apos;// preserve Transaction field from "quot;split"quot; 1st row				'apos;// mod060520
	oCellTrans = poGLSheet.getCellByPosition(COLTRANS, lGLCurrRow)	'apos;// mod060520
	sTransSplit = oCellTrans.String	'apos;// Transaction desc			'apos;// mod060520

	'apos;// check Date in 1st transaction row
	oCellDate = poGLSheet.getCellByPosition(COLDATE, lTrans1stRow)	'apos;//	mod060520
	bDateMatched = bDateMatched AND _
					  (StrComp(oCellDate.String, sDateSplit)=0)	'apos;// mod060520
	if NOT bDateMatched then										'apos;// mod060520
ErrLogSetCellInfo(COLDATE, lTrans1stRow)								'apos;// mod060520
		lRetValue = ERRDATENOTSAME									'apos;// mod060520
		GoTo BailOut												'apos;// mod060520
	endif
	
	'apos;// load debit, and credit, cells from 1st transaction line
ErrLogSetCellInfo(COLDEBIT, lTrans1stRow)
	oCellDebit = poGLSheet.getCellByPosition( COLDEBIT, lTrans1stRow)
	oCellCredit = poGLSheet.getCellByPosition( COLCREDIT, lTrans1stRow)
		
	'apos;// declare tally vars as double dTransTotal, dSplitTally
	sDebit = oCellDebit.Text.String
	sCredit = oCellCredit.Text.String

	'apos;// verify that one or the other is empty and set flag
	bDebitSplits = (Len(sCredit)=0) 		'apos;// debit line is split
	bBadSplit = (Len(sDebit)=0) AND (Len(sCredit)=0)	'apos;// one MUST be nonempty
		
	'apos;// check for entries in both Debit and Credit columns
	if bDebitSplits then
		bBadSplit = bBadSplit OR Len(sCredit)&amp;'apos;gt;0		
	else	
		bBadSplit = bBadSplit OR Len(sDebit)&amp;'apos;gt;0
	endif	'apos;// end debit splits conditional
		
	if bBadSplit then
		lRetValue = ERRBADVALS
		GoTo BailOut
	endif

	if bDebitSplits then
		dDebitVal = oCellDebit.getValue()		'apos;// look at debit and Credit
		dTransTotal = dDebitVal
	else
		dCreditVal = oCellCredit.getValue()
		dTransTotal = dCreditVal
	endif
		
	dSplitTally = 0.			'apos;// clear split tally

	'apos;// loop on split transaction rows, searching for 2nd "quot;split"quot;
	'apos;// and recording amount from 1st row as total,
	'apos;// then tallying amounts from subsequent rows and
	'apos;// comparing tally with 1st row value
	'apos;// credit amount, and COA account number cells
	'apos;//	when encounter "quot;split"quot;, set lRetVal to row index
	
'apos;//	loop on transaction rows looking for "quot;split"quot;

	lGLCurrRow = lTrans1stRow + 1	'apos;// skip over 1st row - already processed
	'apos;// loop beyond MAXSPLITROWS to determine if "quot;split"quot; out of bounds
	for i = 0 to MAXSPLITROWS
ErrLogSetCellInfo(COLSPLIT, lGLCurrRow)

		oCellSplit = poGLSheet.getCellByPosition(COLSPLIT, lGLCurrRow)
		sSplit = oCellSplit.Text.String
		bFoundSplit = (StrComp(sSplit, csSplit) = 0)
		
		if bFoundSplit then

			'apos;// check that last date matches the rest
			oCellDate = poGLSheet.getCellByPosition(COLDATE, lGLCurrRow)
			sDate = oCellDate.String
			bDateMatched = bDateMatched AND _
					 (StrComp(oCellDate.String, sDateSplit)=0)		'apos;// mod060520
			if NOT bDateMatched then								'apos;// mod060520
ErrLogSetCellInfo(COLDATE, lGLCurrRow)									'apos;// mod060520
				lRetValue = ERRDATENOTSAME							'apos;// mod060520
				GoTo BailOut										'apos;// mod060520
			endif

			'apos;// check that the last "quot;split"quot; description matches		'apos;// mod060520
			oCellTrans = poGLSheet.getCellByPosition(COLTRANS, lGLCurrRow)
			sTrans = oCellTrans.String								'apos;// mod060520
			if StrComp(sTrans, sTransSplit) &amp;'apos;lt;&amp;'apos;gt; 0 then				'apos;// mod060520
ErrLogSetCellInfo(COLTRANS, lGLCurrRow)								'apos;// mod060520
				lRetValue = ERRSPLITDESC							'apos;// mod060520
				GoTo BailOut										'apos;// mod060520
			endif
			
			'apos;// check not enough rows to be a valid split			
			if i &amp;'apos;lt; MINROWLIMIT then
ErrLogSetCellInfo(COLDATE, lTrans1stRow)								'apos;// mod060520
				lRetValue = ERRROWSEXCEEDED
				GoTo BailOut
			endif
if true then
GoTo ExitFor
endif

			'apos;// check tally against total
			
			if dSplitTally &amp;'apos;lt;&amp;'apos;gt; dTransTotal then
ErrLogSetCellInfo(COLREF, lGLCurrRow)								'apos;// mod060520
				lRetValue = ERRBADVALS
				GoTo BailOut
			endif
ExitFor:			
			lRetValue = lGLCurrRow
			exit For		'apos;// leave For loop

			
		else	'apos;// not found	
			'apos;// not found yet, check max exceeded			
			if i &amp;'apos;gt;= MAXSPLITROWS then
				lRetValue = ERRROWSEXCEEDED
				GoTo BailOut
			endif
			
			'apos;// if empty Date field, is error
			oCellDate = poGLSheet.getCellByPosition(COLDATE, lGLCurrRow)
		ErrLogSetCellInfo(COLDATE, lGLCurrRow)

			sDate = oCellDate.String		'apos;// get date			'apos;// mod060520
			if Len(sDate) &amp;'apos;lt; MINDATELEN then
				lRetValue = ERRBADDATE
				GoTo BailOut
			endif

			bDateMatched = bDateMatched AND _
					 (StrComp(sDate, sDateSplit)=0)				'apos;// mod060520
			if NOT bDateMatched then								'apos;// mod060520
				lRetValue = ERRBADDATE								'apos;// mod060520
				GoTo BailOut										'apos;// mod060520
			endif													'apos;// mod060520
	

			'apos;// check Debit and Credit columns; one MUST have a nonempty string
						
			'apos;// increment tally value from Debit or Credit
			if bDebitSplits then	'apos;// Debit splits - tally Credits
	ErrLogSetCellInfo(COLCREDIT, lGLCurrRow)
				oCellCredit = poGLSheet.getCellByPosition( COLCREDIT, lGLCurrRow)
				bBadSplit = (Len(oCellCredit.String) = 0)
				dAddAmt = oCellCredit.getValue()
			else	'apos;'apos;// Credit splits; tally Debits
	ErrLogSetCellInfo(COLDEBIT, lGLCurrRow)
				oCellDebit = poGLSheet.getCellByPosition( COLDEBIT, lGLCurrRow)
				dAddAmt = oCellDebit.getValue()
				bBadSplit = (Len(oCellDebit.String) = 0)
			endif
			
			'apos;// bad split if opposing column of transaction total is empty
			if bBadSplit then
				lRetValue = ERRBADVALS
				GoTo BailOut
			endif
						
			dSplitTally = dSplitTally +	dAddAmt		'apos;// add value to tally
			
		endif	'apos;// end split found conditional

		lRetValue = lGLCurrRow		'apos;// point to current row
		lGLCurrRow = lGLCurrRow + 1	'apos;// advance to next entry
		
	next i
	
	'apos;// normal return if tally matches transaction total
	if dSplitTally = dTransTotal then
		GoTo ExitNormal		'apos;// lRetVal = lGLCurrRow
	else		'apos;// tally mismatch on values, but check strings
		if StrComp(Str(dSplitTally), Str(dTransTotal))=0 then
			GoTo ExitNormal
		endif
	endif 	'apos;// end valid tally conditional
	
	lRetValue = ERRBADVALS
	
'apos;//----------- Error handler--------------------	
BailOut:
	if lRetValue &amp;'apos;gt;= 0 then
		GoTo ExitNormal
	endif

	'apos;// lGLCurrRow points to row in which error occurred
	oCellDate = poGLSheet.getCellByPosition( COLDEBIT, lGLCurrRow)
	oCellDate.String = oCellDate.String		'apos;// modify cell with own content
	oCellDate.CellBackColor = YELLOW

	'apos;// ErrLog has corrupted cell column, row; change to YELLOW
dim lCurrErrCol as Long
dim lCurrErrRow As Long
ErrLogGetCellInfo(lCurrErrCol, lCurrErrRow)
	oCellDate = poGLSheet.getCellByPosition(lCurrErrCol, lCurrErrRow)
	oCellDate.String = oCellDate.String
	oCellDate.CellBackColor = YELLOW
	
	Select Case lRetValue
		Case ERRNOTSPLIT
			sErrMsg = "quot;Transaction not 'apos;split'apos;..."quot;_
					+ CHR(13)+CHR(10)+"quot;stopped."quot;
			sErrCode = "quot;ERRNOTSPLIT"quot;
			
		Case ERRBADVALS
			sErrMsg = "quot;Bad Debit/Credit field(s); check values"quot;_
					+ CHR(13)+CHR(10)+"quot;stopped."quot;
			sErrCode = "quot;ERRBADVALS"quot;
					
		Case ERRBADTALLY
			sErrMsg = "quot;Debit and Credit values do not balance..."quot;_
					+ CHR(13)+CHR(10)+"quot;stopped."quot;
			sErrCode = "quot;ERRBADTALLY"quot;
			
		Case ERRROWSEXCEEDED
			sErrMsg = "quot;Too few or too many rows in 'apos;split'apos;..."quot;_
					+ CHR(13)+CHR(10)+"quot;stopped."quot;
			sErrCode = "quot;ERRROWSEXCEEDED"quot;

		Case ERRBADDATE
			sErrMsg = "quot;Date field too short or empty..."quot;_
					+ CHR(13)+CHR(10)+"quot;stopped."quot;			
			sErrCode = "quot;ERRBADDATE"quot;

		Case ERRDATENOTSAME	'apos;// Date field doesn'apos;t match others			'apos;// mod060520
const csDATENOTSAME="quot;ERRDATESNOTSAME"quot;
			sErrCode = csDATENOTSAME
			sErrMsg = "quot;Split - one or more dates don'apos;t match"quot;
		
		Case ERRSPLITDESC	'apos;// "quot;split"quot; descriptions don'apos;t match		'apos;// mod060520
const csSPLITDESC="quot;ERRSPLITDESC"quot;
			sErrCode = csSPLITDESC
			sErrMsg = "quot;Split - 1st and last split descriptions don'apos;t match"quot;

		Case else
			sErrMsg = "quot;Split transaction funky; check it..."quot;_
					+ CHR(13)+CHR(10)="quot;stopped."quot;
			sErrCode = "quot;ERRUNKNOWN"quot;

		End Select
		
	Call LogError(sErrCode, sErrMsg)
'apos;	msgBox(sErrMsg)		'apos;// issue error message
'apos;XRay oCellSplit

ExitNormal:

'apos;//*-----------------restore error handling for caller-----------
	'apos;// restore entry error settings.
	ErrLogSetModule(sErrCurrentMod)
	ErrLogSetSheet(iErrSheetIx)
	ErrLogSetCellInfo(lErrColumn, lErrRow)
'apos;//*----------end error handling setup---------------------------
			

	CheckSplitTrans = lRetValue
	
end function 	'apos;// end CheckSplitTrans	7/4/20
'apos;/**/

'apos;// CreateReverseTrans.bas
'apos;//---------------------------------------------------------------
'apos;// CreateReverseTrans - Create reversal transaction.
'apos;//		6/28/20.	wmk.	23:15
'apos;//---------------------------------------------------------------

public function CreateReverseTrans(poGLSheet As Object, _
					plStartRow As Long, plRowCount As Long) as Integer

'apos;// Usage.	iVal = CreateReverseTrans(oGLSheet, lStartRow, lRowCount)
'apos;//
'apos;//		oGLSheet = ledger sheet object
'apos;//		lStartRow = first row of transaction; if "quot;split"quot;, 1st "quot;split"quot; row
'apos;//		lRowCount = last row of transaction; if "quot;split"quot;, last "quot;split"quot; row
'apos;//
'apos;// Entry.	lStartRow points to the first row of either a "quot;split"quot; or
'apos;//			double entry transaction.
'apos;//			lRowCount = the total row count of the transaction
'apos;//
'apos;//	Exit.	iVal = 0 - normal exit
'apos;//				 = -1 - reverse transaction issue
'apos;//			if normal exit, original transaction is duplicated, with
'apos;//			the reversal transaction below it in the ledger sheet
'apos;//
'apos;// Calls.	none.
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/12/20.	wmk.	original code
'apos;//	6/28/20.	wmk.	ErrLogSetModule call corrected
'apos;//
'apos;//	Notes. 
'apos;//

'apos;//	constants.
const COLSPLIT=5			'apos;// column index looking for "quot;split"quot;
const ERRREVERSE=-1			'apos;// error in reverse transaction
const sERRREVERSE="quot;ERRREVERSE"quot;

'apos;//	local variables.
dim iRetValue As Integer	'apos;// returned value
dim iSheetIx As Integer		'apos;// GL sheet index

'apos;//	CellRange content variables for GL sheet.
dim oGLRange as Object		'apos;// RangeAddress object of two rows processing
dim lGLStartRow as long
dim lGLEndRow as long
dim lGLCurrRow as long		'apos;// current row in GL
dim lGLOrigRow As Long		'apos;// original transaction row index
dim oDateCell As Object		'apos;// Date field cell
dim lRowCount As Long		'apos;// local row count

'apos;// error handling setup------------------------------------------------
const sMyName="quot;CreateReverseTrans"quot;
dim sErrCode as String	'apos;// error code string for user code
Dim sErrMsg As String	'apos;// error message for LogError
dim lLogRow as long		'apos;// cell row working on
dim lLogCol as Long		'apos;// cell column working on
dim iLogSheetIx as Integer	'apos;// sheet index module working on

dim iErrSheetIx As Integer	'apos;// entry error sheet index
dim lErrColumn As Long		'apos;// entry error column ptr
dim lErrRow As Long			'apos;// entry error row
dim sErrCallerMod As String	'apos;// caller module name
'apos;// end error handling setup--------------------------------------------


	'apos;// code.
	
	iRetValue = -1			'apos;// set abnormal return
	ON ERROR GOTO ErrorHandler
'apos;//*
	'apos;// preserve entry error settings.
	iErrSheetIx = ErrLogGetSheet()
	ErrLogGetCellInfo(lErrColumn, lErrRow)
	sErrCallerMod = ErrLogGetModule()
'apos;//*	
	'apos;// set local error settings.
	ErrLogSetModule(sMyName)
	ErrLogSetSheet(poGLSheet.RangeAddress.Sheet)
	ErrLogSetCellInfo(COLSPLIT, plStartRow)
'apos;//*----------end error handling setup---------------------------
	
'apos;//---------------CreateReverseTrans starts here -----------------------
'apos;// CreateReverseTrans starts here
'apos;// Usage.	iVal = CreateReverseTrans(poGLSheet, lStartRow, lRowCount)
'apos;//
'apos;//		poGLSheet = ledger sheet object
'apos;//		lStartRow = first row of transaction; if "quot;split"quot;, 1st "quot;split"quot; row
'apos;//		lRowCount = last row of transaction; if "quot;split"quot;, last "quot;split"quot; row

	'apos;// insert lRowCount new rows above 1st transaction row in ledger
	iSheetIx = poGLSheet.RangeAddress.Sheet
	lGLStartRow = plStartRow
	lRowCount = plRowCount
	poGLSheet.Rows.insertByIndex(lGLStartRow, lRowCount)	'apos;// insert new transaction rows
	lGLOrigRow = lGLStartRow + lRowCount				'apos;// old rows moved down
	
	'apos;// copy old transaction to new rows above.
dim oTargetCellAddress As new com.sun.star.table.CellAddress
	oTargetCellAddress.Sheet = iSheetIx
	oTargetCellAddress.Column = COLDATE
	oTargetCellAddress.Row = lGLStartRow
		
dim oGLCellRange As new com.sun.star.table.CellRangeAddress
	oGLCellRange.Sheet = iSheetIx
	oGLCellRange.StartColumn = COLDATE
	oGLCellRange.EndColumn = COLREF
	oGLCellRange.StartRow = lGLOrigRow
	oGLCellRange.EndRow = lGLOrigRow + lRowCount - 1

	poGLSheet.copyRange(oTargetCellAddress, oGLCellRange)

	'apos;// now swap Debit and Credit columns in 2nd double-entry
	'apos;// to emulate transaction reversal
	'apos;// copy Debit Column to Credit column below first
	oGLCellRange.StartColumn = COLDEBIT
	oGLCellRange.StartRow = lGLStartRow
	oGLCellRange.EndColumn = COLDEBIT
	oGLCellRange.EndRow = lGLStartRow + lRowCount - 1
	
	oTargetCellAddress.Column = COLCREDIT
	oTargetCellAddress.Row = lGLOrigRow
	poGLSheet.copyRange(oTargetCellAddress, oGLCellRange)
	
	'apos;// copy Credit column to Debit column below
	
	oGLCellRange.StartColumn = COLCREDIT
	oGLCellRange.StartRow = lGLStartRow
	oGLCellRange.EndColumn = COLCREDIT
	oGLCellRange.EndRow = lGLStartRow + lRowCount - 1
	
	oTargetCellAddress.Column = COLDEBIT
	oTargetCellAddress.Row = lGLOrigRow
	poGLSheet.copyRange(oTargetCellAddress, oGLCellRange)

'apos;//end CreateReverseTrans-----------------------------------------
	iRetValue = 0		'apos;// set normal return
	
NormalExit:
	'apos;// restore caller error handling setup
	'apos;// set local error settings.
	ErrLogSetModule(sErrCallerMod)
	ErrLogSetSheet(iErrSheetIx)
	ErrLogSetCellInfo(lErrColumn, lErrRow)
	
	CreateReverseTrans = iRetValue
	exit function
	
ErrorHandler:
	'apos;// log error and highlight date field in 1st row
	sErrCode = sERRREVERSE
	sErrMsg = "quot;Error in reverse transaction..."quot; _
			+ CHR(13)+CHR(10) + "quot;Reverse transaction failed."quot;
	Call LogError(sErrCode, sErrMsg)
	oDateCell = poGLSheet.getCellByPosition(COLDATE, lGLStartRow)
	oDateCell.String = oDateCell.String
	oDateCell.CellBackColor = YELLOW
	GoTo NormalExit
	
end function 	'apos;// end CreateReverseTrans	6/28/20
'apos;/**/

'apos;// D.bas
'apos;//------------------------------------------------------------------
'apos;// D - stub function to enable calls to development subs/functions.
'apos;//		5/27/20.	wmk.
'apos;//------------------------------------------------------------------

public function D() as void

'apos;//	Usage.	=D()
'apos;//
'apos;// Entry.	public const DEVDEBUG set = true/false
'apos;//			most tests coded here depend upon the =D() reference being
'apos;//			in the same row as the data to be tested, since the called
'apos;//			subs/functions will look at the current range selection.
'apos;//			When the user presses &amp;'apos;lt;Enter&amp;'apos;gt; after completing the formula
'apos;//			referencing D(), the range selection is the cell in which
'apos;//			the formula occurs.
'apos;//
'apos;//	Exit.	varies according to Calls.
'apos;//			if DEVDEBUG=false, exits without doing anything
'apos;//
'apos;//	Calls.	varies according to test needed.
'apos;//			PlaceSplitTrans
'apos;/
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/13/20		wmk.	original code
'apos;//	5/16/20.	wmk.	documentation; test calls to GetTransSheetName
'apos;//	5/22/20.	wmk.	test call to PlaceSplitTrans
'apos;//	5/27/20.	wmk.	DEVDEBUG constant used to activate/deactivate
'apos;//
'apos;//	Notes. If DEVDEBUG=true, D acts to test whatever functions/subs
'apos;// its code is set up for. Usually the &amp;'apos;lt;Insert notes here&amp;'apos;gt;
'apos;//

'apos;// local variables. (defined as needed)
dim Doc as object		'apos;// current component
dim oSel as object		'apos;// current selection
dim GLSheet as object	'apos;// current sheet selected
dim oRange as object	'apos;// current selection within sheet
dim oSheets As Object		'apos;// sheets array of objects
Dim oGLSheet As Object		'apos;// GL sheet object
dim iSheetIx as Integer		'apos;// sheet index
dim sGLSheet as String		'apos;// GL sheet name 	

dim	lGLStartRow	as long
dim	lGLEndRow	as long
dim	lGLRowCount	as long
dim	lGLNewRow	as long
dim	lGLCurrRow	as long
dim	lNRowsSelected	as long
dim	lTransCount	as long
dim	bOddRowCount	as boolean

dim iBrkpt as integer
dim lSplitEnd	as long		'apos;// end row of split transaction
dim oCellDate	as Object

	'apos;// code.
	'apos;// NEVER REMOVE THIS CONDITIONAL.
	if NOT DEVDEBUG then
		Exit Function
	endif
	
	Doc = ThisComponent
	oSel = Doc.getCurrentSelection()	'apos;// get current cell selection(s) info
	oRange = oSel.RangeAddress			'apos;// extract range information
	iSheetIx = oRange.Sheet				'apos;// get sheet index value
	oGLSheet = Doc.Sheets(iSheetIx)		'apos;// set up sheet object
'apos;XRay oGLSheet		'apos;// xray to get .sun. reference
	sGLSheet = oGLSheet.CodeName			'apos;// get this sheet name

iBrkpt=1	

	'apos;// set row bounds from selection information
	lGLStartRow = oRange.StartRow
	lGLEndRow = oRange.EndRow
	lGLRowCount = lGLEndRow-lGLStartRow
	lGLNewRow = lGLStartRow - 2			'apos;// kludge new row for loop
	lGLCurrRow = lGLStartRow - 2		'apos;// current GL processing row preset for increment
	lNRowsSelected = lGLEndRow+1 - lGLStartRow
	lTransCount = lNRowsSelected/2	'apos;// get transaction count (2 rows per)
	bOddRowCount = lTransCount*2 &amp;'apos;lt;&amp;'apos;gt; lNRowsSelected

	lSplitEnd = PlaceSplitTrans(oGLSheet, lGLStartRow)
	
	if lSplitEnd &amp;'apos;lt; 0 then
		oCellDate = oGLSheet.getCellByPosition(COLDATE, lGLStartRow)
		oCellDate.Text.CellBackColor = YELLOW
		msgBox("quot;Split transaction - bad transaction; check data"quot;)
	endif
	
'apos;	lSplitEnd = PlaceSplitTrans(oGLSheet, lGLStartRow)	'apos;// check static var
	
iBrkPt=1
				
end function	'apos;// end D
'apos;/**/

'apos;// DayName.bas
'apos;// DayName - Return text name of day from week day number
public Function DayName(nDay as integer) as string

'apos;//	Modification history.
'apos;//	---------------------
'apos;//	12/8/15.	wmk.	Added "quot;??"quot; return value if nDay type is unkown.

'apos;DayName = "quot;Monday"quot;
dim strDay as string

'apos;//	Code.

select case nDay

case 1
	strDay = "quot;Sunday"quot;
case 2
	strDay ="quot;Monday"quot;
case 3
	strDay = "quot;Tuesday"quot;
case 4
	strDay = "quot;Wednesday"quot;
case 5
	strDay = "quot;Thursday"quot;
case 6
	strDay = "quot;Friday"quot;
case 7
	strDay = "quot;Saturday"quot;
	
case else
	strDay = "quot;??"quot;
	
end select


DayName = strDay
end function	'apos;// end Dayname
'apos;/**/

'apos;// E.bas
'apos;//------------------------------------------------------------------
'apos;// E - stub function to test error handling capabilities.
'apos;//		5/27/20.	wmk.
'apos;//------------------------------------------------------------------

public function E() as void

'apos;//	Usage.	=E()
'apos;//
'apos;// Entry.	public const DEVDEBUG set = true/false
'apos;//			most tests coded here depend upon the =D() reference being
'apos;//			in the same row as the data to be tested, since the called
'apos;//			subs/functions will look at the current range selection.
'apos;//			When the user presses &amp;'apos;lt;Enter&amp;'apos;gt; after completing the formula
'apos;//			referencing D(), the range selection is the cell in which
'apos;//			the formula occurs.
'apos;//
'apos;//	Exit.	varies according to Calls.
'apos;//			if DEVDEBUG=false, exits without doing anything
'apos;//
'apos;//	Calls.	varies according to test needed.
'apos;//			ErrLogSetup, LogError, ErrLogDisable
'apos;/
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/25/20.	wmk.	original code
'apos;//	5/27/20.	wmk.	DEVDEBUG constant used to activate/deactivate
'apos;//
'apos;//	Notes. If DEVDEBUG=true, D acts to test whatever functions/subs
'apos;// its code is set up for. Usually the &amp;'apos;lt;Insert notes here&amp;'apos;gt;

'apos;// local variables. (defined as needed)
dim Doc As Object			'apos;// this component
dim oSel As Object			'apos;// current selection
dim oRange As Object		'apos;// CellRangeAddress object

	'apos;// code.
	if NOT DEVDEBUG then
		Exit Function
	endif
	
	Doc = ThisComponent
	oSel = Doc.getCurrentSelection()	'apos;// get current cell selection(s) info
	oRange = oSel.RangeAddress			'apos;// extract range information
	Call ErrLogSetup(oRange, "quot;Test sub E"quot;)
	Call LogError("quot;TESTERROR"quot;, "quot;Test sub E - error"quot;)
	Call ErrLogDisable
					
end function	'apos;// end E
'apos;/**/

'apos;// ErrLogDisable.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogDisable - Disable error logging.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public Function ErrLogDisable() As Void

'apos;//	Usage.	macro call or
'apos;//			call ErrLogDisable
'apos;//
'apos;// Entry.	&amp;'apos;lt;entry conditions&amp;'apos;gt;
'apos;//
'apos;//	Exit.	gbErrLogging = false
'apos;//			goErrRangeAddress object released
'apos;//			gsErrMsg = "quot;"quot;
'apos;//			gsErrName = "quot;"quot;
'apos;//			gsErrModule = "quot;"quot;
'apos;//			gsErrSheet = "quot;"quot;
'apos;//
'apos;// Calls.
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/25/20.	wmk.	original code
'apos;// 5/26/20.	wmk.	change from sub to function

'apos;//	constants.

'apos;//	local variables.

	'apos;// code.
	'apos;// if not Basic, add code to release goErrRangeAddress conditionally
	
	gbErrLogging = false
	gsErrMsg = "quot;"quot;
	gsErrName = "quot;"quot;
	gsErrModule = "quot;"quot;
	gsErrSheet = "quot;"quot;
	
end Function		'apos;// end ErrLogDisable
'apos;/**/

'apos;// ErrLogGetCellInfo.bas
'apos;//-----------------------------------------------------------------
'apos;// ErrLogGetCellInfo - Get cell information from error log globals.
'apos;//		wmk. 5/26/20.
'apos;//-----------------------------------------------------------------

public Function ErrLogGetCellInfo(rplColumn As Long, rplRow As Long) As Void

'apos;//	Usage.	ErrLogGetCellInfo(lColumn, lRow)
'apos;//
'apos;// Entry.	goErrLogRange = current cell tracking
'apos;//					.StartColumn = starting column index
'apos;//					.StartRow = starting row index
'apos;//
'apos;//	Exit.	lColumn = goErrLogRange.StartColumn
'apos;//			lRow = goErrLogRange.StartRow
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. Complemented by ErrLogSetInfo(lColumn, lRow)
'apos;//

'apos;//	local variables

	'apos;// code.
	rplColumn = goErrRangeAddress.StartColumn
	rplRow = goErrRangeAddress.StartRow
	
end Function		'apos;// end ErrLogGetCellnfo
'apos;/**/

'apos;// ErrLogGetCellName.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogGetCellName - Get cell name from error log globals.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public Function ErrLogGetCellName() As String

'apos;//	Usage.	sCellName = ErrLogGetCellName()
'apos;//
'apos;// Entry.	goLogRange = current cell tracking
'apos;//
'apos;//	Exit.	sCellName = "quot;XYY"quot; where
'apos;//				X = column letter converted from goLogRange.StartColumn
'apos;//				Y = row number converted from goLogRange.StartRow
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code; stub
'apos;//
'apos;//	Notes. Complemented by ErrLogGetModule()
'apos;//

'apos;//	local variables
dim sRetStr As String		'apos;// returned cell string

	'apos;// code.
	sRetStr = "quot;"quot;
	ErrLogGetCellName = sRetStr	'apos;// return current name
	
end Function		'apos;// end ErrLogGetCellName
'apos;/**/

'apos;// ErrLogGetDisplay.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogGetDisplay - Get msgBox error messaging on/off status.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public function ErrLogGetDisplay() As Boolean

'apos;//	Usage.	bMsgDisplay = ErrLogGetDisplay()
'apos;//
'apos;// Entry.	gbErrDisplay = current msgBox error messaging status
'apos;//
'apos;//	Exit.	bMsgDisplay = gbErrDisplay
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;// Notes. gbErrDisplay flag is used by LogError to determine if
'apos;//	error messages are displayed in a msgBox onscreen

	'apos;// code.
	bRetVal = gbErrDisplay
	ErrLogGetDisplay = bRetVal

end function 	'apos;// end ErrLogGetDisplay
'apos;/**/

'apos;// ErrLogGetModule.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogGetModule - Get module name in error log globals.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public Function ErrLogGetModule() As String

'apos;//	Usage.	sCurrModule = ErrLogGetModule()
'apos;//
'apos;// Entry.	gsErrModule = current module name global
'apos;//
'apos;//	Exit.	sCurrModule = gsErrModule
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. Complemented by ErrLogGetModule()
'apos;//

'apos;//	local variables.

	'apos;// code.
	ErrLogGetModule = gsErrModule		'apos;// return current name
	
end Function		'apos;// end ErrLogGetModule
'apos;/**/

'apos;// ErrLogGetRecording.bas
'apos;//-----------------------------------------------------------------------
'apos;// ErrLogGetRecording - Get error recording status from error log globals
'apos;//		wmk. 5/26/20.
'apos;//-----------------------------------------------------------------------

public function ErrLogGetRecording() As Boolean

'apos;//	Usage.	bLogRecording = ErrLogGetRecording()
'apos;//
'apos;// Entry.	gbErrRecording = current log recording status
'apos;//
'apos;//	Exit.	bLogRecording = gbErrRecording
'apos;//							true if log recording on
'apos;//							false if log recording off
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. LogError uses the gbErrRecording flag to determine if Error Log
'apos;// sheet (ERRLOGSHEET) entries are to be made.

'apos;//	local variables.
dim bRetValue	as Boolean

	'apos;// code.
	
	bRetValue = gbErrRecording
	ErrLogGetRecording = bRetValue

end function 	'apos;// end ErrLogGetRecording
'apos;/**/

'apos;// ErrLogGetSheet.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogGetSheet - Get sheet index from error log globals.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public Function ErrLogGetSheet() As Integer

'apos;//	Usage.	iSheetIx = ErrLogGetSheet()
'apos;//
'apos;//			iSheet = new sheet index to set in goErrRangeAddress.Sheet global
'apos;//
'apos;// Entry.	goErrRangeAddress.Sheet = current error focus sheet index
'apos;//
'apos;//	Exit.	iSheetIx = goErrRangeAddress.Sheet = new error focus sheet index
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. Complemented by ErrLogSetSheet(iSheet)
'apos;//

'apos;//	local variables.

	'apos;// code.
	
	ErrLogGetSheet = goErrRangeAddress.Sheet	'apos;// return global sheet index
	
end Function		'apos;// end ErrLogGetSheet
'apos;/**/

'apos;// ErrLogMakeEntry.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogMakeEntry - record entry in ERRLOGSHEET spreadsheet.
'apos;//		wmk. 6/6/20. 09:45
'apos;//---------------------------------------------------------------

public function ErrLogMakeEntry() As Integer

'apos;//	Usage.	iStatus = ErrLogMakeEntry()
'apos;//
'apos;// Entry.	error handling global values set
'apos;//
'apos;//			ERRLOGSHEET = (string) name of error log sheet
'apos;//
'apos;//	Exit.	iStatus = 0 if log entry made successfully
'apos;//					&amp;'apos;lt; 0 if failed
'apos;//
'apos;// Calls.	SetSheetDate(oLogRange, FMTDATETIME)
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20.	wmk.	original code
'apos;//	5/28/20.	wmk.	time stamp column added
'apos;//	6/3/20.		wmk.	add date/time stamp to error log sheet heading.
'apos;// 6/6/20.		wmk.	update SetSheetDate call with format spec
'apos;//
'apos;//	Notes. &amp;'apos;lt;Insert notes here&amp;'apos;gt;
'apos;//	Sheet	Column	Row	Module	ERRCODE	Message

'apos;//	constants.
const COLTIME=0			'apos;// Time Stamp column 
const COLSHEET=1		'apos;// Sheet column
const COLCOLUMN=2		'apos;// Column column
const COLROW=3			'apos;// Row column
const COLMODULE=4		'apos;// Module column
const COLERRCODE=5		'apos;// ERRCODE column
const COLERRMSG=6		'apos;// Message column
const ERRINSROW=5		'apos;// insertion row index
'apos;const FMTDATETIME=50	'apos;// numeric value of mm/dd/yyy hh:mm:ss spec

'apos;//	local variables.
dim iBrkPt as Integer		'apos;// easy breakpoint spotter
dim iRetValue As Integer	'apos;// return value
dim iStatus As Integer		'apos;// general status
dim oDoc as Object			'apos;// ThisComponent
dim oRange as Object		'apos;// range address of this document
dim oLogSheet as Object		'apos;// sheet ERRLOGSHEET
dim oSheets as Object		'apos;// Doc.Sheets()
dim iSheetIx as Integer		'apos;// sheet index of ERRLOGSHEET
dim oSel as Object
dim sSheet As String		'apos;// sheet name for ERRLOGSHEET
dim oCellSheet As Object	'apos;// Sheet cell
dim oCellTime As Object		'apos;// Time stamp cell
dim oCellColumn As Object	'apos;// Column cell
dim oCellRow As Object		'apos;// Row cell
dim oCellModule As Object	'apos;// Module cell
dim oCellErrCode As Object	'apos;// Error Code cell
dim oCellErrMsg As Object	'apos;// Error Message cell
dim oRangeAdd As Object		'apos;// range address object
dim oErrSheet As Object		'apos;// sheet error occurred in

	'apos;// code.
	
	iRetValue = -1	'apos;// set initial failure
	ON ERROR GoTo BailOut
	oDoc = ThisComponent
	oSheets = oDoc.getSheets()			'apos;// get sheet collection
	oSel = oDoc.getCurrentSelection()	'apos;// get current cell selection(s) info
	oRange = oSel.RangeAddress			'apos;// extract range information
	iSheetIx = oRange.Sheet				'apos;// get sheet index value
'apos;	oGLSheet = Doc.Sheets(iSheetIx)		'apos;// set up sheet object
'apos;XRay oGLSheet		'apos;// xray to get .sun. reference
'apos;	sGLSheet = oGLSheet.CodeName			'apos;// get this sheet name

'apos;XRay oSheets	
	sSheet = ERRLOGSHEET
	oLogSheet = oSheets.getByName(sSheet)
	
	'apos;// see if ERRLOGSHEET exists and set up insertion pointers for
	'apos;// new row
	oRangeAdd = new com.sun.star.table.CellRangeAddress
	oRangeAdd.Sheet = oLogSheet.RangeAddress.Sheet
	oRangeAdd.StartColumn = COLTIME
	oRangeAdd.EndColumn = COLERRMSG
	oRangeAdd.StartRow = ERRINSROW
	oRangeAdd.EndRow = ERRINSROW
	
	'apos;// insert new row into log sheet
	oLogSheet.insertCells(oRangeAdd,_
						com.sun.star.sheet.CellInsertMode.ROWS)
	
	'apos;// set values in new row fields from global error variables
iBrkPt=1
	oCellTime = oLogSheet.getCellByPosition(COLTIME, ERRINSROW)		'apos;// Time stamp cell
	oCellSheet = oLogSheet.getCellByPosition(COLSHEET, ERRINSROW)	'apos;// Sheet cell
	oCellColumn = oLogSheet.getCellByPosition(COLCOLUMN, ERRINSROW)	'apos;// Column cell
	oCellRow = oLogSheet.getCellByPosition(COLROW, ERRINSROW)	'apos;// Row cell
	oCellModule = oLogSheet.getCellByPosition(COLMODULE, ERRINSROW)	'apos;// Module cell
	oCellErrCode = oLogSheet.getCellByPosition(COLERRCODE, ERRINSROW)'apos;// Error Code cell
	oCellErrMsg = oLogSheet.getCellByPosition(COLERRMSG, ERRINSROW)	'apos;// Error Message cell
	oErrSheet = oSheets.getByIndex(goErrRangeAddress.Sheet)

	oCellTime.setValue(Now())					'apos;// time stamp
	oCellTime.NumberFormat = FMTDATETIME		'apos;// set number format
	oCellSheet.String = oErrSheet.CodeName		'apos;// set sheet name
	oCellColumn.String = Str(goErrRangeAddress.StartColumn)
	oCellRow.String = Str(goErrRangeAddress.StartRow)
	oCellModule.String = gsErrModule
	oCellErrCode.String = gsErrName
	oCellErrMsg.String = gsErrMsg
	
	'apos;// set sheet modification date in log sheet header
	iStatus = SetSheetDate(oRangeAdd, FMTDATETIME)
	
	'apos;// set iRetValue = 0 for success

	iRetValue = 0		'apos;// if make it here, success...

BailOut:

	if iRetValue &amp;'apos;lt; 0 then	'apos;// if problem accessing ERRLOGSHEET
		msgBox("quot;Log Sheet 'apos;"quot;+ERRLOGSHEET+"quot;'apos; not found!"quot;)
	endif
	
	ErrLogMakeEntry = iRetValue

end function 	'apos;// end ErrLogMakeEntry	6/3/20
'apos;/**/

'apos;// ErrLogSetCellInfo.bas
'apos;//-----------------------------------------------------------------
'apos;// ErrLogSetCellInfo - Set cell information in error log globals.
'apos;//		wmk. 5/26/20.	16:00
'apos;//-----------------------------------------------------------------

public Function ErrLogSetCellInfo(plColumn As Long, plRow As Long) As Void

'apos;//	Usage.	ErrLogSetCellInfo(lColumn, lRow)
'apos;//
'apos;// Entry.	goLogRange = current cell tracking
'apos;//					.StartColumn = starting column index
'apos;//					.StartRow = starting row index
'apos;//
'apos;//	Exit.	lColumn = goLogRange.StartColumn
'apos;//			lRow = goLogRange.StartRow
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. Complemented by ErrLogGetInfo(lColumn, lRow)
'apos;//

'apos;//	local variables

	'apos;// code.
	goErrRangeAddress.StartColumn = plColumn
	goErrRangeAddress.StartRow = plRow
	
end Function		'apos;// end ErrLogSetCellnfo
'apos;/**/

'apos;// ErrLogSetDisplay.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogSetDisplay - Set msgBox error messaging on/off.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public function ErrLogSetDisplay(pbDisplayOn As Boolean) As Void

'apos;//	Usage.	ErrLogSetDisplay(bDisplayOn)
'apos;//
'apos;//		bDisplayOn = true to turn on msgBpx error messaging
'apos;//					 false to turn off msgBox error messaging
'apos;//
'apos;// Entry.	gbErrDisplay = current msgBox error messaging status
'apos;//
'apos;//	Exit.	gbErrDisplay  = bDisplayOn
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20.	wmk.	original code
'apos;//
'apos;// Notes. gbErrDisplay flag is used by LogError to determine if
'apos;//	error messages are displayed in a msgBox onscreen

	'apos;// code.
	gbErrDisplay = pbDisplayOn

end function 	'apos;// end ErrLogSetDisplay
'apos;/**/

'apos;// ErrLogSetModule.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogSetModule - Set module name in error log globals.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public Function ErrLogSetModule(psName as String) As Void

'apos;//	Usage.	ErrLogSetModule(sName)
'apos;//
'apos;//			sName = new name to set in error module caller global
'apos;//
'apos;// Entry.	gsErrModule = current module name global
'apos;//
'apos;//	Exit.	gsErrModule = new error module caller name
'apos;//
'apos;// Calls.
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. Complemented by ErrLogGetModule()
'apos;//

'apos;//	local variables.

	'apos;// code.
	gsErrModule = psName	'apos;// reset global module name
	
end Function		'apos;// end ErrLogSetModule
'apos;/**/

'apos;// ErrLogSetRecording.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogSetRecording - Set error recording status in error log globals
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public function ErrLogSetRecording(pbRecordingOn as Boolean) As Void

'apos;//	Usage.	ErrLogSetRecording(bRecordingOn)
'apos;//
'apos;//		bRecordingOn = true to turn on log recording in ERRLOGSHEET
'apos;//					   false to turn off log recording in ERRLOGSHEET
'apos;//
'apos;// Entry.	gbErrRecording = current ERRLOGSHEET recording status
'apos;//
'apos;//	Exit.	gbErrRecording = bRecordingOn
'apos;//
'apos;// Calls.	&amp;'apos;lt;other subs/functions called&amp;'apos;gt;
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. LogError uses the gbErrRecording flag to determine if Error Log
'apos;// sheet (ERRLOGSHEET) entries are to be made.
'apos;//	Eventually, this should issue a message to the Error Log
'apos;// sheet reporting the recording on/off event.

	'apos;// code.
	gbErrRecording = pbRecordingOn

end function 	'apos;// end ErrLogSetRecording
'apos;/**/

'apos;// ErrLogSetSheet.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogSetSheet - Set sheet index in error log globals.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public Function ErrLogSetSheet(piSheet as Integer) As Void

'apos;//	Usage.	ErrLogSetModule(iSheet)
'apos;//
'apos;//			iSheet = new sheet index to set in goErrRangeAddress.Sheet global
'apos;//
'apos;// Entry.	goErrRangeAddress.Sheet = current error focus sheet index
'apos;//
'apos;//	Exit.	goErrRangeAddress.Sheet = new error focus sheet index
'apos;//
'apos;// Calls.
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. Complemented by ErrLogGetSheet()
'apos;//

'apos;//	local variables.

	'apos;// code.
	goErrRangeAddress.Sheet = piSheet	'apos;// reset global sheet index
	
end Function		'apos;// end ErrLogSetSheet
'apos;/**/

'apos;// ErrLogSetup.bas
'apos;//---------------------------------------------------------------
'apos;// ErrLogSetup - set up error logging to ERRLOGSHEET.
'apos;//		wmk. 5/26/20.
'apos;//---------------------------------------------------------------

public sub ErrLogSetup(poCellRange as Object, psModule as String)

'apos;//	Usage.	macro call or
'apos;//			call ErrLogSetup( oCellRange, sModule)
'apos;//
'apos;//			oCellRange = .CellRangeAddress object with following
'apos;//							.Sheet = sheet index
'apos;//							.StartColumn = starting column of range
'apos;//							.EndColumn = ending column of range
'apos;//							.StartRow = starting row of range
'apos;//							.EndRow = ending row of range
'apos;//			sModule = (string) name of sub/function for logging
'apos;//
'apos;// Entry.	none.
'apos;//
'apos;//	Exit.	global variables set for ErrLogError
'apos;//			gbErrLogging = true
'apos;//			gsErrModule = sModule
'apos;//			gsErrMsg = "quot;"quot;
'apos;//			gsErrName = "quot;"quot;
'apos;//			gsErrSheet = "quot;"quot;
'apos;//			goErrRangeAddress = oCellRange object values
'apos;//
'apos;// Calls.
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20		wmk.	original code
'apos;//
'apos;//	Notes. This sub should be the first call from any sub/function
'apos;//	desiring debug/error logging. The gbErrLogging var should be
'apos;// tested before calling the LogError sub to ensure that ErrLogSetup
'apos;//	has been called and errors within the error logging will be avoided.
'apos;// 

'apos;//	constants.

'apos;//	local variables.
Static bBeenSetup as boolean	'apos;// very 1st time flag
dim oDoc	As Object		'apos;// this component
dim oSheets As Object		'apos;// Sheets() array
dim oCurrSheet as Object	'apos;// current sheet

	'apos;// code.
	if IsNull(poCellRange) then
		msgBox("quot;ErrLogSetup - null pointer passed for cell range."quot;)
		GoTo BailOut
	endif

	if IsNull(psModule) then
		msgBox("quot;ErrLogSetup - null string passed for module name."quot;)
		GoTo BailOut
	endif

	if NOT bBeenSetup then
		bBeenSetup = true
		goErrRangeAddress = new com.sun.star.table.CellRangeAddress
	endif
		
	'apos;// new module creates fresh instance
	if StrComp(psModule, gsErrModule) &amp;'apos;lt;&amp;'apos;gt; 0 then
'apos;XRay goErrRangeAddress	'apos;// no .Release method
		goErrRangeAddress = new com.sun.star.table.CellRangeAddress
	endif 		'apos;// end new module conditional

	oDoc = ThisComponent
	oCurrSheet = oDoc.Sheets(poCellRange.Sheet)		'apos;// set up sheet object
	gsErrSheet = oCurrSheet.CodeName				'apos;// set up sheet name
	goErrRangeAddress.Sheet = oCurrSheet.RangeAddress.Sheet
	goErrRangeAddress.StartColumn = poCellRange.StartColumn
	goErrRangeAddress.EndColumn = poCellRange.EndColumn
	goErrRangeAddress.StartRow = poCellRange.StartRow
	goErrRangeAddress.EndRow = poCellRange.EndRow
	gbErrDisplay = true							'apos;// display loggin on
	gbErrRecording = false						'apos;// log sheet recording on/off flag
	gbErrLogging = true							'apos;// enable error logging
	gsErrMsg = "quot;"quot;
	gsErrName = "quot;"quot;
	gsErrModule = psModule						'apos;// initialize module name
	
Bailout:

end sub		'apos;// end ErrLogSetup
'apos;/**/

'apos;// GetInsRow.bas
'apos;//------------------------------------------------------------------
'apos;// GetInsRow - Get insertion row given sheet, COA account, month.
'apos;//		wmk. 6/8/20. 15:00
'apos;//------------------------------------------------------------------

function GetInsRow( poSheet, psAcctCat, psMonth) as long

'apos;//	Usage.	lInsRow = GetInsRow( oSheet, sAcctCat, sMonth)
'apos;//
'apos;//		oSheet = Sheet object from Doc.Sheets[]
'apos;//		sAcctCat = accounting COA to search for
'apos;//		sMonth = month to search for within category
'apos;//
'apos;//	Exit.	lInsRow = &amp;'apos;gt;= 0; row index at which to insert new transaction
'apos;//							returned index is where COA and month found + INSBIAS
'apos;//					= -1 COA not found at all
'apos;//					= -2 COA and month found, but not enough room to insert
'apos;//					= -3 COA found, but month not found before COA changed
'apos;//
'apos;// Calls.	&amp;'apos;lt;other subs/functions called&amp;'apos;gt;
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/17/20		wmk.	original code; stub.
'apos;//	5/18/20.	wmk.	code fleshed out
'apos;//	5/20/20.	wmk.	vars compliant with EXPLICIT
'apos;// 5/23/20.	wmk.	clarify calling sequence documentation
'apos;// 6/8/20.		wmk.	mod to stop checking if have encountered a COA
'apos;//						that is &amp;'apos;gt; COA looking for and flag not found

'apos;//
'apos;//	Notes. Does row-by-row search for line containing the COA account number
'apos;// in Column A, and the month in ColumnB
'apos;//

'apos;//	constants. (see module-wide constants)

'apos;//	local variables.
dim il			as Long		'apos;// loop index
dim iBrkPt		as Integer	'apos;// easy breakpoint marker
dim	j			as integer	'apos;// short loop index
dim lRowIndex	as Long		'apos;// returned row index
dim sAcctCat	as String	'apos;// localized reference to psAcctCat
dim sMonth		as String	'apos;// localized reference to psMonth
dim bCOAMatched	as Boolean	'apos;// found COA flag
dim bNoMatch	as Boolean	'apos;// this one not COA looking for flag

'apos;// sheet information variables.
dim lStartRow	as Long		'apos;// sheet starting row (should be 0)
dim lStartColumn	as Long		'apos;// sheet starting column
dim lEndRow		as Long		'apos;// sheet end row
dim lEndColumn		as Long		'apos;// sheet end column
dim oRange		as Object	'apos;// RangeAddress object
Static oCellDate	as Object
Static oCellTrans	as Object
dim sRowDate	as String	'apos;// string from Date column
dim sRowTrans	as String	'apos;// string from Transaction column
dim sScanDate	as String	'apos;// Date field from current scan row
'apos;dim oRow		as Object	'apos;// row cells from search row index

	'apos;// code.

	lRowIndex = -1		'apos;// set not found
	sAcctCat = psAcctCat
	sMonth = psMonth
	oRange = poSheet.RangeAddress
	lStartRow = oRange.StartRow
	lStartColumn = oRange.StartColumn
	lEndRow = oRange.EndRow
	lEndColumn = oRange.EndColumn
	bCOAMatched = false		'apos;// set COA not found yet	

iBrkpt = 1
'apos;XRay poSheet
	
	'apos;// for each row 0 to sheet last row
	for il = lStartRow to lEndRow

		'apos;// search for month first to ease debugging, since there are so many
		'apos;// less month matches...		

		'apos;// check Transaction field for month match
		
		oCellTrans = poSheet.getCellByPosition(COLTRANS, il)		
		sRowTrans = oCellTrans.String

'apos;// debugging
if Len(sRowTRans)&amp;'apos;lt;&amp;'apos;gt;Len(sMonth) then
iBrkPt=0
else
iBrkPt=1
endif
iBrkPt = StrComp(sRowTrans, sMonth)
'apos;//---end debugging

		if StrComp(sRowTrans, sMonth) &amp;'apos;lt;&amp;'apos;gt; 0 then		'apos;// if not month match, skip out
			GoTo AdvanceRow
		endif

iBrkPt=1	

		'apos;// check Date field for COA account match
		oCellDate = poSheet.getCellByPosition(COLDATE, il)
		sRowDate = oCellDate.String
		if Len(sRowDate) &amp;'apos;lt;&amp;'apos;gt; COALEN then 	'apos;// if not correct COA account length, skip out
			GoTo AdvanceRow
		endif
	
iBrkPt=1	
		bNoMatch = StrComp(sRowDate, sAcctCat) &amp;'apos;lt;&amp;'apos;gt; 0	
		if bNoMatch then	'apos;// if not a match
'apos;			if bCOAMatched then	'apos;// but matched before, COA changed
'apos;				lInsertRow = -3		'apos;// no month match for account
'apos;				exit For
'apos;			else
'apos;				GoTo AdvanceRow		'apos;// keep looking for matching COA
'apos;			endif	'apos;// end found COA before condtional
			
			'apos;// check to see if past COA looking for; if so, stop searching
			'apos;// and flag COANOTOFUND
			if StrComp(sRowDate, sAcctCat) &amp;'apos;gt; 0 then					'apos;// mod060820
				exit for											'apos;// mod060820
			endif													'apos;// mod060820
			
			
			GoTo AdvanceRow
		endif	'apos;// end no match conditional

		bCOAMatched = true
		lRowIndex = il
		
		'apos;// Check next rows to be within INSBIAS on this account number
		for j = 1 to INSBIAS
			oCellDate = poSheet.getCellByPosition(COLDATE, il+j)
			sScanDate = oCellDate.String
			if Len(sScanDate) = COALEN then		'apos;// found another account
				lRowIndex = -2		'apos;// regardless of account, not enough room for insert
				exit For
			endif
		next j		'apos;// end look-ahead loop

		'apos;// check if enough room to insert
		if lRowIndex &amp;'apos;gt;= 0 then
			lRowIndex = il + INSBIAS			'apos;// have insertion row index
		endif
		exit For
		'apos;//	if match exit loop with lRowIndex = index row + INSBIAS(=3)
		
AdvanceRow:	
	next il		'apos;// end loop on sheet rows

	'apos;// set return value
	GetInsRow = lRowIndex		

iBrkpt = 1	

end function 	'apos;// end GetInsRow	6/8/20
'apos;/**/

'apos;// GetMonthName.bas
'apos;//---------------------------------------------------------------
'apos;// GetMonthName - return month name given date number.
'apos;//		wmk. 5/20/20.
'apos;//---------------------------------------------------------------

function GetMonthName(psDate as String) as String

'apos;//	Usage.	sMonth = GetMonthName( sDate )
'apos;//
'apos;//		sDate = date string "quot;mm/dd/yy"quot; or compatible format
'apos;//
'apos;//	Exit.	sMonth = appropriate month from list
'apos;//			"quot;January"quot;, "quot;February"quot;, "quot;March"quot;, "quot;April"quot;, "quot;May"quot;, "quot;June"quot;
'apos;//			"quot;July"quot;, "quot;August"quot;, "quot;September"quot;, "quot;October"quot;, "quot;November"quot;, "quot;December"quot;
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/15/20		wmk.	original code
'apos;//	5/20/20.	wmk.	vars compliant with EXPLICIT
'apos;//
'apos;//	Notes. &amp;'apos;lt;Insert notes here&amp;'apos;gt;
'apos;//

'apos;//	constants. (see module-wide constants)

'apos;//	local variables.

dim nMonth as integer	'apos;// number of month
dim sName as String		'apos;// returned name of month

	'apos;// code.

	sName = "quot;"quot;	'apos;// default to empty string
	nMonth = Month(DateValue(psDate))
	
	Select Case nMonth
	Case 1
		sName = MON1
	Case 2
		sName = MON2
	Case 3
		sName = MON3
	Case 4
		sName = MON4
	Case 5
		sName = MON5
	Case 6
		sName = MON6
	Case 7
		sName = MON7
	Case 8
		sName = MON8
	Case 9
		sName = MON9
	Case 10
		sName = MON10
	Case 11
		sName = MON11
	Case 12
		sName = MON12

	end Select	'apos;// end month number case
	
	GetMonthName = sName	'apos;// set return value
	
'apos;//--------------Debugging
'apos;msgBox( "quot;In GetMonthName, returning month: "quot;+sName)
'apos;//-----------end Debugging
	
end function 	'apos;// end GetMonthName 
'apos;/**/

'apos;// GetTransSheetName.bas
'apos;//---------------------------------------------------------------
'apos;// GetTransSheetName - Get sheet name where transaction belongs.
'apos;//		wmk. 5/20/20.
'apos;//---------------------------------------------------------------

function GetTransSheetName( sAcct as String ) as String

'apos;//	Usage.	sSheetName = GetTransSheetName( sAcct )
'apos;//
'apos;//		sAcct = account number for transaction
'apos;//				1xxx - Asset account number
'apos;//				2xxx - Liability account number
'apos;//				3xxx - Income account number
'apos;//				4xxx - Expenses account number
'apos;//				5xxx - Expenses account number	(non-deductible)
'apos;//				9xxx - Expenses account number	(uncategorized)
'apos;//
'apos;//	Exit.	sSheetName = &amp;'apos;lt;target&amp;'apos;gt; = &amp;'apos;lt;result&amp;'apos;gt;
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/15/20		wmk.	original code
'apos;//	5/20/20.	wmk.	var dims to comply with EXPLICIT
'apos;//
'apos;//	Notes. &amp;'apos;lt;Insert notes here&amp;'apos;gt;
'apos;//

'apos;//	constants. (see module-wide constants)

'apos;//	local variables.

dim sName	as String	'apos;// returned string
dim sDigit 	as String	'apos;// extracted 1st digit

	'apos;// code.
	
	sName = "quot;"quot;					'apos;// default is empty string
	sDigit = Left(sAcct, 1)		'apos;// trigger on leftmost digit
	Select Case sDigit
	
		Case "quot;1"quot;
			sName = ASTSHEET
		Case "quot;2"quot;
			sName = LIASHEET
		Case "quot;3"quot;
			sName = INCSHEET
		Case "quot;4"quot;
			sName = EXPSHEET
		Case "quot;5"quot;
			sName = EXPSHEET
		Case "quot;9"quot;
			sName = EXPSHEET
			
	end Select	'apos;// end 1st digit case statement

'apos;// Note: the Switch statement must account for all values of sDigit or else
'apos;// a runtime error occurs... when "quot;6"quot; was passed, function failed.
'apos;	sName = Switch( sDigit="quot;1"quot;,ASTSHEET, sDigit="quot;2"quot;,LIASHEET, sDigit="quot;3"quot;,INCSHEET,_
'apos;				sDigit="quot;4"quot;,EXPSHEET, sDigit="quot;5"quot;,EXPSHEET, sDigit="quot;9"quot;,EXPSHEET)		

	GetTransSheetName = sName					'apos;// return name string

'apos;//--------------Debugging
'apos;msgBox( "quot;In GetTransSheetName, returning sheet name: "quot;+sName)
'apos;//-----------end Debugging

end function 	'apos;// end GetTransSheetName
'apos;/**/

'apos;// LogError.bas
'apos;//---------------------------------------------------------------
'apos;// LogError - Make error log entry to ERRLOGSHEET.
'apos;//		wmk. 6/2/20.
'apos;//---------------------------------------------------------------

public sub LogError(psErrName, psErrMsg)

'apos;//	Usage.	macro call or
'apos;//			call LogError(sErrName, sErrMsg)
'apos;//
'apos;//		sErrName = error code name (e.g. "quot;ERRBADVALS"quot;)
'apos;//		sErrMsg = error message string
'apos;//
'apos;// Entry.	gbErrDisplay = true if msgBox to be displayed
'apos;//						   false otherwise
'apos;//			gbErrRecording = true if to write ERRLOGSHEET entry
'apos;//							 false otherwise
'apos;//
'apos;//	Exit.	gsErrName = psErrName
'apos;//			gsErrMsg = psErrMsg
'apos;//
'apos;// Calls.	ErrLogGetCellInfo, ErrLogGetModule, ErrLogMakeEntry
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/26/20.	wmk.	original code; issue message in box; set
'apos;//						up call to ErrMakeEntry
'apos;//	6/2/20.		wmk.	added caller module name to msgBox	
'apos;//
'apos;//	Notes. &amp;'apos;lt;Insert notes here&amp;'apos;gt;
'apos;//

'apos;//	constants.

'apos;//	local variables.
dim lErrCol As Long			'apos;// error column index
dim lErrRow As Long			'apos;// error row index
dim lErrSheet as Integer	'apos;// error sheet index
dim sErrCol As String	'apos;// string conversions
dim sErrRow As String
dim sErrSheet As String
dim iStatus As Integer		'apos;// recording status

	'apos;// code.
	gsErrName = psErrName
	gsErrMsg =  psErrMsg
	ErrLogGetCellInfo(lErrCol, lErrRow)		'apos;// extract column and row information
	lErrSheet = ErrLogGetSheet()
	sErrSheet = Str(lErrSheet)
	sErrCol = Str(lErrCol)
	sErrRow = Str(lErrRow)	

	if gbErrDisplay then
		msgBox("quot;In LogError, Called by 'apos;"quot; + ErrLogGetModule() + CHR(13)+CHR(10)_
			+ "quot;Error Name: "quot;+psErrName+CHR(13)+CHR(10)_
			+ "quot;Sheet index: "quot;+sErrSheet+CHR(13)+CHR(10)_
			+ "quot;At cell index: "quot;+sErrCol+"quot;:"quot;+sErrRow+CHR(13)+CHR(10)_
			+psErrMsg)
	endif	'apos;// end display message conditional
	
	if gbErrRecording then
		iStatus = ErrLogMakeEntry()
	endif	'apos;// end log recording active conditional
	
			
end sub		'apos;// end LogError	6/2/20
'apos;/**/

'apos;// PlaceSplitTotal.bas
'apos;//------------------------------------------------------------------
'apos;// PlaceSplitTotal - Itemize split transaction in total COA account.
'apos;//		7/8/20.	wmk.	15:00
'apos;//------------------------------------------------------------------

public function PlaceSplitTotal(poGLRange As Object, psAcct As String) As Integer

'apos;//	Usage.	iVal = PlaceSplitTotal(oGLRange, sAcct)
'apos;//
'apos;//		oGLRange = RangeAddress information for GL sheet
'apos;//		sAcct = Total COA account from which items split
'apos;//
'apos;// Entry.	oGLRange.Sheet = index of ledger sheet
'apos;//					.StartColumn = index of starting column
'apos;//					.StartRow = index of first "quot;split"quot; row
'apos;//					.EndColumn = index of ending column
'apos;//					.EndRow = index of last "quot;split"quot; row
'apos;//
'apos;//	Exit.	iVal = 0 - normal return
'apos;//				   ERRCONTINUE - nonfatal error; continue processing
'apos;//				   ERRSTOP - fatal error; stop processing transactions
'apos;//
'apos;// Calls.	GetMonthName, GetTransSheetName, GetInsRow, SetSumFormula
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/4/20.		wmk.	original code
'apos;//	6/6/20.		wmk.
'apos;//	6/27/20.	wmk.	superfluous "quot;if"quot; removed
'apos;//	7/8/20.		wmk.	code to remove extra row in source COA transaction	
'apos;//
'apos;//	Notes.
'apos;// This function adds lines to the splitting account category
'apos;// then copies lines from the original "quot;split"quot; transaction
'apos;// except for the Total transaction line; then moves the
'apos;// nonempty Debit or Credit range into the opposing column
'apos;// and ensures that the SUM formula in the last "quot;split"quot; row
'apos;// is corrected to sum the moved data.
'apos;//

'apos;//	constants.
const FORMULA=3		'apos;// FORMULA type on cell

'apos;//	local variables.
dim iRetValue As Integer		'apos;// returned value
dim oDoc As Object				'apos;// ThisComponent
dim oSheets As Object			'apos;// Doc.getSheets()
dim iSheetIx As Integer			'apos;// sheet index of ledger
dim oGLSheet As Object			'apos;// ledger sheet object
dim sSplitAcct As String		'apos;// COA of account splitting from
dim iBrkPt As Integer			'apos;// easy breakpoint

'apos;//	GL transaction variables.
dim oGLCellDate	As Object		'apos;// Date cell from GL
dim sStyle	As String			'apos;// cell style
dim sDate	As String			'apos;// date field string
dim sMonth	As String			'apos;// month name of date (e.g. "quot;January"quot;)
dim l1stRow	As Long				'apos;// first "quot;split"quot; row
dim lLastRow As Long			'apos;// last "quot;split"quot; row
dim lRowCount	As Long			'apos;// transaction row count between "quot;split"quot;s

'apos;//	COA category sheet variables.
dim sAcctCat2 As String			'apos;// COA account category total transaction
dim oCat2Sheet As Object		'apos;// COA account sheet for total
dim lCat2InsRow	As Long			'apos;// COA sheet insertion row
dim oDebitCell as Object
dim bMoveDebits as Boolean
dim oFormulaCell As Object
dim sFormula As String
dim sFormStart as String
dim lCat2CurrRow as Long	'apos;// insertion sheet current row
dim li as Long				'apos;// long loop counter
dim oDateCell as Object		'apos;// Date cell object

	'apos;// code.
	sSplitAcct = psAcct			'apos;// copy COA account string
	
	iRetValue = 0
	
	PlaceSplitTotal = iRetValue
	
'apos;	if true then
'apos;		Exit Function
'apos;	endif

'apos;//---------------begin PlaceSplitTotal here------------------------
'apos;//	poGLSheet is the ledger sheet with the whole transaction
'apos;// sSplitAcct is the account that has the total COA
		'apos;//==========================================================
'apos;//-----------------error handling setup-----------------------------
dim sErrCallerMod 	As String	'apos;// caller routine name
dim iErrSheetIx		As Integer	'apos;// caller sheet index
dim lErrColumn		As Long		'apos;// caller error focus column
dim lErrRow			As Long		'apos;// caller error focus row
dim sMyName			As String	'apos;// this routine name
	'apos;//*-----------------preserve error handling for caller-----------
	'apos;// preserve caller error settings.
	sErrCallerMod = ErrLogGetModule()
	iErrSheetIx = ErrLogGetSheet()
	ErrLogGetCellInfo(lErrColumn, lErrRow)
	'apos;// set up new error settings.
	sMyName = "quot;PlaceSplitTotal"quot;
	ErrLogSetModule(sMyName)
	ErrLogSetSheet(poGLRange.Sheet)
	ErrLogSetCellInfo(poGLRange.StartColumn, poGLRange.StartRow)
'apos;//*----------end error handling setup-------------------------------

		'apos;//	insertCells...
'apos;// entry.	sSplitAcct is the COA from the total line entry
'apos;//         oGLCellDate.Text.String is the Date field from the totals
'apos;//			 line entry
'apos;//			oSheets object is the Sheets[] array this component

	oDoc = ThisComponent			'apos;// set component and access objects
	oSheets = oDoc.getSheets()
	iSheetIx = poGLRange.Sheet
	oGLSheet = oSheets.getByIndex(iSheetIx)
	
	'apos;// extract date information from ledger entry
	l1stRow = poGLRange.StartRow	'apos;// first row of "quot;split"quot;
	lLastRow = poGLRange.EndRow		'apos;// last row of "quot;split"quot;
	lRowCount = poGLRange.EndRow - l1stRow - 1			'apos;// transaction lines with COAs
	oGLCellDate = oGLSheet.getCellByPosition(COLDATE, l1stRow)

	'apos;// set up parameters for GetTransSheetName and GetInsRow
	sStyle = oGLCellDate.Text.String
	sDate = oGLCellDate.Text.String
	sMonth = GetMonthName(sDate)		'apos;// get month search name
	sAcctCat2 = GetTransSheetName(sSplitAcct)	'apos;// get sheet name matching COA

	'apos;// access target COA sheet and set up insertion row
	oCat2Sheet = oSheets.getByName(sAcctCat2)
iBrkpt=1		'apos;// oCat2Sheet.RangeAddress.Sheet = sheet index
ErrLogSetSheet(oCat2Sheet.RangeAddress.Sheet)	'apos;// set sheet for errors
	lCat2InsRow = GetInsRow(oCat2Sheet, sSplitAcct, sMonth)

	if lCat2InsRow &amp;'apos;lt; 0 then
		sErrName = "quot;ERRCOAINSERT"quot;
		sErrMsg = "quot;Unable to insert Total rows in split account"quot;
		GoTo ErrHandler
	endif
		
	
Dim oCOARange As New com.sun.star.table.CellRangeAddress

	'apos;// set up oCOARange object
	'apos;// copy entire transaction just like if doing it manually
	'apos;// move filled values to other column (Debit &amp;'apos;gt; Credit) or
	'apos;//		(Credit to Debit)
	'apos;// delete total row from transaction
	'apos;// set sum formula in COA field of last "quot;split"quot; row
	
	'apos;// at this point l1stRow = 1st row with "quot;split"quot;
	'apos;//               lLastRow = last row with "quot;split
	'apos;//				  set lStartColumn = COLDATE
	'apos;//				  set lEndColumn = COLREF
	'apos;//				  set sheet number (integer) = index of GL sheet
	'apos;//				  set target start row at lCat2InsRow
	'apos;//				  set target start column to COLDATE
	'apos;//				  set number of rows to lRowCount + 1
	'apos;// Doc = ThisComponent
	'apos;// Sheet = Doc.Sheets(0)
	'apos;//	oSel = Doc.getCurrentSelection()
	'apos;//	oRange = oSel.RangeAddress			'apos;// extract range information
iBrkPt=1		
	oCOARange.Sheet = oCat2Sheet.RangeAddress.Sheet
	oCOARange.StartColumn = COLDATE
	oCOARange.EndColumn = COLREF
	oCOARange.StartRow = lCat2InsRow	'apos;// we are inserting into the 2nd sheet
	oCOARange.EndRow = lCat2InsRow + lRowCount + 2	'apos;// start+transaction rowcount+1
ErrLogSetCellInfo(COLDATE, lCat2InsRow)
	oCat2Sheet.insertCells(oCOARange,_
							com.sun.star.sheet.CellInsertMode.ROWS)
dim lSplitEnd As Long		'apos;// save split end in target sheet
	lSplitEnd = oCOARange.EndRow
		
	'apos;// now copy original transaction split range address to same range
	'apos;// overwriting blank rows;
dim oTargetCellAddress As new com.sun.star.table.CellAddress
	oTargetCellAddress.Sheet = oCOARange.Sheet
	oTargetCellAddress.Column = COLDATE
	oTargetCellAddress.Row = lCat2InsRow
		
dim oGLCellRange As new com.sun.star.table.CellRangeAddress
	oGLCellRange.Sheet = poGLRange.Sheet
	oGLCellRange.StartColumn = COLDATE
	oGLCellRange.EndColumn = COLREF
	oGLCellRange.StartRow = l1stRow
	oGLCellRange.EndRow = lLastRow

'apos;// ErrLogGetSheet - Get sheet index from error log globals.
ErrLogSetSheet(oTargetCellAddress.Sheet)
ErrLogSetCellInfo(oTargetCellAddress.Column, oTargetCellAddress.Row)

	'apos;// copy the entire split transaction to the COA sheet
	'apos;// then delete the total row;
	oGLSheet.copyRange(oTargetCellAddress, oGLCellRange)
		
	'apos;// use removeCells to remove Total row
	'apos;// uses same parameters as insertCells; only change Row range
	oCOARange.Sheet = oTargetCellAddress.Sheet	
	oCOARange.StartRow = lCat2InsRow + 1	'apos;// total row
	oCOARange.EndRow = oCOARange.StartRow
	
ErrLogSetSheet(oCat2Sheet.RangeAddress.Sheet)
ErrLogSetCellInfo(COLDATE, oCOARange.EndRow)
	
	oCat2Sheet.removeRange(oCOARange,_
						com.sun.star.sheet.CellDeleteMode.ROWS)
	lSplitEnd = lSplitEnd - 1		'apos;// adjust split end for removed row
		
iBrkPt=1
							
	'apos;// then move the Debit/Credit values to the other column
	'apos;// change background color of colored cells to LTGREEN
	'apos;// test which column is .Type empty cells
	'apos;// then move the Debit/Credit values to the empty column
	oDebitCell = oCat2Sheet.getCellByPosition(COLDEBIT,_
					oCOARange.StartRow)
	oTargetCellAddress.Row = oDebitCell.CellAddress.Row
	oCOARange.EndRow = lSplitEnd - 2	'apos;// 2 back; no Total row and not end "quot;split"quot;

	bMoveDebits = (oDebitCell.Type &amp;'apos;lt;&amp;'apos;gt; EMPTY)
		'apos;	EMPTY,VALUE,TEXT,FORMULA

	'apos;// if there is something in the Debits column; the Debits move to Credits		
	if bMoveDebits then	'apos;// set range to debits column
		oTargetCellAddress.Column = COLCREDIT
		oCOARange.StartColumn = COLDEBIT
		oCOARange.EndColumn = COLDEBIT
	else				'apos;// otherwise, the Credits move to Debits
		oTargetCellAddress.Column = COLDEBIT
		oCOARange.StartColumn = COLCREDIT
		oCOARange.EndColumn = COLCREDIT
	endif	'apos;// end move Debits conditional

'apos;// ErrLogGetSheet - Get sheet index from error log globals.
'apos;// ErrLogSetSheet - Set sheet index in error log globals.
ErrLogSetCellInfo(oTargetCellAddress.Column, oTargetCellAddress.Row)

	'apos;// move the column containing values to opposing column
	oCat2Sheet.moveRange(oTargetCellAddress, oCOARange)

		
	'apos;// set .formula on COLACCT of last line to
	'apos;//	 "quot;=SUM()"quot; previous cells above in either Debit or Credit

ErrLogSetCellInfo(COLACCT, lSplitEnd-1)

	'apos;// row is now lSplitEnd-1 since Total row deleted	
	oFormulaCell = oCat2Sheet.getCellByPosition(COLACCT, lSplitEnd-1)
		
	'apos;// Always force SUM formula into last row COLACCT field
'apos;	if oFormulaCell.Type = FORMULA then

	'apos;// set up RangeAddress object for SetSumFormula
dim oNewSplitRange as new com.sun.star.table.CellRangeAddress
	oNewSplitRange.Sheet = oCat2Sheet.RangeAddress.Sheet
	oNewSplitRange.StartRow = lCat2InsRow		'apos;// first "quot;split"quot; row
	oNewSplitRange.EndRow = lSplitEnd-1			'apos;// last "quot;split"quot; row (minus 1, row deleted)
	oNewSplitRange.StartColumn = COLDATE		'apos;// Date column
	oNewSplitRange.EndColumn = COLREF			'apos;// Reference column
	sFormula = oFormulaCell.String			'apos;// just for kicks

ErrLogSetSheet(oCat2Sheet.RangeAddress.Sheet)
ErrLogSetCellInfo(COLACCT, oNewSplitRange.EndRow)
			
	sFormula = SetSumFormula(oNewSplitRange, bMoveDebits)		
	oFormulaCell.String = sFormula			'apos;// set formula in cell
	oFormulaCell.setFormula(sFormula)
	'apos;// loop changing Date fields'apos; color to LTGREEN
ErrLogSetSheet(oCat2Sheet.RangeAddress.Sheet)
ErrLogSetCellInfo(COLDATE, oNewSplitRange.StartRow)
	lRowCount = oNewSplitRange.EndRow - oNewSplitRange.StartRow + 1
	lCat2CurrRow = oNewSplitRange.StartRow
	for li = 0 to lRowCount-1
		oDateCell = oCat2Sheet.getCellByPosition(COLDATE, lCat2CurrRow)
		oDateCell.CellBackColor = LTGREEN
		lCat2CurrRow = lCat2CurrRow + 1	
	next li					

'apos;// add code to remove superfluous row at bottom
	'apos;// row to remove is at lSplitEnd								'apos;// mod070820
	'apos;// use removeCells to remove extraneous						'apos;// mod070820
	'apos;// uses same parameters as insertCells; only change Row range	'apos;// mod070820
	oCOARange.Sheet = oTargetCellAddress.Sheet						'apos;// mod070820
	oCOARange.StartRow = lSplitEnd	'apos;// blank row					'apos;// mod070820
	oCOARange.EndRow = lSplitEnd									'apos;// mod070820
	
ErrLogSetSheet(oCat2Sheet.RangeAddress.Sheet)						'apos;// mod070820
ErrLogSetCellInfo(COLDATE, lSplitEnd)								'apos;// mod070820	
	oCat2Sheet.removeRange(oCOARange,_
						com.sun.star.sheet.CellDeleteMode.ROWS) 		'apos;// mod070820

		'apos;// update sheet modified date cell							'apos;// mod060620
		oCOARange.Sheet = oCat2Sheet.RangeAddress.Sheet				'apos;// mod060620
		Call SetSheetDate(oCOARange, MMDDYY)						'apos;// mod060620
		oDateCell = oCat2Sheet.getCellByPosition(COLDEBIT, DATEROW)	'apos;// mod060620
		oDateCell.CellBackColor = LTGREEN							'apos;// mod060620

	GoTo NormalExit

ErrHandler:
	'apos;// here because insertion row returned &amp;'apos;lt;0; unable to insert rows
	'apos;// iStatus is bad value returned
	iRetValue = iStatus
	Select Case iStatus
	Case ERRCOANOTFOUND
		sErrCode = csERRCOANOTFOUND
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot; not found"quot;
	Case ERROUTOFROOM
		sErrCode = csERROUTOFROOM
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot; month "quot;+sMonth+"quot; not enough rows"quot;_
					+"quot; to insert"quot;
	Case ERRCOACHANGED
		sErrCode = csCOACHANGED
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot; month "quot;+sMonth+"quot; not found"quot;
	Case else
		sErrCode = csERRUNKNOWN
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot;Undocumented error"quot;
	end Select

	Call LogError(sErrCode, sErrMsg)			
	
'apos;//-----------------end PlaceSplitTotal here----------------------------
NormalExit:
	'apos;//*-----------------restore error handling for caller-----------
	'apos;// restore entry error settings.
	ErrLogSetModule(sErrCallerMod)
	ErrLogSetSheet(iErrSheetIx)
	ErrLogSetCellInfo(lErrColumn, lErrRow)
	'apos;//*----------end error handling restore----------------------------

	'apos;// iStatus = 0, no error
	'apos;//				ERRCONTINUE (-1) - error; continue with next transaction
	'apos;//				ERRSTOP (-2) - error; stop processing transactions

	PlaceSplitTotal = iRetValue

end Function		'apos;// end PlaceSplitTotal		7/8/20
'apos;/**/

'apos;// PlaceSplitTrans.bas
'apos;//------------------------------------------------------------------
'apos;// PlaceSplitTrans - Place split transaction into multiple accounts.
'apos;//		7/8/20.	wmk.	07:00
'apos;//-------------------------------------------------------------------

function PlaceSplitTrans( poGLSheet as Object, plSplit1stRow as Long)_
	as long

'apos;//	Usage.	lSplitEndRow = PlaceSplitTrans(oGLSheet, lSplit1stRow)
'apos;//			call PlaceSplitTrans( oGLSheet, lSplit1stRow )
'apos;//
'apos;//		oGLSheet = GL sheet object
'apos;//		lSplit1stRow = row index of first row of split transaction
'apos;//
'apos;// Entry.	GL sheet processing has found row with "quot;split"quot; as the
'apos;//			.Text.String value in COLSPLIT. The split entry contains
'apos;//			multiple rows until another row is found with "quot;split"quot; as the
'apos;//			.Text.String value in COLSPLIT. The second row of the split
'apos;//			transaction MUST contain the COA target and the total amount
'apos;//			of the split, regardless of whether it is a Debit or Credit.
'apos;//			Subsequent rows contain the amounts split into the opposing
'apos;//			COAs, and their value sums MUST equal the COA target amount.
'apos;//
'apos;//	Exit.	lSplitEndRow &amp;'apos;gt;=0 - row index of GL last row of split
'apos;//						 &amp;'apos;lt;0 - error, caller should terminate processing`
'apos;//			ErrLogDisable() called, so caller will have to re-enable
'apos;//			caller error logging
'apos;//
'apos;//	Calls.	CheckSplitTrans, GetTransSheetName, GetInsRow, GetMonthName
'apos;//			ErrLogSetup, LogError, ErrLogDisable
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/22/20		wmk.	original code
'apos;//	5/23/20		wmk.	code added to put lines in split accounts; bug
'apos;//						fixes; fix splits loop count; changes inserted
'apos;//						rows Date field color to LTGREEN
'apos;//	5/24/20.	wmk.	start adding code to copy rows into COA total;
'apos;//						add insert error code checking and ErrorHandler
'apos;//	5/26/20.	wmk.	error handling modified; error logging code added
'apos;// 5/27/20.	wmk.	bug fixed where COA error in GL sheet being reported
'apos;//						as in accounting category sheet; error code string
'apos;//						ERRCOANOTFOUND corrected in constants fix bug where
'apos;//						last split row with "quot;split"quot; not being copied into
'apos;//						account where other accounts split from
'apos;//	5/28/20.	wmk.	bug fix where debit/credit column swap copying 1
'apos;//						too many rows; SUM formula insertion fix for COA
'apos;//						category sheet entry of split details; feature implemented
'apos;//						where split block inserted in sheet gets LTGREEN Date
'apos;//						cell background color
'apos;//	6/3/20.		wmk.	add code to change source transaction REF fields
'apos;//						cell background color to LTGREEN between "quot;split"quot;
'apos;//						rows; dead code block removed
'apos;//	6/4/20.		wmk.	modularize code; add PlaceSplitTotal function;
'apos;//						dead code and unused vars removed
'apos;//	6/5/20.		wmk.	adjust code for SplitOutCOAs call; dead code
'apos;//						removed; additional error codes from SplitTrans
'apos;//	7/8/20.		wmk.	debugging superfluous msgbox removed
'apos;//
'apos;//	Notes.
'apos;//	Any return value &amp;'apos;lt; 0 should be treated as a termination error,
'apos;// and the caller should stop processing rows, as likely there will
'apos;// only be errors in the subsequent rows until the user corrects
'apos;// the GL spreadsheet entries.
'apos;// Feature; 5/17/20; transfer "quot;split"quot; transaction to appropriate
'apos;// multiple accounts (generalized add code to process split
'apos;// transactions; caveat: all split transactions should have total in
'apos;// first line with COA that will be used in all other line items as
'apos;// moved to ease cross-referencing, regardless of whether total is a
'apos;// debit or credit.
'apos;//	Method.
'apos;//	Stolen code from PlaceTransM. Only use oCat2Sheet, since only 
'apos;// dealing with one target account sheet at a time. oGLSheet contains
'apos;// the original "quot;split"quot; transaction lines. oCat2Sheet is the
'apos;// category account sheet that gets each inserted line.

'apos;//	constants. (see module-wide definitions, and add these)

'apos;// local contants.

'apos;// split controls and error codes.
const MAXSPLITROWS=13	'apos;// maximum rows in "quot;split"quot; to "quot;split"quot;; 10 subaccounts
const ERRNOTSPLIT=-1	'apos;// first fow not "quot;split"quot; in COLSPLIT
const ERRBADTALLY=-2	'apos;// split values don'apos;t add to total
const ERRROWSEXCEEDED=-3	'apos;// too many rows bedore second "quot;split"quot; found
const ERRBADVALS=-4		'apos;// bad values, either missing or don'apos;t add
const ERRBADDATE=-5		'apos;// Date field too short
const ERRDATENOTSAME=-6	'apos;// Date field doesn'apos;t match others			'apos;// mod060520
const ERRSPLITDESC=-7	'apos;// "quot;split"quot; descriptions don'apos;t match		'apos;// mod060520

'apos;// insert error codes.
const ERRCOANOTFOUND=-1
const ERROUTOFROOM=-2
const ERRCOACHANGED=-3

'apos;//------------------begin error handling block---------------
'apos;// LogError setup snippet.

'apos;// split error code strings.
const csERRUNKNOWN="quot;ERRUNKNOWN"quot;
const csERRNOTSPLIT="quot;ERRNOTSPLIT"quot;
const csERRBADTALLY="quot;ERRBADTALLY"quot;
const csERRROWSEXCEEDED="quot;ERRROWSEXCEEDED"quot;
const csERRBADVALS="quot;ERRBADVALS"quot;
const csERRBADDATE="quot;ERRBADDATE"quot;

'apos;// insert error codes.
const csERRCOANOTFOUND="quot;ERRCOANOTFOUND"quot;
const csERROUTOFROOM="quot;ERROUTOFROOM"quot;
const csERRCOACHANGED="quot;ERRCOACHANGED"quot;

'apos;//------------------end error handling block---------------

'apos;//	local variables.

Dim Doc As Object			'apos;// This component
Dim oSheets as Object		'apos;// Sheets() collection

'apos;//	GL XCell information objects (original line)
dim oGLCellDate as Object
dim oGLCellTrans as Object
dim oGLCellDebit as Object
dim oGLCellCredit as Object
dim oGLCellAcct as Object
dim oGLCellRef as Object

'apos;//	oCat2Sheet XCell information objects.
dim oCat2Date as object		'apos;// date of transaction
dim oCat2Trans as object	'apos;// transaction description
dim oCat2Debit as object	'apos;// debit amount
dim oCat2Credit as object	'apos;// credit amount
dim oCat2Acct as object		'apos;// COA account number
dim oCat2Ref as object		'apos;// reference info

'apos;// GL field data extraction objects (.Text or .Value) and strings
dim sStyle as String		'apos;// Text.Style string

'apos;// Date field variables for both GL objects.
dim sDate as String
dim lDateFormat as long		'apos;// date format mm/dd/yy
dim sMonth as String

'apos;//	CellRange content variables for GL sheet.
dim lGLStartRow as long
dim lGLEndCol as long
dim lGLCurrRow as long		'apos;// current row in GL

'apos;// more local vars.
dim iStatus as integer		'apos;// general status return var
dim sAcctCat2 as String		'apos;// account category of 2nd line of transaction
dim oCat2Sheet as Object	'apos;// account sheet object of 2nd category in transaction
dim lCat2InsRow as long		'apos;// insert index for 2nd cateory in transaction

'apos;// row processing variables; use to process selected area
dim i as long				'apos;// loop counter
dim li as Long				'apos;// long loop counter
dim sBadCOA2 as String		'apos;// bad acct # in 2nd line
dim sCOA2Msg	as String	'apos;// bad 2nd line message

'apos;// other local vars.
dim iBrkPt as integer		'apos;// easy breakpoint reference
dim lRetValue as long		'apos;// returned value
dim l1stRow as Long			'apos;// 1st row defining "quot;split"quot;
dim sSplitAcct as String	'apos;// account splitting into
dim lLastRow as Long		'apos;// last line of "quot;split"quot; block
dim sAcct2 as String		'apos;// COA target account
dim lTrans1stRow as Long	'apos;// first row after "quot;split"quot;
dim lRowCount as Long		'apos;// transaction row count

'apos;// error handling setup------------------------------------------------
const sMyName="quot;PlaceSplitTrans"quot;
dim sErrCode as String	'apos;// error code string for user code
Dim sErrMsg As String	'apos;// error message for LogError
dim lLogRow as long		'apos;// cell row working on
dim lLogCol as Long		'apos;// cell column working on
dim iLogSheetIx as Integer	'apos;// sheet index module working on
dim oLogRange as new com.sun.star.table.CellRangeAddress

	'apos;// code.

'apos;// error handling initialization---------------------------------------
	sErrCode = "quot;"quot;
	iLogSheetIx = poGLSheet.RangeAddress.Sheet
	lLogCol = poGLSheet.RangeAddress.StartColumn
	lLogRow = poGLSheet.RangeAddress.StartRow
	oLogRange.Sheet = iLogSheetIx
	oLogRange.StartColumn = lLogCol
	oLogRange.StartRow = lLogRow
	oLogRange.EndRow = lLogRow
	oLogRange.EndColumn = lLogCol
	Call ErrLogSetup(oLogRange, sMyName)
	ErrLogSetRecording(true)		'apos;// turn on log sheet
'apos;//--------------end error handling setup-------------------------------

	lRetValue = ERRNOTSPLIT	'apos;// set bad return
	Doc = ThisComponent				'apos;// set up for sheet access within document
	oSheets = Doc.getSheets()			'apos;// get sheet collection

	lGLStartRow = plSplit1stRow	'apos;// one of these is superfluous?
	l1stRow = plSplit1stRow		'apos;// set local ptr to 1st "quot;split"quot; row
	lTrans1stRow = l1stRow + 1	'apos;// first actual transaction data row

	'apos;// CheckSplitTrans validates and returns last "quot;split"quot; row
	lRetValue = CheckSplitTrans(poGlSheet, l1stRow)
	
	if lRetValue &amp;'apos;lt; 0 then		'apos;// if invalid split, get out
		GoTo BailOut			'apos;// CheckSplitTrans handled error
	else
		lLastRow = lRetValue	'apos;// set last row of split block
	endif

	'apos;// l1stRow = first row of "quot;split"quot;
	'apos;// lLastRow = last row of "quot;split"quot;
	'apos;// get "quot;total"quot; account COA and preserve as split "quot;from"quot; account
	lGLCurrRow = l1stRow + 1			'apos;// move back to total line to split lines
	oGLCellAcct = poGLSheet.getCellByPosition(COLACCT, lGLCurrRow)
ErrLogSetCellInfo(COLACCT, lGLCurrRow)
	sSplitAcct = oGLCellAcct.Text.String			'apos;// set total COA splitting

'apos;// set up Range object for SplitOutCOAs and PlaceSplitTotal calls.
dim oGLRange As new com.sun.star.table.CellRangeAddress

'apos;	if true then			'apos;// jump around new code 1st pass
'apos;		GoTo SplitTrans
'apos;	endif
	
'apos;// set RangeAddress object pointing to the "quot;split"quot; transaction
'apos;// in the ledger sheet
'apos;//	iStatus = SplitOutCOAs(oGLRange, sSplitAcct)
'apos;//		oGLRange specifies sheet, and row indexes of the
'apos;//         entire split transaction
'apos;//		sSplitAcct = COA of Total account for xrefs
	oGLRange.Sheet = poGLSheet.RangeAddress.Sheet
	oGLRange.StartColumn = COLDATE
	oGLRange.EndColumn = COLREF
	oGLRange.StartRow = l1stRow
	oGLRange.EndRow = lLastRow
	lRetValue = SplitOutCOAs(oGLRange, sSplitAcct)

	if lRetValue &amp;'apos;lt; 0 then
		lCat2InsRow = lRetValue
		GoTo ErrHandler
	endif
GoTo SplitTotal
	
SplitTrans:

SplitTotal:
iBrkPt=1

'apos;//	setup code for PlaceSplitTotal
'apos;// oGLRange = RangeAddress information from poGLSheet
'apos;// sSplitAcct = COA for transaction total
		oGLRange.Sheet = poGLSheet.RangeAddress.Sheet
		oGLRange.StartColumn = COLDATE
		oGLRange.EndColumn = COLREF
		oGLRange.StartRow = l1stRow
		oGLRange.EndRow = lLastRow
		iStatus = PlaceSplitTotal(oGLRange, sSplitAcct)

		if iStatus &amp;'apos;lt; 0 then
			'apos;// iStatus should be set to negative insert row
			lCat2InsRow = iStatus
			GoTo ErrHandler
		endif	'apos;// end error splitting total conditional


'apos;//	change Ref field background to LTGREEN for all split
'apos;//	rows between "quot;split"quot;s in source transaction
	lGLCurrRow = l1stRow + 1			'apos;// start at Total row
	lRowCount = lLastRow - l1stRow -1	'apos;// row count of lines between "quot;split"quot;
	for li = 0 to lRowCount-1			'apos;// minus 1, since indexed on 0
		oGLCellRef = poGLSheet.getCellByPosition(COLREF, lGLCurrRow)
		oGLCellRef.CellBackColor = LTGREEN
		lGLCurrRow = lGLCurrRow + 1		'apos;// advance to next transaction row
	next li			'apos;// next transaction line
	
	lRetValue = lLastRow		'apos;// ensure return last "quot;split"quot; row	
	GoTo ExitNormal		'apos;// jump around error handler
			

ErrHandler:
	'apos;// issue appropriate message and don'apos;t mark rows as processed
	sBadCOA2 = sAcct2
	sCOA2Msg = "quot;"quot;			'apos;// clear line 2 message
	Select Case lCat2InsRow
	Case ERRCOANOTFOUND
		sErrCode = csERRCOANOTFOUND
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot; not found"quot;
	Case ERROUTOFROOM
		sErrCode = csERROUTOFROOM
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot; month "quot;+sMonth+"quot; not enough rows"quot;_
					+"quot; to insert"quot;
	Case ERRCOACHANGED
		sErrCode = csCOACHANGED
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot; month "quot;+sMonth+"quot; not found"quot;
		
	Case ERRDATENOTSAME	'apos;// Date field doesn'apos;t match others			'apos;// mod060520
const csDATENOTSAME="quot;ERRDATESNOTSAME"quot;
		sErrCode = csDATENOTSAME
		sErrMsg = "quot;Split - one or more dates don'apos;t match"quot;
		
	Case ERRSPLITDESC	'apos;// "quot;split"quot; descriptions don'apos;t match		'apos;// mod060520
const csSPLITDESC="quot;ERRSPLITDESC"quot;
		sErrCode = csSPLITDESC
		sErrMsg = "quot;Split - 1st and last split descriptions don'apos;t match"quot;
		
	Case else
		sErrCode = csERRUNKNOWN
		sErrMsg = "quot;Account "quot;+sBadCOA2+"quot;Undocumented error"quot;
	end Select

	Call LogError(sErrCode, sErrMsg)			
	GoTo BailOut

	'apos;// end up here if insert row &amp;'apos;lt; 0
ExitNormal:	
BailOut:
	ErrLogDisable()
	PlaceSplitTrans = lRetValue		'apos;// set return value

iBrkPt = 1
	
end function		'apos;// end PlaceSplitTrans	7/8/20
'apos;/**/

'apos;// PlaceTransM.bas
'apos;//-------------------------------------------------------------------
'apos;// PlaceTransM - place multiple transactions into appropriate sheets.
'apos;//		wmk. 6/12/20.	18:00
'apos;//-------------------------------------------------------------------

sub PlaceTransM()

'apos;//	Usage.	macro call or
'apos;//			call PlaceTransM
'apos;//
'apos;// Entry.	user has selected one or more transaction rows in ledger sheet
'apos;//			from which transactions are to be placed in appropriate
'apos;//			accounts sheet
'apos;//
'apos;//	Exit.	transaction (2 rows) is copied with each row of the transaction
'apos;//			copied to the appropriate sheet by account number, and the
'apos;//			account numbers transposed in the new entries to cross-reference
'apos;//			the corresponding double-entry account for the transaction
'apos;//
'apos;//	Calls.	GetTransSheetName, GetInsRow, GetMonthName, SetSheetDate,
'apos;//			PlaceSplitTrans
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/17/20.	wmk.	original code
'apos;//	5/18/20.	wmk.	finish original code and get functional; fix show-
'apos;//						stopping bugs
'apos;// 5/19/20.	wmk.	fixes where inserting transactions into different
'apos;//						COAs within same sheet
'apos;//	5/20/20.	wmk.	bug fixes in COA validation (5/18 bugs); var
'apos;//						declarations to match EXPLICIT option
'apos;//	5/30/20.	wmk.	bug fix with error handling setup code; bug fixes to comply
'apos;//						with OPTION EXPLICIT
'apos;//	6/1/20.		wmk.	add error code ERRBADSEL for when user selects
'apos;//						odd number of rows or selection splits across
'apos;//						a transaction; improved error checking on date
'apos;//						field
'apos;//	6/1/20.		wmk.	add process controls ERRCONTINUE and ERRSTOP;
'apos;//						modularize code to processes CheckDoubleEntry,
'apos;//						CheckInserts, StoreTrans, and SetProcessed;
'apos;//						dead code cleanup
'apos;//	6/2/20.		wmk.	CheckDoubleEntry code completed; old inline code
'apos;//						removed; LogError modified to display calling
'apos;//						module name; CheckInserts code completed; StoreTrans
'apos;//						code completed
'apos;//	6/3/20.		wmk.	Modularized code removed; error handling on last
'apos;//						line improved; eliminate extraneous leftover
'apos;//						variable declarations
'apos;//	6/6/20.		wmk.	SetSheetDate call added to set modification date
'apos;//						in any changed sheets; background LTGREEN
'apos;// 6/6/20.		wmk.	mod to add capability to process "quot;split"quot;
'apos;//						transactions by calling PlaceSplitTrans
'apos;// 6/12/20.	wmk.	code rearranged to check for "quot;split"quot; first in loop
'apos;//					 	to allow user to select only 1st row if just
'apos;//						wants to process 1 split transaction
'apos;//
'apos;//	Notes. This emulates the process of taking general ledger
'apos;// transactions (double entries) and creating new entries in
'apos;// the appropriate account sheets for each line of the transaction. 
'apos;// Each line of the transaction uses the following columns
'apos;// Column A=date, B=transaction, C=debit D=credit  E=COA account
'apos;//			 F=reference
'apos;// Method.
'apos;// (maybe write a utility just to verify transactions)
'apos;// user should "quot;select"quot; area in GL sheet with ledger entries to clone
'apos;//  to other sheets;
'apos;// const INSBIAS = 3 to set row bias from start of Month/Acct#
'apos;// convert month field from date data to capitalized month name for
'apos;// use CurrentSelection function to get General Ledger rows to push into
'apos;// appropriate category account sheet;
'apos;// function GetTransSheetName (&amp;'apos;lt;sAcct&amp;'apos;gt;) as String - get sheet name, given
'apos;// account number string
'apos;// function GetInsRow( &amp;'apos;lt;oExSheet&amp;'apos;gt;, &amp;'apos;lt;sAcct&amp;'apos;gt;, &amp;'apos;lt;sMonth&amp;'apos;gt; ) as long returning row #
'apos;//   if -1 not found; -2 if won'apos;t fit before next month or account;
'apos;//  -3 COA found, but month not found before COA changed, 
'apos;// insert new row at row returned from GetInsRow and copy row information
'apos;// from General Ledger sheet to Expenses sheet; change color of date
'apos;// field in both General Ledger and target account category sheet to a
'apos;// light green. This will help the user to avoid the mistake of
'apos;// double-updating General Ledger entries into account category sheets
'apos;// use getSheetByName() call on Sheets object to accesss category
'apos;// sheets
'apos;// search in both category sheets looks for &amp;'apos;lt;month-name&amp;'apos;gt; in the second
'apos;// field, and &amp;'apos;lt;account-number&amp;'apos;gt; in the first field, then move forward
'apos;// in rows to check if enough space for insert without affecting
'apos;// either month tallies; if not, transaction is skipped.
'apos;// new rows are marked with light green background in Date field to
'apos;// make it easy for the user to spot "quot;automated"quot; entries.
'apos;// processed transaction rows in GL sheet are marked with light green
'apos;//	background in Reference field to make it easy for user to spot
'apos;// entries that have been disseminated with "quot;automated"quot; entries in
'apos;// the account category sheets; if a transaction has been skipped
'apos;// by the automated dissemination, its Reference field is left in
'apos;// its original background color state
'apos;//----------------------------------------------------------------------
'apos;***************BUGS - * = still outstanding
'apos;// 5/18/20.	failed to flag as error and skip transaction with at least
'apos;//				one line with values in both debit and credit fields
'apos;// 5/18/20.	failed to flag as error and skip transaction with at least
'apos;//				one l line with no values in both debit and credit fields
'apos;//	5/19/20.	transaction on accounts 1010/1020 ATM withdrawal
'apos;//				skipped with "quot;duplicate accounts"quot; error
'apos;//	5/19/20.	"quot;duplicate accounts"quot; trasaction error still colored
'apos;//				reference field green
'apos;//	5/19/20.	insert into same sheet with two different COAs causes
'apos;//				insert of second COA line only 2 rows in from COA, not 3

'apos;//	constants. (see module-wide constants)
'apos;const COLDATE=0		'apos;// Date - column A
'apos;const COLTRANS=1	'apos;// Transaction - column B
'apos;const COLDEBIT=2	'apos;// Debit - column C
'apos;const COLCREDIT=3	'apos;// Credit - column D
'apos;const COLACCT=4		'apos;// COA Account - column E
'apos;const COLREF=5		'apos;// Reference - column F


'apos;// column index values for accessing information.

'apos;// cell formatting constants.

'apos;const DEC2=123		'apos;// number format for (x,xxx.yy)
'apos;const MMDDYY=37		'apos;// date format mm/dd/y
'apos;const LJUST=1		'apos;// left-justify HoriJustify
'apos;const CJUST=2		'apos;// center HoriJustify
'apos;const RJUST=0		'apos;// right-justify HoriJustify
'apos;const MAXTRANSL=50	'apos;// maximum transaction text length

'apos;// process control constants.
const ERRCONTINUE=-1	'apos;// error, but continue with next transaction
const ERRSTOP=-2		'apos;// error, stop processing transactions

'apos;//	local variables.	6/3/20
Dim Doc As Object
Dim oGLSheet As Object		'apos;// GL (source) sheet object
dim oSheets As Object		'apos;// .Sheets() current Doc
dim oSel as Object
dim iSheetIx as integer
dim oRange as Object

'apos;//	GL XCell information objects (1st transaction line)	6/3/20
dim oCellRef as object		'apos;// reference info
dim sRef As String			'apos;// reference field

'apos;//	GL XCell information objects (line 2 of transaction)
dim oCellDateB as object	'apos;// date of transaction
dim oCellRefB as object		'apos;// reference info

'apos;// GL field data extraction objects (.Text or .Value) and strings
dim sAcct as String			'apos;// COA account field .String

'apos;// Date field variables for both GL objects.
dim sMonth as String

'apos;//	CellRange content variables for GL sheet.
dim oGLRange as Object		'apos;// RangeAddress object of two rows processing
dim lGLStartRow as long
dim lGLEndRow as long
dim lGLCurrRow as long			'apos;// current row in GL
dim lTransCount	as long			'apos;// number of transactions to process

'apos;// more local vars.
dim iBrkPt as integer			'apos;// dummy var for setting breakpoint lines
dim iStatus as integer				'apos;// general status return var
dim lStatus as Long				'apos;// long status for PlaceSplitTrans

'apos;// row processing variables; use to process selected area
dim i as long		'apos;// loop counter
dim lNRowsSelected as long
dim bOddRowCount as boolean
dim sAcctB		as String		'apos;// COA field from 2nd line
dim oCat1Range As Object		'apos;// COA information for line 1
dim oCat2Range As Object		'apos;// COA information for line 2		

'apos;//----------in Module error handling setup-------------------------------
Dim sErrName as String		'apos;// error name for LogError
Dim sErrMsg As String		'apos;// error message for LogError
'apos;*
'apos;//-----------error handling setup-----------------------
'apos;// Error Handling setup snippet.
	const sMyName="quot;PlaceTransM"quot;
	dim lLogRow as long		'apos;// cell row working on
	dim lLogCol as Long		'apos;// cell column working on
	dim iLogSheetIx as Integer	'apos;// sheet index module working on
	dim oLogRange as new com.sun.star.table.CellRangeAddress
'apos;//-----------end in Module error handling setup-----------------------


	'apos;// code.
	
	Doc = ThisComponent			'apos;// set up for sheet access within document
	oSel = Doc.getCurrentSelection()	'apos;// get current cell selection(s) info
	oRange = oSel.RangeAddress			'apos;// extract range information
	iSheetIx = oRange.Sheet				'apos;// get sheet index value

'apos;//------------error handling setup code.--------------------
	iLogSheetIx = iSheetIx
	oLogRange.Sheet = iLogSheetIx
	lLogCol = oRange.StartColumn
	lLogRow = oRange.StartRow
	oLogRange.StartColumn = lLogCol
	oLogRange.StartRow = lLogRow
	oLogRange.EndRow = lLogRow
	oLogRange.EndColumn = lLogCol
	Call ErrLogSetup(oLogRange, sMyName)
'apos;//-----------end error handling setup-----------------------

	oGLSheet = Doc.Sheets(iSheetIx)		'apos;// set up sheet object

iBrkpt=1	

	'apos;// set row bounds from selection information
	oGLRange = new com.sun.star.table.CellRangeAddress				'apos;// mod060120
	oGLRange.Sheet = iSheetIx										'apos;// mod060120
	lGLStartRow = oRange.StartRow
	lGLEndRow = oRange.EndRow
	lGLCurrRow = lGLStartRow - 2		'apos;// current GL processing row preset for increment
	lNRowsSelected = lGLEndRow+1 - lGLStartRow
	lTransCount = lNRowsSelected/2	'apos;// get transaction count (2 rows per)
'apos;	bOddRowCount = lTransCount*2 &amp;'apos;lt;&amp;'apos;gt; lNRowsSelected
	
	for i = 1 to lTransCount

		lGLCurrRow = lGLCurrRow + 2	'apos;// advance to next entry

		'apos;// Note. By checking for "quot;split"quot; first, allows user to select
		'apos;// first row only if wants to process 1 split transaction and no others
		'apos;// Check for "quot;split"quot;; branch into and check
		oCellRef = oGLSheet.getCellByPosition(COLREF, lGLCurrRow)
		sRef = oCellRef.String

		if StrComp(sRef, "quot;split"quot;)=0 then
			lStatus = PlaceSplitTrans(oGLSheet, lGLCurrRow)
			if lStatus &amp;'apos;lt; 0 then
				iStatus = ERRSTOP
				exit for
			else
				lGLCurrRow = lStatus - 1	'apos;// minus 1 since advances by 2
				'apos;// restore error handling setup
	iLogSheetIx = iSheetIx
	oLogRange.Sheet = iLogSheetIx
	lLogCol = oRange.StartColumn
	lLogRow = oRange.StartRow
	oLogRange.StartColumn = lLogCol
	oLogRange.StartRow = lLogRow
	oLogRange.EndRow = lLogRow
	oLogRange.EndColumn = lLogCol
	Call ErrLogSetup(oLogRange, sMyName)
	
				if lGLCurrRow &amp;'apos;lt; lGLEndRow-1 then 
					GoTo AdvanceTrans
				else
					exit for
				endif
				
			endif	'apos;// bad return from PlaceSplitTrans conditional
			
		endif	'apos;// end "quot;split"quot; conditional
		
		'apos;// not a "quot;split"quot;, check for end/odd row count
		bOddRowCount = (lGLCurrRow = lGLEndRow)
		if bOddRowCount then
			GoTo CheckLastRow
		endif
		
		'apos;// account for case where a "quot;split"quot; was handled and
		'apos;// adding 2 advances past end of selection by user
		if lGLCurrRow &amp;'apos;gt; lGLEndRow then
			exit for
		endif
				
		'apos;// set RangeAddress object fields to point to 1st row
		'apos;// and last row of transaction
		'apos;// set up the range of the current transaction
		oGLRange.StartColumn = COLDATE
		oGLRange.StartRow = lGLCurrRow
		oGLRange.EndColumn = COLREF
		oGLRange.EndRow = lGLEndRow			'apos;// set end of user selection

	
		'apos;// Check the Date, Transaction, Debit/Credit and COA fields
		'apos;//	for validity
		sMonth = "quot;"quot;
		sAcct = "quot;"quot;
		sAcctB = "quot;"quot;
'apos;//	Usage.	iVal = CheckDoubleEntry(oTransRange, rpsMonth, rpsAcct1,
'apos;//						rpsAcct2)
		iStatus = CheckDoubleEntry(oGLRange, sMonth, sAcct, sAcctB)
		if iStatus &amp;'apos;lt; 0 then
			Select Case iStatus
			Case ERRCONTINUE
				GoTo AdvanceTrans
			Case ERRSTOP
				exit for
			End Select
		endif		'apos;// end error on Double Entry conditional
		
		'apos;// Check target sheets/accounts for ability to insert
		'apos;// row at correct accounting line
'apos;//	Usage.	iVal = CheckInserts(sMonth, sAcct1, sAcct2, oCat1Range, oCat2Range)

		oCat1Range = new com.sun.star.table.CellRangeAddress
		oCat2Range = new com.sun.star.table.CellRangeAddress
		oCat1Range.Sheet = 0
		oCat1Range.StartRow = 0
		oCat1Range.StartColumn = 0
		oCat1Range.EndRow = 0
		oCat1Range.EndColumn = 0
		oCat2Range.Sheet =  0
		oCat2Range.StartRow = 0
		oCat2Range.StartColumn = 0
		oCat2Range.EndRow = 0
		oCat2Range.EndColumn = 0
		iStatus = CheckInserts(sMonth, sAcct, sAcctB, oCat1Range, oCat2Range)
		if iStatus &amp;'apos;lt; 0 then
			Select Case iStatus
			Case ERRCONTINUE
				GoTo AdvanceTrans
			Case ERRSTOP
				exit for
			End Select
		endif		'apos;// end error on CheckInserts conditional
		
		'apos;// Store transaction in new rows in account sheet(s)
		iStatus = StoreTrans(oGLRange, oCat1Range, oCat2Range, sAcct, sAcctB)
		if iStatus &amp;'apos;lt; 0 then
			Select Case iStatus
			Case ERRCONTINUE
				GoTo AdvanceTrans
			Case ERRSTOP
				exit for
			End Select
		endif		'apos;// end error on StoreTrans conditional
		
		'apos;// Update processed status in current transaction
		'apos;//	by setting COLREF cells background to LTGREEN
'apos;//	Usage.	iVar = SetProcessed(oTransRange)
		iStatus = SetProcessed(oGLRange)
		if iStatus &amp;'apos;lt; 0 then
			Select Case iStatus
			Case ERRCONTINUE
				GoTo AdvanceTrans
			Case ERRSTOP
				exit for
			End Select
		endif		'apos;// end error on SetProcessed conditional

SetProcessed:
			'apos;// Change color in GL lines to indicate processed.					'apos;// mod051920
			oCellRef = oGLSheet.getCellByPosition(COLREF, lGLCurrRow)
			oCellRefB = oGLSheet.getCellByPosition(COLREF, lGLCurrRow+1)	
			oCellRef.Text.CellBackColor = LTGREEN								'apos;// mod051920
			oCellRefB.Text.CellBackColor = LTGREEN								'apos;// mod051920

iBrkpt=1
	
AdvanceTrans:						'apos;// use since this Basic does not support Continue For
			
	next i		'apos;// end loop on GL rows to process

CheckLastRow:
	'apos;// check for last row orphaned.
	if bOddRowCount then
		oCellDateB = oGLSheet.getCellByPosition( COLDATE, lGLEndRow)	'apos;// load last row date field
		oCellDateB.Text.CellBackColor = YELLOW
		sErrName = "quot;ERRODDROWS"quot;
		sErrMsg = "quot;Selection ends in mid-transaction!"quot;_
				+CHR(13)+CHR(10)+ "quot;Check last row selection of transaction"quot;
		Call LogError(sErrName, sErrMsg)
	endif	'apos;// end odd row count conditional

DontCheck:
	'apos;// Update Sheet modified date stamp to indicate changes		'apos;// mod060620
	'apos;// Note: COA sheets'apos; dates updated in StoreTrans
	Call SetSheetDate(oGLRange, MMDDYY)									'apos;// mod060620

end sub		'apos;// end PlaceTransM		6/12/20
'apos;/**/

'apos;// ReverseDETrans.bas
'apos;//---------------------------------------------------------------
'apos;// ReverseDETrans - Reverse double-entry transaction.
'apos;//		6/12/20.	wmk.	17:30
'apos;//---------------------------------------------------------------

public function ReverseDETrans(poGLRange As Object) As Integer

'apos;//	Usage.	iVal = ReverseDETrans(oGLRange)
'apos;//
'apos;//		oGLRange = GL RangeAddress of double-entry transaction
'apos;//
'apos;// Entry.	oGLRange.Sheet = sheet index of ledger
'apos;//					.StartColumn = COLDATE
'apos;//					.StartRow = 1st row of transaction
'apos;//					.EndColumn = COLREF
'apos;//					.EndRow = last row of transaction
'apos;//
'apos;//	Exit.	iVal = 0 - normal return
'apos;//				  -1 - abnormal return
'apos;//
'apos;// Calls.	CheckDoubleEntry, CreateReverseTrans
'apos;//			ErrLogSetup, ErrLogSetRecording
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/11/20.	wmk.	original code
'apos;//	6/12/20.	wmk.	documentaton updated; code updated to call
'apos;//						CheckDoubleEntry and CreateReverseTrans
'apos;//
'apos;//	Notes. Method: For double-entry transaction,insert 2 lines at first
'apos;// row position with "quot;ROWS"quot; option; copy original transaction to 2 new
'apos;// rows; swap Debit and Credit columns in old transaction by copying
'apos;// back from original transaction into opposite columns.
'apos;// Once the reversing transaction has been put in place in the GL,
'apos;// the user runs PlaceTransM or PlaceSplitTrans to store the 
'apos;// new transaction in the appropriate COA sheets
'apos;// as though the user entered them for processing.

'apos;//	constants.
const DEROWCOUNT=2			'apos;// double entry row count

'apos;//	local variables.
dim iRetValue As Integer
Dim Doc As Object
Dim oGLSheet As Object		'apos;// GL (source) sheet object
dim oSheets As Object		'apos;// .Sheets() current Doc
dim oSel as Object
dim oRange as Object
dim iSheetIx As Integer		'apos;// GL sheet index
dim iStatus As Integer

'apos;//	CellRange content variables for GL sheet.
dim oGLRange as Object		'apos;// RangeAddress object of two rows processing
dim lGLStartRow as long
dim lGLEndRow as long
dim lGLCurrRow as long			'apos;// current row in GL
dim lTransCount	as long			'apos;// number of transactions to process
dim sMonth As String			'apos;// month name
dim sAcct1 As String			'apos;// line 1 COA field
dim sAcct2 As String			'apos;// line 2 COA field
dim oCellDate As Object		'apos;// ledger date field
dim oCellRef As Object		'apos;// ledger reference field
dim lGLOrigRow As Long		'apos;// original transaction row after insert

'apos;// error handling setup------------------------------------------------
const sMyName="quot;ReverseSplitTrans"quot;
dim sErrCode as String	'apos;// error code string for user code
Dim sErrMsg As String	'apos;// error message for LogError
dim lLogRow as long		'apos;// cell row working on
dim lLogCol as Long		'apos;// cell column working on
dim iLogSheetIx as Integer	'apos;// sheet index module working on
dim oLogRange as new com.sun.star.table.CellRangeAddress

	'apos;// code.

	Doc = ThisComponent			'apos;// set up for sheet access within document
	oSel = Doc.getCurrentSelection()	'apos;// get current cell selection(s) info
	oRange = oSel.RangeAddress			'apos;// extract range information
	iSheetIx = poGLRange.Sheet			'apos;// get sheet index value
	lGLStartRow = poGLRange.StartRow	'apos;// set local start row for new
	
'apos;// error handling initialization---------------------------------------
	sErrCode = "quot;"quot;
	iLogSheetIx = poGLRange.Sheet
	lLogCol = poGLRange.StartColumn
	lLogRow = poGLRange.StartRow
	oLogRange.Sheet = iLogSheetIx
	oLogRange.StartColumn = lLogCol
	oLogRange.StartRow = lLogRow
	oLogRange.EndRow = lLogRow
	oLogRange.EndColumn = lLogCol
	Call ErrLogSetup(oLogRange, sMyName)
	ErrLogSetRecording(true)		'apos;// turn on log sheet
'apos;//--------------end error handling setup-------------------------------

	
	iRetValue = -1		'apos;// abnormal return
	oGLSheet = Doc.Sheets(iSheetIx)		'apos;// set up sheet object

	'apos;// set up call to CheckDouble entry
	'apos;// verify transaction before reversing
	iStatus = CheckDoubleEntry(poGLRange, sMonth, sAcct1, sAcct2)
	if iStatus &amp;'apos;lt; 0 then
		iRetValue = iStatus
		GoTo ErrorHandler
	endif
	
	iStatus = CreateReverseTrans(oGLSheet, lGLStartRow, DEROWCOUNT)
	if iStatus &amp;'apos;lt; 0 then
		iRetValue = iStatus
		GoTo ErrorHandler
	endif	'apos;// end CreateReversTrans error conditional
	
	lGLOrigRow = lGLStartRow + DEROWCOUNT

	'apos;// highlight reversal transaction date fields
	oCellDate = oGLSheet.getCellByPosition(COLDATE, lGLOrigRow)
	oCellDate.String = oCellDate.String
	oCellDate.CellBackColor = LTGREEN
	oCellDate = oGLSheet.getCellByPosition(COLDATE, lGLOrigRow+1)
	oCellDate.String = oCellDate.String
	oCellDate.CellBackColor = LTGREEN

	'apos;// document "quot;reversal"quot; in REF column
	oCellRef = oGLSheet.getCellByPosition(COLREF, lGLOrigRow)
	oCellRef.String = "quot;reversal"quot;
	oCellRef = oGLSheet.getCellByPosition(COLREF, lGLOrigRow+1)
	oCellRef.String = "quot;reversal"quot;
	
	'apos;// set normal return
	iRetValue = 0

NormalExit:	
	ReverseDETrans = iRetValue
	exit function
	
ErrorHandler:
	ReverseDETrans = iRetValue
	
end function 	'apos;// end ReverseDETrans	6/12/20
'apos;/**/

'apos;// ReverseSplitTrans.bas
'apos;//---------------------------------------------------------------
'apos;// ReverseSplitTrans - Reverse split transaction.
'apos;//		7/9/20.	wmk.	07:45
'apos;//---------------------------------------------------------------

public function ReverseSplitTrans(poGLRange As Object) As Integer

'apos;//	Usage.	iVal = ReverseSplitTrans(oGLRange)
'apos;//
'apos;//		oGLRange = GL RangeAddress of double-entry transaction
'apos;//
'apos;// Entry.	oGLRange.Sheet = sheet index of ledger
'apos;//					.StartColumn = COLDATE
'apos;//					.StartRow = 1st row of transaction
'apos;//					.EndColumn = COLREF
'apos;//					.EndRow = last row of transaction
'apos;//
'apos;//	Exit.	iVal = 0 - normal return
'apos;//				  -1 - abnormal return
'apos;//
'apos;// Calls.	CreateReverseTrans, SetSumFormula
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/11/20.	wmk.	original code; stub
'apos;//	6/12/20.	wmk.	original complete
'apos;//	7/9/20.		wmk.	bug fix where "quot;reversal"quot; field LTGREEN
'apos;//
'apos;//	Notes. Method. For "quot;split"quot; entry transaction, verify with
'apos;// CheckSplitTrans which will return the last "quot;split"quot; row of the
'apos;// transaction; insert lRowCount rows at first row of transaction with
'apos;// "quot;ROWS"quot; option; copy original transaction rows into new rows; swap
'apos;// Debit and Credit columns in old.
'apos;// The original transaction is placed first in the
'apos;// GL, and the new reversing transaction immediately following.
'apos;// Once the reversing transaction has been put in place in the GL,
'apos;// the user runs PlaceTransM or PlaceSplitTrans to store the 
'apos;// new transaction in the appropriate COA sheets
'apos;// as though the user entered them for processing.

'apos;//

'apos;//	constants.

'apos;//	local variables.
dim iRetValue As Integer
Dim Doc As Object
Dim oGLSheet As Object		'apos;// GL (source) sheet object
dim oSheets As Object		'apos;// .Sheets() current Doc
dim oSel as Object
dim iSheetIx as integer
dim oRange as Object
dim iStatus As Integer
dim i As Integer			'apos;// short loop counter

'apos;// GL Row processing variables.
dim lSplit1stRow As Long
dim lSplitEndRow As Long
dim lGLCurrRow As Long		'apos;// current GL row being processed
dim lRowCount As Integer	'apos;// count of rows in transaction
dim oCellDate As Object		'apos;// Date field cell
dim oCellCredit As Object	'apos;// Credit field cell
dim oCellRef As Object		'apos;// Reference field cell
dim oCellAcct As Object		'apos;// COA field cell
dim bDebitsMoving As Boolean	'apos;// debits moving to credits flag
dim sSumStr As String		'apos;// SUM formula string

'apos;// error handling setup------------------------------------------------
const sMyName="quot;ReverseSplitTrans"quot;
dim sErrCode as String	'apos;// error code string for user code
Dim sErrMsg As String	'apos;// error message for LogError
dim lLogRow as long		'apos;// cell row working on
dim lLogCol as Long		'apos;// cell column working on
dim iLogSheetIx as Integer	'apos;// sheet index module working on
dim oLogRange as new com.sun.star.table.CellRangeAddress

	'apos;// code.

'apos;// error handling initialization---------------------------------------
	sErrCode = "quot;"quot;
	iLogSheetIx = poGLRange.Sheet
	lLogCol = poGLRange.StartColumn
	lLogRow = poGLRange.StartRow
	oLogRange.Sheet = iLogSheetIx
	oLogRange.StartColumn = lLogCol
	oLogRange.StartRow = lLogRow
	oLogRange.EndRow = lLogRow
	oLogRange.EndColumn = lLogCol
	Call ErrLogSetup(oLogRange, sMyName)
	ErrLogSetRecording(true)		'apos;// turn on log sheet
'apos;//--------------end error handling setup-------------------------------

	iRetValue = -1
	Doc = ThisComponent			'apos;// set up for sheet access within document
	oSel = Doc.getCurrentSelection()	'apos;// get current cell selection(s) info
	oRange = oSel.RangeAddress			'apos;// extract range information
	iSheetIx = oRange.Sheet				'apos;// get sheet index value

	'apos;// set up sheet object for CheckSplitTrans
	'apos;// verify original split transaction
	oSheets = Doc.getSheets()
	oGLSheet = Doc.Sheets(iSheetIx)
	lSplit1stRow = poGLRange.StartRow
	lSplitEndRow = CheckSplitTrans(oGLSheet, lSplit1stRow)
	if lSplitEndRow &amp;'apos;lt; 0 then
		GoTo ErrorHandler
	endif	'apos;// end problem with split conditional

	lRowCount = lSplitEndRow - lSplit1stRow + 1
	iStatus = CreateReverseTrans(oGLSheet, lSplit1stRow, lRowCount)
	if iStatus &amp;'apos;lt; 0 then
		GoTo ErrorHandler
	endif
	
	'apos;// highlight reversal transaction rows DATE fields with LTGREEN
	'apos;// and set REF field to "quot;reversal"quot;
	lGLCurrRow = lSplit1stRow + lRowCount + 1	'apos;// start at total row
	for i = 0 to lRowCount-3
		oCellDate = oGLSheet.getCellByPosition(COLDATE, lGLCurrRow)
		oCellDate.String = oCellDate.String
		oCellDate.CellBackColor = LTGREEN
		oCellRef = oGLSheet.getCellByPosition(COLREF, lGLCurrRow)
		oCellRef.String = "quot;reversal"quot;
		oCellRef.CellBackColor = NOFILL
		lGLCurrRow = lGLCurrRow + 1
	next i		'apos;// end loop on transaction rows

	'apos;// get transaction sum formula, store and highlight as LTGREEN
	oCellCredit = oGLSheet.getCellByPosition(COLCREDIT, lSplit1stRow+1)
	bDebitsMoving = (len(oCellCredit.String)&amp;'apos;gt;0)
dim	oGLRange As new com.sun.star.table.CellRangeAddress
	oGLRange.Sheet = iSheetIx
	oGLRange.StartColumn = COLDATE
	oGLRange.StartRow = poGLRange.StartRow
	oGLRange.EndColumn = COLREF
	oGLRange.EndRow = oGLRange.StartRow + lRowCount -1
	sSumStr = SetSumFormula(oGLRange, bDebitsMoving)
	if len(sSumStr) =  0 then
		GoTo ErrorHandler
	endif	'apos;// end SetSumFormula error conditional
	
	oCellAcct = oGLSheet.getCellByPosition(COLACCT, lSplitEndRow + lRowCount)
	oCellAcct.String = sSumStr		'apos;// set sum formula
	oCellAcct.setFormula(sSumStr)
	oCellAcct.Text.HoriJustify = CJUST
	oCellAcct.CellBackColor = LTGREEN
	oCellAcct.NumberFormat = DEC2
	iRetValue = 0			'apos;// set normal return
	
NormalExit:
	ReverseSplitTrans = iRetValue
	exit function
	
ErrorHandler:	
	ReverseSplitTrans = iRetValue

end function 	'apos;// end ReverseSplitTrans 7/9/20
'apos;/**/

'apos;// ReverseTrans.bas
'apos;//---------------------------------------------------------------
'apos;// ReverseTrans - Reverse transaction.
'apos;//		6/12/20.	wmk.	17:45
'apos;//---------------------------------------------------------------

public sub ReverseTrans()

'apos;//	Usage.	macro call or
'apos;//			call ReverseTrans()
'apos;//
'apos;// Entry.	User has first row of transaction selected;
'apos;//			may either be a double entry or "quot;split"quot;
'apos;//			
'apos;//	Exit.	Reversing transaction entered below original
'apos;//			Reversing transaction processed by appropriate
'apos;//			double-entry or "quot;split"quot; transaction
'apos;//
'apos;// Calls.	ReverseSplit, ReverseDETrans, SetSheetDate
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/11/20.		wmk.	original code
'apos;//	6/12/20.		wmk.	add call to SetSheetDate
'apos;//
'apos;//	Notes. Method: For double-entry transaction,insert 2 lines at first
'apos;// row position with "quot;ROWS"quot; option; copy original transaction to 2 new
'apos;// rows; swap Debit and Credit columns in old transaction by copying
'apos;// back from original transaction into opposite columns.
'apos;// For "quot;split"quot; entry transaction, verify with CheckSplitTrans which will
'apos;// return the last "quot;split"quot; row of the transaction; insert lRowCount rows
'apos;// at first row of transaction with "quot;ROWS"quot; option; copy original
'apos;// transaction rows into new rows; swap Debit and Credit columns in old
'apos;// Both these methods will place the original transaction first in the
'apos;// GL, and the new reversing transaction immediately following.
'apos;// Once the reversing transaction has bee put in place in the GL,
'apos;// the new transaction should be stored in the appropriate COA sheets
'apos;// as though the user entered them for processing.


'apos;//	constants.

'apos;//	local variables.
Dim Doc As Object
Dim oGLSheet As Object		'apos;// GL (source) sheet object
dim oSheets As Object		'apos;// .Sheets() current Doc
dim oSel as Object
dim iSheetIx as integer
dim oRange as Object
dim iStatus As Integer

'apos;// GL entry processing variables.
dim lGLStartRow As Long
dim lGLEndRow As Long
dim lGLCurrRow As Long
dim oCellRef As Object
dim sRef As String

'apos;//----------in Module error handling setup-------------------------------
Dim sErrName as String		'apos;// error name for LogError
Dim sErrMsg As String		'apos;// error message for LogError
'apos;*
'apos;//-----------error handling setup-----------------------
'apos;// Error Handling setup snippet.
	const sMyName="quot;ReverseTrans"quot;
	dim lLogRow as long		'apos;// cell row working on
	dim lLogCol as Long		'apos;// cell column working on
	dim iLogSheetIx as Integer	'apos;// sheet index module working on
	dim oLogRange as new com.sun.star.table.CellRangeAddress
'apos;//-----------end in Module error handling setup-----------------------

	'apos;// code.
	Doc = ThisComponent			'apos;// set up for sheet access within document
	oSel = Doc.getCurrentSelection()	'apos;// get current cell selection(s) info
	oRange = oSel.RangeAddress			'apos;// extract range information
	iSheetIx = oRange.Sheet				'apos;// get sheet index value

'apos;//------------error handling setup code.--------------------
	iLogSheetIx = iSheetIx
	oLogRange.Sheet = iLogSheetIx
	lLogCol = oRange.StartColumn
	lLogRow = oRange.StartRow
	oLogRange.StartColumn = lLogCol
	oLogRange.StartRow = lLogRow
	oLogRange.EndRow = lLogRow
	oLogRange.EndColumn = lLogCol
	Call ErrLogSetup(oLogRange, sMyName)
'apos;//-----------end error handling setup-----------------------

	oGLSheet = Doc.Sheets(iSheetIx)		'apos;// set up sheet object

	'apos;// set row bounds from selection information
	'apos;// Note. ReverseTrans will only process one transaction, regardless
	'apos;// of how many rows the user has selected.
dim	oGLRange As new com.sun.star.table.CellRangeAddress				'apos;// mod060120
	oGLRange.Sheet = iSheetIx										'apos;// mod060120
	lGLStartRow = oRange.StartRow
	lGLEndRow = oRange.EndRow
	lGLCurrRow = lGLStartRow 		'apos;// current GL processing row
'apos;	lNRowsSelected = lGLEndRow+1 - lGLStartRow

	'apos;// set RangeAddress object fields to point to 1st row
	'apos;// and last row of transaction
	'apos;// set up the range of the current transaction
	oGLRange.StartColumn = COLDATE
	oGLRange.StartRow = lGLCurrRow
	oGLRange.EndColumn = COLREF
	oGLRange.EndRow = lGLEndRow			'apos;// set end of user selection

	'apos;// Check for "quot;split"quot;; branch into and check
	oCellRef = oGLSheet.getCellByPosition(COLREF, lGLCurrRow)
	sRef = oCellRef.String

	if StrComp(sRef, "quot;split"quot;)=0 then
		iStatus = ReverseSplitTrans(oGLRange)
	else		'apos;// is double entry transaction
		iStatus = ReverseDETrans(oGLRange)
	endif

	if iStatus &amp;'apos;lt; 0 then
		GoTo ErrorHandler
	endif

	'apos;// Update Sheet modified date stamp to indicate changes			'apos;// mod060620
	'apos;// Note: COA sheets'apos; dates updated in StoreTrans
	Call SetSheetDate(oGLRange, MMDDYY)									'apos;// mod060620
	
NormalExit:
	Exit Sub

ErrorHandler:


end sub		'apos;// end ReverseTrans	6/12/20
'apos;/**/

'apos;// SetProcessed.bas
'apos;//---------------------------------------------------------------
'apos;// SetProcessed - Set double-entry transaction processed.
'apos;//		6/1/20.	wmk.
'apos;//---------------------------------------------------------------

public function SetProcessed(oTransRange as Object) as Integer

'apos;//	Usage.	iVar = SetProcessed(oTransRange)
'apos;//
'apos;//		oTransRange = RangeAddress of double-entry transaction
'apos;//
'apos;// Entry.	&amp;'apos;lt;entry conditions&amp;'apos;gt;
'apos;//
'apos;//	Exit.	iVar = 0 if no error; -1 otherwise
'apos;//
'apos;// Calls.	&amp;'apos;lt;other subs/functions called&amp;'apos;gt;
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/1/20.	wmk.	original code; stub
'apos;//
'apos;//	Notes. &amp;'apos;lt;Insert notes here&amp;'apos;gt;
'apos;//

'apos;//	constants.

'apos;//	local variables.
dim iRetValue As Integer

	'apos;// code.
	
	iRetValue = 0		'apos;// set normal return
	
	SetProcessed = iRetValue

end function 	'apos;// end SetProcessed  6/1/20
'apos;/**/

'apos;// SetSheetDate.bas
'apos;//---------------------------------------------------------------
'apos;// SetSheetDate - Set date/time stamp in common sheet date field.
'apos;//		6/8/20.	wmk.
'apos;//---------------------------------------------------------------

public function SetSheetDate(poRange As Object, piFmt As Integer) As Integer

'apos;//	Usage.	iVar = SetSheetDate(oRange, iFmt)
'apos;//
'apos;//		oRange = RangeAddress of sheet to set date/time stamp in
'apos;//		iFmt = NumberFormat for date/time stamp {FMTDATETIME, MMDDYY}
'apos;//
'apos;// Entry.	system clock running
'apos;//
'apos;//	Exit.	Sheet at Sheet index within oRange has Now() date/time
'apos;//			stamp set in Cell column=COLDATEMOD, row=ROWDATEMOD
'apos;//			iVar = 0 - no error
'apos;//				   -1 - untrapped error occurred; date/time stamp
'apos;//						setting probably failed
'apos;//
'apos;// Calls.	&amp;'apos;lt;other subs/functions called&amp;'apos;gt;
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/3/20.		wmk.	original code
'apos;//	6/6/20.		wmk.	date/time format parameter added	
'apos;//	6/8/20.		wmk.	add LTGRN background color to new date
'apos;//	Notes.


'apos;//	constants.
const COLDATEMOD=2		'apos;// corresponds to "quot;C"quot;
const ROWDATEMOD=1		'apos;// corresponds to "quot;2"quot; for cell C2
'apos;const FMTDATETIME=50	'apos;// numeric value of mm/dd/yyy hh:mm:ss spec

'apos;//	local variables.
dim iRetValue As Integer
dim oDoc As Object		'apos;// ThisComponent
dim oSheets As Object	'apos;// Doc.getSheets()
dim oSheet As Object	'apos;// target sheet
dim iSheetIx As Integer	'apos;// target sheet index
dim oCellTime as Object	'apos;// date/time modified cell
dim iDateFormat As Integer	'apos;// date/time format to set

	'apos;// code.
	
	iRetValue = 0
	ON ERROR GoTo Abnormal
	iSheetIx = poRange.Sheet	'apos;// extract sheet index from passed range
	oDoc = ThisComponent		'apos;// access target sheet using range information
	oSheets = oDoc.getSheets()
	oSheet = oSheets.getByIndex(iSheetIx)
	oCellTime = oSheet.getCellByPosition(COLDATEMOD, ROWDATEMOD)
	
	oCellTime.setValue(Now())					'apos;// time stamp
	
	Select Case piFmt
	CASE FMTDATETIME
		iDateFormat = FMTDATETIME
		
	CASE MMDDYY
		iDateFormat = MMDDYY
		
	CASE else
		oDateFormat = MMDDYY
		
	End Select
	
	oCellTime.NumberFormat = iDateFormat		'apos;// set number format
	oCellTime.CellBackColor = LTGREEN			'apos;// set changed color
	GoTo NormalExit
	
Abnormal:
	iRetValue = -1		'apos;// set abnormal return code
	
NormalExit:
	SetSheetDate = iRetValue

end function 	'apos;// end SetSheetDate	6/8/20
'apos;/**/

'apos;// SetSumFormula.bas
'apos;//---------------------------------------------------------------
'apos;// SetSumFormula - Create SUM formula for last split account cell.
'apos;//		wmk. 5/28/20.	23:15
'apos;//---------------------------------------------------------------

public function SetSumFormula(poRange as Object, pbDebitsMoving as Boolean) As String

'apos;//	Usage.	sNewFormula = SetSumFormula(oRange, bDebitsMoving)
'apos;//
'apos;//			oRange = RangeAddress object with .StartRow = first row of split
'apos;//						.EndRow = last row of split
'apos;//			bDebitsMoving = true if moving Debits to Credits; formula should be SUM(Credits)
'apos;//					   false if moving Credits to Debits; formula should be SUM(Debits)
'apos;//
'apos;//	Exit.	sNewFormula = sum formula; either "quot;=SUM(D..:D)"quot; for SUM(Credits)
'apos;//						 or "quot;=SUM(C..:C..)"quot; for SUM(Debits)
'apos;//
'apos;// Calls.	ErrLogGetModule, ErrLogSetModule
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	5/25/20.	wmk.	original code
'apos;//	5/26/20.	wmk.	error handling upgrade
'apos;// 5/28/20.	wmk.	bug fix; changed calling sequence for readability;
'apos;//						changed logic to properly regenerate formula; code clarified
'apos;//						cell range index strings trimmed; convert row indices to
'apos;//						row numbers by adding 1
'apos;//
'apos;//	Notes. &amp;'apos;lt;Insert notes here&amp;'apos;gt;

'apos;//	constants.
const CHECKFORMCHARS=6		'apos;// number of leading characters to check in formula
const DEBITCHARS="quot;=SUM(C"quot;	'apos;// Debit summing formula
const CREDCHARS="quot;=SUM(D"quot;	'apos;// Credit summing formula
const RANGEDEBIT="quot;:C"quot;
const RANGECREDIT="quot;:D"quot;
const ERRBADRANGE=-1		'apos;// bad range error code; missing 'apos;:'apos;

'apos;//------------------begin error handling block---------------
'apos;// LogError setup snippet.

'apos;// error code strings.
const csERRUNKNOWN="quot;ERRUNKNOWN"quot;
const csERRBADRANGE="quot;ERRBADRANGE"quot;

Dim sErrName as String		'apos;// error name for LogError
Dim sErrMsg As String		'apos;// error message for LogError
'apos;//------------------end error handling block---------------

'apos;//	local variables.
dim iMidPtr As Integer		'apos;// mid-string char pos pointer
dim sRetString As String	'apos;// returned string
dim sStartRow As String		'apos;// starting row
dim sEndRow As String		'apos;// ending row
dim iScanPos As Integer		'apos;// string scanning position
dim sScanChar As String		'apos;// current scan char
dim iFormulaLen As Integer	'apos;// formula length
dim sWhatsLeft As String	'apos;// remaining formula to scan
dim iErr as Integer			'apos;// error flag
dim iColonPos as Integer	'apos;// pos of "quot;:"quot; in formula string
dim sMidRange As String		'apos;// midrange string "quot;:C"quot; or "quot;:D"quot;

	'apos;// code.
'apos;//-----------error handling setup-----------------------
'apos;// Error Handling setup snippet.
	const sMyName="quot;SetSumFormula"quot;
'apos;	dim lLogRow as long		'apos;// cell row working on
'apos;	dim lLogCol as Long		'apos;// cell column working on
'apos;	dim iLogSheetIx as Integer	'apos;// sheet index module working on
'apos;	dim oLogRange as new com.sun.star.table.CellRangeAddress
'apos;//	dim oDoc as Object		'apos;// ThisComponent
'apos;//	dim oSheets as Object	'apos;// oDoc.Sheets
'apos;//	code to put sheet index into oLogRange.Sheet
'apos;	oLogRange.Sheet = iLogSheetIx
'apos;	oLogRange.StartColumn = lLogCol
'apos;	oLogRange.StartRow = lLogRow
'apos;	oLogRange.EndRow = lLogRow
'apos;	oLogRange.EndColumn = lLogCol
'apos;	Call ErrLogSetup(oLogRange, sMyName)
'apos;//OR
	dim sCallerModule As String
	sCallerModule = ErrLogGetModule()
	ErrLogSetModule(sMyName)
	'apos;// on exit...
'apos;	ErrLogSetModule(sCallerModule)
'apos;//----------------end error handling setup-----------------------

	iErr = ERRUNKNOWN			'apos;// set unknown error code
	ON ERROR GOTO ErrHandler
	sRetString = "quot;test string"quot;
	sStartRow = Ltrim(Str(poRange.StartRow+1))	'apos;// add 1 to Row indices to get numbers
	sEndRow = Ltrim(Str(poRange.EndRow+1))

		
'apos;// build returned formula as DEBITCHARS+&amp;'apos;lt;startrow&amp;'apos;gt;+RANGEDEBIT+
'apos;//							  +&amp;'apos;lt;endrow&amp;'apos;gt;+"quot;)"quot;
'apos;// or CREDCHARS+&amp;'apos;lt;startrow&amp;'apos;gt;+RANGECREDIT+
'apos;//							  +&amp;'apos;lt;endrow&amp;'apos;gt;+"quot;)"quot;
	if pbDebitsMoving then
 		sRetString = CREDCHARS	'apos;// + "quot;XX"quot; + RANGECREDIT + "quot;YY"quot; + "quot;)"quot;
 		sMidRange = RANGECREDIT
	else
		sRetString = DEBITCHARS 'apos;// + "quot;XX"quot; + RANGEDEBIT + "quot;YY"quot; + "quot;)"quot;
		sMidRange = RANGEDEBIT
	endif
			
	'apos;// append start row number to returned string
	sRetString = sRetString + sStartRow	+ sMidRange
	sRetString = sRetString + sEndRow + "quot;)"quot;	
	iErr = 0
	GoTo NormalExit
	
ErrHandler:

	if iErr = 0 then
		GoTo NormalExit
	endif
	
	Select Case ierr

		Case ERRBADRANGE
			sErrName = csERRBADRANGE
			sErrMsg = "quot;Expected 'apos;:'apos; in split SUM formula..."quot;
				
		Case else
			sErrName = csERRUNKNOWN
			sErrMsg = "quot;Unrecognized error generating split SUM formula range..."quot;
			
	End Select
	
	Call LogError(sErrName, sErrMsg)
	
NormalExit:
	ErrLogSetModule(sCallerModule)	'apos;// restore caller module name
	SetSumFormula = sRetString
	
end function 	'apos;// end SetSumFormula
'apos;/**/

'apos;// SplitOutCOAs.bas
'apos;//-----------------------------------------------------------------
'apos;// SplitOutCOAs - Split out COA transaction lines from split entry.
'apos;//		8/22/22.	wmk.	11:32
'apos;//-----------------------------------------------------------------

public function SplitOutCOAs(poGLRange As Object, psSplitAcct As String) As Integer

'apos;//	Usage.	iVal = SplitOutCOAs(oGLRange, sSplitAcct)
'apos;//
'apos;//		oGLRange = RangeAddress of the entire "quot;split"quot; transaction
'apos;//		sSplitAcct = COA account splitting total from
'apos;//
'apos;// Entry.	ThisComponent = spreadsheet document
'apos;//
'apos;//	Exit.	iVal = 0 - no error encountered
'apos;//				 &amp;'apos;lt; 0 - error encountered during split operation
'apos;//
'apos;// Calls.	GetMonthName, GetTransSheetName
'apos;//			ErrLogGetModule, ErrLogGetSheet, ErrLogGetCellInfo,
'apos;//			ErrLogSetModule, ErrLogSetSheet, ErrLogSetCellInfo
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/5/20.		wmk.	original code
'apos;// 6/6/20.		wmk.	add code to update sheet cell C2 with date
'apos;//						modified for COAs
'apos;//	6/7/20.		wmk.	add code to set SUM in last split GL row
'apos;//	6/10/20.	wmk.	expande SUM range to go "quot;split"quot; to "quot;split"quot;
'apos;//	7/6/20.		wmk.	bug fix; eliminate code overwriting sSplitAcct
'apos;// 8/22/22.	wmk.	bug fix; text values being stored in Debit/Credit
'apos;//				 target fields, changed to .value values.
'apos;//
'apos;//	Notes.
'apos;//

'apos;//	constants.

'apos;//	local variables.
dim iRetValue As Integer		'apos;// returned value
dim i As Integer				'apos;// loop counter
dim iBrkPt As Integer			'apos;// easy breakpoint
dim iSheetIx As integer			'apos;// sheet index in oSheets()
dim oDoc As Object				'apos;// ThisComponent
dim oSheets As Object			'apos;// oDoc.getSheets()
dim oGLSheet As Object			'apos;// ledger sheet

'apos;// GL sheet processing variables.
dim lRowCount as Long
dim lLastRow As Long
dim l1stRow As Long
dim lGLCurrRow as Long
dim oGLCellDate as Object
dim lDateFormat As Long
dim sStyle As String
dim sDate As String
dim sMonth As String
dim sSplitAcct As String
dim oGLCellAcct As Object
dim sAcct2 As String

'apos;//	target COA sheet processing variables.
dim lCat2InsRow As Long			'apos;// COA insert row
dim sAcctCat2 As String			'apos;// COA category and month
dim oCat2Sheet As Object		'apos;// COA sheet inserting into
'apos;dim oGLCellDate As Object		'apos;// Date cell
dim	oGLCellTrans As Object		'apos;// Transaction description cell
dim	oGLCellDebit As Object		'apos;// Debit cell
dim	oGLCellCredit As Object		'apos;// Credit cell
dim	oGLCellRef As Object		'apos;// Reference cell


'apos;// target COA sheet column values.
dim	oCat2Date As Object		'apos;// Date field
dim	oCat2Trans As Object	'apos;// Transaction description
dim	oCat2Debit As Object	'apos;// Debit field
dim	oCat2Credit As Object 	'apos;// Credit field
dim	oCat2Acct As Object		'apos;// COA field
dim	oCat2Ref As Object		'apos;// Reference field
		

	'apos;// code.
	
	iRetValue = 0

	SplitOutCOAs = iRetValue

'apos;	if true then
'apos;		Exit function
'apos;	endif

'apos;// SplitOutCOAs code begins here...------------------------------------------
'apos;//-----------------error handling setup-----------------------------
dim sErrCallerMod 	As String	'apos;// caller routine name
dim iErrSheetIx		As Integer	'apos;// caller sheet index
dim lErrColumn		As Long		'apos;// caller error focus column
dim lErrRow			As Long		'apos;// caller error focus row
dim sMyName			As String	'apos;// this routine name
	'apos;//*-----------------preserve error handling for caller-----------
	'apos;// preserve caller error settings.
	sErrCallerMod = ErrLogGetModule()
	iErrSheetIx = ErrLogGetSheet()
	ErrLogGetCellInfo(lErrColumn, lErrRow)
	'apos;// set up new error settings.
	sMyName = "quot;SplitOutCOAs"quot;
	ErrLogSetModule(sMyName)
	ErrLogSetSheet(poGLRange.Sheet)
	ErrLogSetCellInfo(poGLRange.StartColumn, poGLRange.StartRow)
'apos;//*----------end error handling setup-------------------------------

	oDoc = ThisComponent			'apos;// document object
	oSheets = oDoc.getSheets()		'apos;// sheets array
	iSheetIx = poGLRange.Sheet
	oGLSheet = oSheets.getByIndex(iSheetIx)
	l1stRow = poGLRange.StartRow	'apos;// first row of "quot;split"quot;
	lLastRow = poGLRange.EndRow		'apos;// last row of "quot;split"quot;
	sSplitAcct = psSplitAcct		'apos;// localize split account
	
	oGLCellDate = oGLSheet.getCellByPosition(COLDATE, l1stRow)

	'apos;// set up parameters for GetTransSheetName and GetInsRow
'apos;	sStyle = oGLCellDate.Text.String
	sDate = oGLCellDate.Text.String
	sMonth = GetMonthName(sDate)		'apos;// get month search name
	sAcctCat2 = GetTransSheetName(sSplitAcct)	'apos;// get sheet name matching COA

	'apos;// access target COA sheet and set up insertion row
	oCat2Sheet = oSheets.getByName(sAcctCat2)
iBrkpt=1		'apos;// oCat2Sheet.RangeAddress.Sheet = sheet index
ErrLogSetSheet(oCat2Sheet.RangeAddress.Sheet)	'apos;// set sheet for errors
	lCat2InsRow = GetInsRow(oCat2Sheet, sSplitAcct, sMonth)

	if lCat2InsRow &amp;'apos;lt; 0 then
		sErrName = "quot;ERRCOAINSERT"quot;
		sErrMsg = "quot;Unable to insert Total rows in split account"quot;
		GoTo ErrHandler
	endif
		
	'apos;// set up oCat2Range for SetSheetDate calls
dim oCat2Range as new com.sun.star.table.CellRangeAddress			'apos;// mod060620
	oCat2Range.Sheet = oCat2Sheet.RangeAddress.Sheet				'apos;// mod060620
	oCat2Range.StartColumn = COLDEBIT								'apos;// mod060620
	oCat2Range.StartRow = DATEROW									'apos;// mod060620
	oCat2Range.EndColumn = COLDEBIT									'apos;// mod060620
	oCat2Range.EndRow = DATEROW										'apos;// mod060620
		
	lGLCurrRow = l1stRow + 1			'apos;// move to total line to split lines
	lRowCount = lLastRow - l1stRow -1	'apos;// transactions row count

iBrkPt=1	

	'apos;// loop on lines between "quot;split"quot;s past total line
	for i=0 to lRowCount-2
		lGLCurrRow = lGLCurrRow + 1		'apos;// advance GL row
		
		'apos;// extract line date from GL line
		'apos;// get month from date field.
		'apos;// get category sheet name
		'apos;// set up parameters for GetTransSheetName and GetInsRow
		oGLCellDate = oGLSheet.getCellByPosition(COLDATE,lGLCurrRow)

	ErrLogSetCellInfo(COLDATE, lGLCurrRow)
		lDateFormat = oGLCellDate.Text.NumberFormat
		sStyle = oGLCellDate.Text.String
		sDate = oGLCellDate.Text.String
		sMonth = GetMonthName(sDate)		'apos;// get month search name
	ErrLogSetCellInfo(COLACCT, lGLCurrRow)
	ErrLogSetSheet(oGLSheet.RangeAddress.Sheet)
		oGLCellAcct = oGLSheet.getCellByPosition(COLACCT, lGLCurrRow)
		sAcct2 = oGLCellAcct.Text.String		'apos;// COA field

		'apos;// check for short or empty account field
		if Len(sAcct2) &amp;'apos;lt; 4 then
			lCat2InsRow = ERRCOANOTFOUND

			GoTo ErrHandler
		endif
		
		sAcctCat2 = GetTransSheetName(sAcct2)	'apos;// get sheet name matching COA

		oCat2Sheet = oSheets.getByName(sAcctCat2)
iBrkpt=1
		lCat2InsRow = GetInsRow(oCat2Sheet, sAcct2, sMonth)

		'apos;// if any problem with insert, bail out - this is all or nothing
		if lCat2InsRow &amp;'apos;lt; 0 then
			GoTo ErrHandler
		endif	'apos;// end insert row error conditional


		'apos;// load category/account splitting transactions from
		'apos;// at this point, just add the lines to the appropriate accounts,
	
		'apos;// extract line data; treat every line like a 2nd line
		oGLCellDate = oGLSheet.getCellByPosition(COLDATE, lGLCurrRow)
		oGLCellTrans = oGLSheet.getCellByPosition(COLTRANS, lGLCurrRow)
		oGLCellDebit = oGLSheet.getCellByPosition(COLDEBIT, lGLCurrRow)
		oGLCellCredit = oGLSheet.getCellByPosition(COLCREDIT, lGLCurrRow)
		oGLCellRef = oGLSheet.getCellByPosition(COLREF, lGLCurrRow)

iBrkPt=1
		'apos;// lCat2InsRow set above...
		'apos;// Insert new line in appropriate category sheet.
	ErrLogSetSheet(oCat2Sheet.RangeAddress.Sheet)
	ErrLogSetCellInfo(COLDATE, lCat2InsRow)
		oCat2Sheet.Rows.insertByIndex(lCat2InsRow, 1)	'apos;// insert new category 2 row


		'apos;// set up target cell objects.
		oCat2Date = oCat2Sheet.getCellByPosition(COLDATE, lCat2InsRow)
		oCat2Trans = oCat2Sheet.getCellByPosition(COLTRANS, lCat2InsRow)
		oCat2Debit = oCat2Sheet.getCellByPosition(COLDEBIT, lCat2InsRow)
		oCat2Credit = oCat2Sheet.getCellByPosition(COLCREDIT, lCat2InsRow)
		oCat2Acct = oCat2Sheet.getCellByPosition(COLACCT, lCat2InsRow)
		oCat2Ref = oCat2Sheet.getCellByPosition(COLREF, lCat2InsRow)

		'apos;// copy date, transaction, debit, credit, and reference fields from GL entry.
		'apos;// set account field to splitting account COA
		oCat2Date.String = oGLCellDate.String
		oCat2Date.Text.HoriJustify = RJUST
		oCat2Date.CellBackColor = LTGREEN
		oCat2Date.HoriJustify = RJUST
		oCat2Trans.Text.String = oGLCellTrans.Text.String
		oCat2Trans.Text.HoriJustify = LJUST
		oCat2Debit.setvalue(oGLCellDebit.getvalue())
'apos;		oCat2Debit.Text.String = oGLCellDebit.Text.String
 		oCat2Debit.Text.HoriJustify = RJUST
 		oCat2Credit.setvalue(oGLCellCredit.getvalue())
'apos;		oCat2Credit.Text.String = oGLCellCredit.Text.String
		oCat2Credit.Text.HoriJustify = RJUST
		oCat2Ref.Text.String = oGLCellRef.Text.String
		oCat2Ref.Text.HoriJustify = CJUST
		
		'apos;// replace account field with splitting account field
		oCat2Acct.Text.String = sSplitAcct
		oCat2Acct.Text.HoriJustify = CJUST

		'apos;// update sheet modified date cell							'apos;// mod060620
		oCat2Range.Sheet = oCat2Sheet.RangeAddress.Sheet			'apos;// mod060620
		Call SetSheetDate(oCat2Range, MMDDYY)						'apos;// mod060620
		oCat2Date = oCat2Sheet.getCellByPosition(COLDEBIT, DATEROW)	'apos;// mod060620
		oCat2Date.CellBackColor = LTGREEN							'apos;// mod060620

	next i		'apos;// advance to next split transaction line

	'apos;// set up RangeAddress object for SetSumFormula
	'apos;// poGLRange has the entire GL range
dim oSumSplitRange as new com.sun.star.table.CellRangeAddress
dim lSumRow As Long			'apos;// row for SUM formula in GL
dim sFormula as String		'apos;// target for SUM in GL
dim bOpposite As Boolean	'apos;// flag for SetSumFormula
	oSumSplitRange.Sheet = poGLRange.Sheet
	oSumSplitRange.StartRow = poGLRange.StartRow + 2	'apos;// first COA row
	oSumSplitRange.EndRow = poGLRange.EndRow - 1		'apos;// last COA row
	oSumSplitRange.StartColumn = poGLRange.StartColumn	'apos;// Date column
	oSumSplitRange.EndColumn = poGLRange.EndColumn		'apos;// Reference column
	lSumRow = poGLRange.EndRow
	oGLCellAcct = oGLSheet.getCellByPosition(COLACCT, lSumRow)
	sFormula = oGLCellAcct.String			'apos;// just for kicks
	
ErrLogSetSheet(oGLSheet.RangeAddress.Sheet)
ErrLogSetCellInfo(COLACCT, lSumRow)
	'apos;// oGLCellDebit contains last Debit value; non-empty 
	'apos;// bMoveDebits would be true, so pass the opposite
	'apos;// to SetSumFormula..
	oSumSplitRange.StartRow = poGLRange.StartRow	'apos;// 1st "quot;split"quot;	'apos;// mod061020
	oSumSplitRange.EndRow = poGLRange.EndRow		'apos;// 2nd "quot;split"quot;	'apos;// mod061020
	bOpposite = (Len(oGLCellDebit.String)=0)
	sFormula = SetSumFormula(oSumSplitRange, bOpposite)		
	oGLCellAcct.String = sFormula			'apos;// set formula in cell
	oGLCellAcct.setFormula(sFormula)
	oGLCellAcct.CellBackColor = LTGREEN
	oGLCellAcct.NumberFormat = DEC2	
	Call SetSheetDate(oSumSplitRange, MMDDYY)
	GoTo NormalExit
	
ErrHandler:
	'apos;// lCat2InsRow &amp;'apos;lt; 0 sets error code.
	'apos;//*-----------------restore error handling for caller-----------
	'apos;// restore entry error settings.
	ErrLogSetModule(sErrCallerMod)
	ErrLogSetSheet(iErrSheetIx)
	ErrLogSetCellInfo(lErrColumn, lErrRow)
	'apos;//*----------end error handling restore----------------------------

	iRetValue = lCat2InsRow
	
NormalExit:
	'apos;// restore caller error handling.
	
	SplitOutCOAs = iRetValue


end function 	'apos;// end SplitOutCOAs	8/22/22.
'apos;/**/

'apos;// StoreTrans.bas
'apos;//---------------------------------------------------------------
'apos;// StoreTrans - Store double-entry transaction.
'apos;//		6/11/20.	wmk.	21:30
'apos;//---------------------------------------------------------------

public function StoreTrans(poGLRange as Object, poCat1Range as Object,_
			poCat2Range as Object, psAcct1 As String,_
					psAcct2 As String) as Integer

'apos;//	Usage.	iVal = StoreTrans(oGLRange, oCat1Range, oCat2Range,
'apos;//							sAcct1, sAcct2)
'apos;//
'apos;//		oGLRange = RangeAddress of double-entry transaction
'apos;//		oCat1Range = RangeAddress of COA sheet for 1st line
'apos;//		oCat2Range = RangeAddress of COA sheet for 2nd line
'apos;//		sAcct1 = COA account number for line 1 of transaction
'apos;//		sAcct2 = COA account number for line 2 of transaction
'apos;//
'apos;// Entry.	oGLRange.Sheet = sheet index for GL 2-line transaction
'apos;//					.StartRow = row index of 1st line of transaction
'apos;//			oCat1Range.Sheet = sheet index for COA of line 1
'apos;//					  .StartRow = insertion row index for new line
'apos;//			oCat2Range.Sheet = sheet index for COA of line 2
'apos;//					  .StartRow = insertion row index for new line
'apos;//			sAcct1 may equal sAcct2 if double entry is for only one
'apos;//				COA account
'apos;//
'apos;//	Exit.	iVal = 0 - no error occurred during insertion of new lines
'apos;//					into both COA account sheets
'apos;//
'apos;// Calls.	&amp;'apos;lt;other subs/functions called&amp;'apos;gt;
'apos;//
'apos;//	Modification history.
'apos;//	---------------------
'apos;//	6/2/20.		wmk.	original code; stub
'apos;//	6/2/20.		wmk.	code transported in from PlaceTransM and
'apos;//						modified
'apos;//	6/6/20.		wmk.	runtime bugs fixed; SetSheetDate called for changes
'apos;//						to COA sheets
'apos;//	6/11/20.	wmk.	bug fix where stray dValue used instead of
'apos;//						dSetValue when debit/credit fields blank
'apos;//                     in swapped rows
'apos;//	Notes.


'apos;//	constants.

'apos;//	local variables.
dim iRetValue As Integer
dim oDoc As Object				'apos;// This Component
dim oSheets As Object			'apos;// Doc.getSheets()
dim oGLSheet As Object			'apos;// ledger sheet
dim oCat1Sheet As Object		'apos;// COA sheet for line 1
dim oCat2Sheet As Object		'apos;// COA sheet for line 2
dim iSheetIx As Integer			'apos;// ledger sheet index
dim iCat1Ix As Integer			'apos;//	COA line 1 sheet index
dim iCat2Ix As Integer			'apos;// COA line 2 sheet index
dim lGLRow As Long				'apos;// ledger row

'apos;//	ledger access variables.
dim oCellDate As Object		'apos;// ledger date field
dim oCellTrans As Object	'apos;// transaction field
dim oCellDebit As Object	'apos;// Debit field	
dim oCellCredit As Object	'apos;// Credit field
dim oCellAcct As Object		'apos;// COA field
dim oCellRef As Object		'apos;// reference field
dim sRef as String			'apos;// reference string
dim sTrans As String		'apos;// transaction text string
dim dSetValue As Double		'apos;// transaction value to set
dim iNumberFormat As Integer	'apos;// transaction number format
dim sAcct As String			'apos;// line 1 COA
dim sAcctB As String		'apos;// line 2 COA

'apos;//	COA transaction 1st line access variables.
dim oCat1Date As Object		'apos;// ledger date field
dim oCat1Trans As Object	'apos;// transaction field
dim oCat1Debit As Object	'apos;// Debit field	
dim oCat1Credit As Object	'apos;// Credit field
dim oCat1Acct As Object		'apos;// COA field
dim oCat1Ref As Object		'apos;// reference field

'apos;// COA transaction 2nd line access variables.
dim oCat2Date As Object		'apos;// ledger date field
dim oCat2Trans As Object	'apos;// transaction field
dim oCat2Debit As Object	'apos;// Debit field	
dim oCat2Credit As Object	'apos;// Credit field
dim oCat2Acct As Object		'apos;// COA field
dim oCat2Ref As Object		'apos;// reference field


	'apos;// code.
	
	iRetValue = 0
	
	StoreTrans = iRetValue

'apos;	if true then
'apos;		Exit Function
'apos;	endif
	
'apos;//-----------------StoreTrans code begins here
'apos;dim oCat1Date as Object		'apos;// date cell from accounting category 1 sheet
'apos;dim oCat2Date as Object		'apos;// date cell from accounting category 2 sheet
'apos;const LTGREEN=10092390			'apos;// light green background color
'apos;// StoreTrans - Store transactions in inserted rows
'apos;// parameters: oGLRange, oCat1Range, oCat2Range, Acct1, sAcct2
'apos;// Entry conditions.
'apos;// Entry.	oGLRange.Sheet = sheet index for GL 2-line transaction
'apos;//					.StartRow = row index of 1st line of transaction
'apos;//			oCat1Range.Sheet = sheet index for COA of line 1
'apos;//					  .StartRow = insertion row index for new line
'apos;//			oCat2Range.Sheet = sheet index for COA of line 2
'apos;//					  .StartRow = insertion row index for new line
'apos;//			sAcct1 may equal sAcct2 if double entry is for only one
'apos;//				COA account
'apos;//'apos;//	lCat1Sheet = COA sheet for sAcct1
'apos;// lCat2Sheet = COA sheet for sAcct2
'apos;//	lCat1InsRow = insert fow for lCat1Sheet
'apos;// lCat2InsRow = insert row for lCat2Sheet
'apos;//	oCellDate = Date cell information from 1st ledger liner
'apos;// sTrans = transaction description field from ledger
'apos;//	oCellDebit = Debit cell information from 1st ledger line
'apos;//	oCellDebitB = Debit cell information from 2nd ledger line
'apos;// oCellCredit = Credit cell information from 1st ledger line
'apos;// oCellCreditB = Credit cell inromation from 2nd ledger line
'apos;// oCellAcct = COA # from 1st ledger line
'apos;// oCellAcctB = COA # from 2nd ledger line
'apos;// oCellRef = Reference field from 1st ledger line
'apos;// oCellRefB = Reference field from 2nd ledger line

'apos;// Notes: sAcct, sAcctB were set above 
'apos;//	the transaction dates had to match, so are the same
'apos;// the transaction descriptions had to match, so are the same
'apos;// the Debit and Credit amounts had to match, so are the same
'apos;// the Reference fields can be assumed to be the same
'apos;// the COA#s are different, but have been preserved in sAcct, sAcctB
'apos;// passed parameter is the poGLRange which has all the information
'apos;// necessary to acces the first transaction row, from which
'apos;// all date except the COAs can be extracted and duplicated in both
'apos;// COA sheet entries
'apos;// setup code can be this..
'apos;	oDoc = ThisComponent
'apos;	oSheets = oDoc.getSheets()
'apos;	iSheetIx = oGLRange.Sheet
'apos;	lGLRow = oGLRange.StartRow
'apos;	oGLSheet = oSheets.getByPosition(iSheetIx)
'apos;	oCellDate = oGLSheet.getCellByPosition(COLDATE, lGLRow)
'apos;	oCellTrans = oGLSheet.getCellByPosition(COLTRANS, lGLRow)
'apos;	oCellDebit = oGLSheet.getCellByPosition(COLDEBIT, lGLRow)
'apos;	'apos;// Debit and Credit are same values; balanced transaction
'apos;	oCellRef = oGLSheet.getCellByPosition(COLREF, lGLRow)

	'apos;// copy account strings to local vars
	sAcct = psAcct1			'apos;// 1st COA
	sAcctB = psAcct2		'apos;// 2nd COA
	
	'apos;// set up access to all relevant sheets
	oDoc = ThisComponent
	oSheets = oDoc.getSheets()
	iSheetIx = poGLRange.Sheet
	iCat1Ix = poCat1Range.Sheet
	iCat2Ix = poCat2Range.Sheet
	oGLSheet = oSheets.getByIndex(iSheetIx)
	oCat1Sheet = oSheets.getByIndex(iCat1Ix)
	oCat2Sheet = oSheets.getByIndex(iCat2Ix)
	
	'apos;// set up get/insert information for each COA sheet
	lGLRow = poGLRange.StartRow
dim lCat1InsRow As Long
dim lCat2InsRow As Long
	lCat1InsRow = poCat1Range.StartRow
	lCat2InsRow = poCat2Range.StartRow
	
	if iCat1Ix = iCat2Ix then
	
		'apos;// if same sheet, adjust the insert row of the highest COA by 1
		'apos;// only if the lower COA is in the first line of the transaction
		'apos;// since the row number of the second line of the transaction
		'apos;// will change by one when the lower COA inserts its line

		if StrComp(sAcct, sAcctB) &amp;'apos;lt;= 0 then									'apos;// mod051920
			lCat2InsRow = lCat2InsRow + 1									'apos;// mod051920
		endif																'apos;// mod051920
		
	endif	'apos;// end same sheet for both COAs conditional	

	'apos;// get row index of 1st account sheet for insertion
	'apos;// insert new line in account sheet with 2nd COA number
	'apos;// set date field color in inserted rows to LTGREEN
	oCat1Sheet.Rows.insertByIndex(lCat1InsRow, 1)	'apos;// insert new category 1 row
	oCat2Sheet.Rows.insertByIndex(lCat2InsRow, 1)	'apos;// insert new category 2 row
	oCat1Date = oCat1Sheet.getCellByPosition(COLDATE, lCat1InsRow)
	oCat1Date.Text.CellBackColor = LTGREEN
	oCat2Date = oCat2Sheet.getCellByPosition(COLDATE, lCat2InsRow)
	oCat2Date.Text.CellBackColor = LTGREEN
		
dim iBrkPt as Integer
iBrkpt=1
		
	'apos;// extract key ledger fields for setting in new entries
	oCellDate = oGLSheet.getCellByPosition(COLDATE, lGLRow)
	oCellTrans = oGLSheet.getCellByPosition(COLTRANS, lGLRow)
	oCellDebit = oGLSheet.getCellByPosition(COLDEBIT, lGLRow)
	oCellCredit = oGLSheet.getCellByPosition(COLCREDIT, lGLRow)
	oCellAcct = oGLSheet.getCellByPosition(COLACCT, lGLRow)
	oCellRef = oGLSheet.getCellByPosition(COLREF, lGLRow)

	'apos;// set new date fields to line 1 date
	oCat1Date.setValue(oCellDate.getValue())
	oCat2Date.setValue(oCellDate.getValue())
	oCat1Date.Text.NumberFormat = oCellDate.Text.NumberFormat
	oCat2Date.Text.NumberFormat = oCellDate.Text.NumberFormat
	oCat1Date.Text.HoriJustify = RJUST		'apos;// reset alignment since row above might be diff
	oCat2Date.Text.HoriJustify = RJUST		'apos;// reset alignment since row above might be diff

iBrkpt=1

	'apos;// duplicate transaction field in both category sheets/accounts
	sTrans = oCellTrans.String
	oCat1Trans = oCat1Sheet.getCellByPosition(COLTRANS, lCat1InsRow)
	oCat2Trans = oCat2Sheet.getCellByPosition(COLTRANS, lCat2InsRow)
	oCat1Trans.String = sTrans
	oCat2Trans.String = sTrans
	oCat1Trans.Text.HoriJustify = LJUST		'apos;// left-justify since row above might be diff		'apos;// mod051620
	oCat2Trans.Text.HoriJustify = LJUST
		
iBrkpt=1

	'apos;// copy old credit and debit fields to new sheets
	oCat1Debit = oCat1Sheet.getCellByPosition(COLDEBIT, lCat1InsRow)
	oCat2Debit = oCat2Sheet.getCellByPosition(COLDEBIT, lCat2InsRow)
	oCat1Credit = oCat1Sheet.getCellByPosition(COLCREDIT, lCat1InsRow)
	oCat2Credit = oCat2Sheet.getCellByPosition(COLCREDIT, lCat2InsRow)
	
	'apos;// Test for empty strings and set accordingly to avoid setting 0 values.
	if Len(oCellDebit.Text.String) &amp;'apos;lt;&amp;'apos;gt; 0 then
		'apos;// line 1 debit is non-empty, so line cat1 Debit = line 1 debit
		'apos;//   and cat1 Credit is empty
		dSetValue = oCellDebit.getValue()
		iNumberFormat = oCellDebit.NumberFormat
		oCat1Debit.setValue(dSetValue)
		oCat1Debit.Text.NumberFormat = iNumberFormat
		oCat1Debit.Text.HoriJustify = RJUST
		oCat1Credit.String = "quot;"quot;
		'apos;// AND line 2 credit = line 1 debit; line 2 Debit is empty
		oCat2Credit.setValue(dSetValue)
		oCat2Credit.Text.NumberFormat = iNumberFormat
		oCat2Credit.HoriJustify = RJUST
		oCat2Debit.String = "quot;"quot;
	else	'apos;// line 1 debit is empty, so line2 credit should be empty
		dSetValue = oCellCredit.getValue()
		iNumberFormat = oCellCredit.NumberFormat
		oCat1Credit.setValue(dSetValue)
		oCat1Credit.Text.NumberFormat = iNumberFormat
		oCat1Credit.Text.HoriJustify = RJUST
		oCat1Debit.String = "quot;"quot;
		'apos;// and line 2 debit = line 1 credit; line 2 Credit is empty
		oCat2Debit.setValue(dSetValue)
		oCat2Debit.Text.NumberFormat = iNumberFormat
		oCat2Debit.HoriJustify = RJUST
		oCat2Credit.String = "quot;"quot;
	endif

iBrkpt=1

	'apos;// swap COA #s in transactions to xref
	oCat1Acct = oCat1Sheet.getCellByPosition(COLACCT, lCat1InsRow)
	oCat2Acct = oCat2Sheet.getCellByPosition(COLACCT, lCat2InsRow)
	
'apos;	oCellAcctB.setValue(oCellAcct.getValue())
'apos;	oCellAcctB.HoriJustify = CJUST
'apos;	oCellAcctB.NumberFormat = oAcct.NumberFormat
'apos;	oCat1Acct.SetValue(oCellAcctB.getValue())
	iNumberFormat = oCellAcct.NumberFormat
	oCat1Acct.String = sAcctB		'apos;// 2nd line account
	oCat1Acct.Text.HoriJustify = CJUST
	oCat1Acct.NumberFormat = iNumberFormat
	oCat2Acct.String = sAcct		'apos;// 1st line account
	oCat2Acct.Text.HoriJustify = CJUST
	oCat2Acct.NumberFormat = iNumberFormat
		
iBrkpt=1

'apos;// duplicate reference field and center it
	oCat1Ref = oCat1Sheet.getCellByPosition(COLREF, lCat1InsRow)
	oCat2Ref = oCat2Sheet.getCellByPosition(COLREF, lCat2InsRow)
	sRef = oCellRef.String
	oCat1Ref.String = sRef
	oCat2Ref.String = sRef
	oCat1Ref.Text.HoriJustify = CJUST
	oCat2Ref.Text.HoriJustify = CJUST

	'apos;// Update Sheets modified date stamp to indicate changes		'apos;// mod060620
	'apos;// Note: COA sheets'apos; dates updated in StoreTrans
	Call SetSheetDate(poCat1Range, MMDDYY)							'apos;// mod060620
'apos;const DATEROW=1
	oCellDate = oCat1Sheet.getCellByPosition(COLDEBIT, DATEROW)		'apos;// mod060620
	oCellDate.CellBackColor = LTGREEN
	Call SetSheetDate(poCat2Range, MMDDYY)							'apos;// mod060620
	oCellDate = oCat2Sheet.getCellByPosition(COLDEBIT, DATEROW)		'apos;// mod060620
	oCellDate.CellBackColor = LTGREEN

	StoreTrans = iRetValue

end function 	'apos;// end StoreTrans	6/11/20
'apos;/**/