My Basic journal

Sim City 4 - Cities

2008-10-06T09:24:00.000-07:00

Farm communities

The first city in a region often starts out as a farm community. But, as education levels increase, demand for farmland decreases. To keep the city a farm community, start zoning as low density residential and low density commercial. Because of the low density, there is no need to go beyond windmills and water towers. The more educated citizens will commute to other cities for better employment possibilities.

Bedroom communities

A bedroom community provides employees for the other cities in the region. It's probably the hardest one to make money on. Sims need a lot of services to be happy. To keep the traffic down, you will need transportation. At a minimum, you will need bus stops. Though, I like to do without bus service and just concentrate on trains and subways. As the city and region grow, you will also need passenger train service to industrial cities and subway service to commercial cities. A few commercial areas within the city will help keep the Sims happy -- and help generate money.

Purely industrial or commercial cities

These are the easiest to make money with because they don't need services such as hospitals and schools. In fact, these cities don't even need a police department since there are no Sims living there to commit crimes. The cities don't even need a fire department. When a fire start, you can plunk down a firehouse near the flames and then demolish it when the flames are out. (You don't even need a road for the fire engine to travel along.)

Mega cities

To get all or most of the rewards possible in the game, you need a city with residential, commercial, and industrial areas. When planning to develop a mega city, I start with farms and high (or medium) density residential and commercial areas. (Tall buildings won't develop without water, and so I don't see much need to start with low density zoning.) I place the first residential/commercial area in a corner of the city. Then I establish a train link to what will become an industrial city.
The industrial city can provide electricity (a coal-burning plant), garbage disposal (a waste-to-energy plant set at zero electricity production), and jobs for lower skilled workers. The residential city can provide water for the industrial city when it is time to water the city. (Dirty industry and farms produce water pollution which stops water pumps from working.)
As the city grows, you will get some beneficial rewards such as the mayor's house, a farmer's market, the state fair, houses of worship, private schools, and cemeteries. Then as the city grows and the citizenry become more educated, you'll want to dezone farmland and build more residential and commercial areas. When the demand meter shows that the only type of industry wanted is high tech, it's time to start building non-farm industrial areas.
I like to start off by building along the edges of the city map with farmland in the center of the map. When the farmland is dezoned, it provides a perfect place for a city center.
Hold off on a lot of the rewards. I would suggest waiting before building such rewards as the country club, city hall, university, and tourist trap -- either because of the monthly cost or the need for nearby police. (It seems that the larger an area a reward takes up, the more need it has for police.)
Also, hold off on establishing a purely commercial city bordering the mega city until your commercial areas are well developed.
When you do start watering the city and zoning for high density development, it will be time to demolish small police stations and build large ones where they can have the best coverage. You'll probably also want to move the library and museum. (I'd suggest near a college -- for ease in keeping track of them.) You'll need the central space for larger schools for your growing population.

Some notes about transportation:

I'm ambivalent about bus stops. Sometimes I think they cause more traffic congestion than they alleviate. For smaller cities, if you decide to use buses, place bus stops along heavily traveled routes to relieve congestion. For larger cities, place bus stops (or, better, subway stops) near residential skyscrapers and also work places. To get a new residential section to develop, place a bus stop near the residential zones. (People will hop off the bus and decide to build a house.)
I think that passenger train stations and the like do more than bus stops do to alleviate congestion. Passenger train stations also provide taxi service.
When choosing an airport to build, choose one that is right for the size of your city. If it's too large, it will drain your finances. If it's too small, it won't develop properly.

Sim City 4 - Zones

2008-10-04T07:45:00.000-07:00

Residential

Residential areas need schools (elementary and high school) plus health care (large medical center). The best place to put them is in the center of each residential area. When the city is still small, it's also a good idea to put a library, museum, fire department, and police department in the center as well. This allows you to find and check them easily. But, of course, don't add all of these services at the start. (I usually just start out a residential neighborhood with an elementary school, museum and library -- set to the minimum levels. The museum will provide buses for the school -- even if the school is set to minimum coverage area.)
This diagram shows how a completed residential area might look. The light green areas are areas that could be zoned for residential development, if watered, but left open or as parks if not. It also shows how services can be placed in one location.
Residential areas also need a lot of open areas. 1x6 open areas can be used to divide traffic flow and also to provide space for possible bus stops, subway stops, or parks/recreational areas. Larger open areas are great for a college, softball field, large police station (when needed), and house of worship.
Do not put residential zones next to the edge of the map. Sims do not like to live near traffic. It causes noise and air pollution. And there will be a lot of traffic going from one zone to another using the shortest possible route. The first money-making city I made had residential zones next to the edge of the map. I got so frustrated at their poor development that first I tried to torch them (which didn't work because of the smoke detector ordinance) and then I tried a meteor shower.

Commercial

Commercial areas need open spaces as much as residential areas do. Smaller open areas can be used for statues and small plazas. Larger ones can be used for plazas or rewards such as a farmer's market. Even larger ones can be used for larger rewards.
It's better not to put commercial areas right next to residential ones. There should be a buffer between the two. Though, it is nice if there are some within easy walking distance. There should also be a buffer between certain commercial rewards and residential zones.
I like to put passenger train stations in those commercial areas near residential areas. It helps increase traffic to stores near the stations.

Farm

Farms are important when a city is just starting out. They employ a lot of minimally educated people. Plus they provide one of the nicer rewards, in my opinion, the farmer's market.
They should be positioned away from commercial and residential areas. Commercial areas do not like being next to farms. Residential areas will develop well adjacent to farms but will soon complain about water pollution. Farms can be placed on slopes.
It's not clear to me whether large or medium or small farms are better. Although farmland is cheap to dezone, farm buildings are less so. However, larger farms seem to produce more water pollution.
It's possible to create a giant-sized farm with no interior roads by pausing the game during its creation. Zone for farmland to just before a road would appear. Then zone more farmland next to the original zone, etc. Unpause when the farm is large enough. (There is an upper limit on how large a farm can be made and still develop.) However, I recommend the "trick" detailed for low-tech industry such as farms.
For a large city, you'll want to dezone farmland until there are no farms left. For a small town, you'll probably want to keep the farms.
For the original version of the game, a town size of 3000 and farm population of 120 will give you a farmer's market, and a town size of 3500 and farm population of 240 will give you a state fair. (These numbers are different from the numbers supplied by the game. They are based on experience. However, for the deluxe version, the numbers are as supplied by the game.) Both the farmer's market and state fair should be placed with commercial zones. The farmer's market can be placed next to a residential zone, but the state fair should be quite a few squares away from any residential zones. (One web page said at least 17 squares away.)

Non-Farm Industrial

In general, these areas should be in their own separate city. Dirty industry produces a lot of air and water pollution. The pollution produced is so high that tech will only locate at the edges of these industrial areas. Farms also will not get settled when pollution is high. Trees and parks will help with pollution to a small extent.
It's also a good idea to build train tracks through industrial zones and connected to neighboring cities. Freight trains will appear even without freight stations.
For my first few cities I built bus stops when traffic became a problem. However, I like the "trick" I read about at simtropolis.com for zoning industry. Build a train track through the area you want to zone. Add a 3-square-long road that crosses the railroad tracks - as in the diagram at the right. If the area is zoned for farms, 4 farmhouses will develop on the road. If the area is zoned for other industry, a factory will develop at a corner next to the road. Then the rest of the zoned area will develop the same type of industry. There is no need to build passenger train stations at the crossing. Evidently, people just jump off the train as it slows for the road crossing. ;)
High-tech industry can be placed in a regular city. When a residential city is well educated, the demand meter for that city and possibly neighboring cities will show demand for only high tech industry. Add industrial zones to a residential city gradually in the same types of places you would commercial zones. Be sure to add open areas and flower gardens.

Sim City 4 - Walk through

2008-10-01T08:59:00.000-07:00

Basics

In SimCity 4, it is possible to create a city that starts making money starting at the end of the second month. There are a few tricks to this.

Legalize gambling. It gives you an extra §100 per month.
Don’t build too many streets/roads during the first few months. Their upkeep can make a difference between a loss and a profit for the city during these months.
Start with a windmill instead of a big power plant. Windmills provide electricity at a lower cost than a natural gas power plant – up to the power provided by 4 windmills.
Don’t add services until needed. Starting with a library and museum is fine. Together they cost less than §10 per month. Add an elementary school when you can afford the cost. Then add a high school, large medical center, and a water tower or two, in that order, as you can afford them. With the Smoke Detector Program, you can wait until there is a fire before building a small fire station – and still put out the fire. I usually end up adding a college before a fire department. Add a small police department or two when you have the money. Police and fire departments help attract business.
Don’t be afraid of farmland. Farms are good employers for startup cities.
Utilize neighbor cities. When Sims complain about garbage, build a connecting city, an industrial city, with a waste to energy plant. Set the energy production for the plant to zero, but accept garbage from the first city. Set the garbage import rate to maximum. When you return to the original city, set the garbage export rate to about half of the maximum. Later, have your original city export water to the industrial one.
Do urban renewal. Dezone farmland that is no longer needed. Demolish buildings (like used car dealerships) that create a nuisance. Change or add streets to help traffic flow. Demolish buildings that have passed their prime, that no longer have any Sims in them -- provided you have the money to do so.
Micromanage. Set the coverage area low for schools and medical centers but not fire or police departments. Check often whether you have enough desks (schools), beds (medical centers), books (libraries), tickets (museums), and cells (police departments). As a side note, if teachers or health care workers go on strike, it's cheaper to bulldoze the building and replace it with another than to pay the increased costs to end the strike.

--------------------------------------------------------------------------------

The First Three Months

Month 1: Pause. While in God Mode, use a smoothing or feathering tool to smooth out the terrain. If you want to add a beach or dock to your future city, be sure to smooth the beach area so that there are sections of beach that are aligned with the grid. Add forests. Go to Mayor Mode to start the city and give it a name.

Keep the game paused. Lay out a minimal residential area. (For a first city, zone for moderate residential and commercial areas.) Add one windmill set at §50, a few streets, and small commercial areas at one or more corners. Leave an empty area at the center of the development for future services (such as education, health care, and safety). Add a farm set away from the town and connected to the town by a road or two and a power line. Lay out connections to two neighboring cities. Unpause.

Month 2: Pause. Open the city ordinances box and choose to legalize gambling. Also, choose the Smoke Detector Program and Clean Air Act. Unpause.

Month 3: Add more streets, residential areas, and/or farmland as needed. Add a library and museum. Set their funding low (less than §10 between the two).

At the right is a sample town plan:

--------------------------------------------------------------------------------

Utilities

Electricity

Start residential and commercial cities with 1 windmill. Add more windmills as the population grows. However, instead of adding a fifth windmill, build a natural gas power plant in a corner of the city and decrease the funding for the windmills to zero. There is no need to demolish them. (Note: Windmills produce no pollution. Natural gas power plants produce minimal water pollution.)
Start industrial cities with 2 windmills at full power plus a waste to energy plant at zero power. When demand increases, build a coal plant in a corner of the city and decrease the funding for the windmills to zero. (Note: Coal plants produce a lot of air and water pollution, but they produce a lot of electricity at a cheap rate. It's best to keep them away from everything -- except the corner of the map.)

Water

Water is the main reason for choosing a system of streets or empty land every 7th square both lengthwise and crosswise. Pipes provide water for buildings up to 6 squares away. An optimal way of laying pipes would be to have one crosswise pipe (under a road or empty land) and then lengthwise pipes every 14th square. (The roads with no pipes under them would be unwatered.)
Startup cities do not need water or water pipes. They just won't develop beyond the light development stage. Once you do add water, be prepared for a burst of activity. Whether it is better to start with water towers or water pumps depends on the size of the city at the time they are added. It's best to keep water funding near maximum to keep pipes from bursting.
Four water towers at full capacity provide water for a lower cost than one water pump at the equivalent partial capacity. Note also that all water resources need to be run at the same capacity. So, water towers should be demolished before building the first water pump. Just as power plants need to be periodically replaced as they age, so do water towers and water pumps.
Finally, industrial cities and cities in which you decide to keep farmland will eventually need a water treatment plant. Commercial areas and high-tech areas do not like water pollution. Plus, water pumps and towers will stop working if water pollution near them becomes too high.

Garbage

Garbage is best taken care of by a waste to energy plant (set at zero electricity production) in a completely industrial city. Since there is nobody living in the city, there is no one to complain about the pollution. However, for the health of the industry in the city, locate the plant a short distance away from the main industrial areas.
For large residential cities, recycling centers are nice but not essential -- as long as garbage is taken care of in some way.
If for some reason you like vultures (I think that's what they are), create a landfill.

Sim City 4

2008-10-01T08:49:00.000-07:00

Since aol Journals and Home Spaces are shutting down, I'm moving some pages I created about Sim City 4 over to here.

The first page of these pages has hints on starting a city, including a walk through for the first 3 months plus some thoughts about utilities. The next page has info on zones. The final page has info on types of cities.

Links

The Region part of the official strategy guide at EA Games allowed me to create my first money-making city. The city fell apart after it reached 20,000, but I didn't need to take out any loans.
Gamespot has a lot of useful hints. Scroll down the page.
Simtropolis.com has many more hints plus a link to a very active Sim City bulletin board.

printing

2008-02-18T09:02:00.000-08:00

Here is the basics of a print macro that will print successive pages in turn. Use the "end of used area"-Cursor to print just the used area (bounded by blank rows and columns). Use the "gotoend"-Cursor to print the entire sheet (all the cells with entries plus, of course, the blank rows and columns). It prints the range from oCell (here, the top left cell on the page) to the lower right edge of oCursor.

Sub printpage
oSheets = ThisComponent.sheets

for j = 0 to oSheets.count() - 1
oSheet = oSheets.getbyindex(j)
oCell = oSheet.getcellbyposition(0,0)
oCursor = oSheet.createCursorbyrange(oCell)
oCursor.gotoEndOfUsedArea(true) ' This is a range
oCursor.gotoEnd 'This is a single cell
ThisComponent.getCurrentController.select(oCursor)

answer = msgbox("Do you want to print this sheet?",3,"Print sheet?")
if answer = 3 then'cancel
exit sub
elseif answer = 6 then'yes
oPrintArea(0).StartColumn = oCell.rangeaddress.StartColumn
oPrintArea(0).EndColumn = oCell.rangeaddress.EndColumn
oPrintArea(0).StartRow = oCursor.rangeaddress.StartRow
oPrintArea(0).EndRow = oCursor.rangeaddress.EndRow
oSheet.setPrintAreas(oPrintArea())
thisComponent.Print(Array())
end if

next
end sub

where macros and dialogs are kept

2007-09-14T11:38:00.000-07:00

I just discovered that my version wasn't keeping my macros where I thought. After looking and looking (Search didn't work), I finally looked at Tools/Options/OpenOffice.Org/Paths/Basic.

finally, redid this

2007-09-11T10:28:00.000-07:00

This took me a long time to recreate. But, I think it's finally ok.
Sub Lines(tfield, tname2, tbroker2, tshares2, tbasis2, xshares, xbasis, ten())'----------------Lines----------------
dim clrw(3)
dim tots(9)'running totals

tshares1 = val(ten(0))'shs added to tname1, may be negative
tbasis1 = val(ten(9))'basis added to tname1, may be negative
if ten(6) = "exchange" or ten(6) = "transfer" then tbasis1 = 0
'for transfer and exchange, tbasis1 and tbasis2 start off at zero
'tbasis1 = - tbasis2 are calculated here

if val(tshares2) <> 0 then two = -1 else two = 0

if tfield = "stock" then
trange = "Trades"
trange2 = "CTrades"
tsheet = "Stock"
diff1 = tshares1 - int(tshares1)
diff2 = tshares2 - int(tshares2)
if ten(6) = "exchange" or ten(6) = "merger" then diff1 = 0 : diff2 = 0
elseif tfield = "fund" then
trange = "Fund"
trange2 = "CFund"
Call NewSheet(tsheet, ten(1), -1, -1)
oCell = thiscomponent.sheets.getbyname(tsheet).getcellbyposition(0,0)
thiscomponent.getCurrentController.select(oCell)
Call Resized(trange, tsheet, "shares", 0, 1, 1)
Call Resized(trange2, tsheet, "shares", 0, 2, 1)

diff1 = 0
diff2 = 0
else
' msgbox tfield
exit sub
end if

oSheet = SheetObj(trange,tsheet,clrw())
oRange2 = oSheet.getcellrangebyname(trange2)
Call Sorting(trange,tsheet,8,1,2,-1,-1,-1,-1)

tots(0) = xshares
tots(1) = xbasis
tots(2) = tshares1 + xshares 'new share total
tots(3) = tbasis1 + xbasis 'new basis
tots(4) = diff1
if two then
tots(5) = tshares2
tots(6) = tbasis2 ' not needed or used
tots(7) = diff2
tots(8) = xbasis ' not needed or used
tots(9) = xshares
end if

n = 1
do
Next1:
r = ""
if tots(0) = 0 then exit do
if (ten(6) = "exchange" or ten(6) = "transfer") and rounding(tots(2) - tots(0),3) = 0 then exit do

answer = FindTriple(trange, tsheet, 1, r, ten(1), ten(8), 7, "", 0, n, -1)
if not answer then exit do

if ten(1) = tname2 and ten(8) = tbroker2 then n = n + 1

lshares = oSheet.getcellbyposition(0,r).value'shares in entry
lbasis = oSheet.getcellbyposition(9,r).value'basis in entry
temp1 = lshares
temp2 = lbasis
if lshares = 0 then goto Next1

if two then call CopyLine(trange, tsheet, 0, r-clrw(1), trange, tsheet, 0, r + 1-clrw(1), 0)

'deal with first line, the line with tname1 and tname2
if ten(6) = "exchange" or ten(6) = "transfer" then 'may not transfer all shares
if lshares > abs(tots(2)) then temp1 = lshares - abs(tots(2)) else temp1 = 0
temp2 = rounding(lbasis * temp1 / lshares, 3)
else
temp1 = rounding(lshares * tots(2) / tots(0), 3)
if tots(1) = 0 then
temp2 = 0
else
temp2 = rounding(lbasis * tots(3) / tots(1), 3)
end if
end if

todel = 0
if two then'copy line r to "C" stock/fund
if rounding(temp1,3) = 0 then todel = -1
if todel then 'not sure whether first few lines here are needed if line not deleted
call CopyLine(trange, tsheet, 0, r-clrw(1), trange2, tsheet, 0, 1, todel) 'may delete line
oSheet.getcellrangebyname(trange2).getcellbyposition(0,1).value = lshares - temp1
oSheet.getcellrangebyname(trange2).getcellbyposition(9,1).value = lbasis - temp2
if todel then r = r - 1
end if
end if

if not todel then'line r not deleted
oSheet.getcellbyposition(0,r).value = temp1
oSheet.getcellbyposition(9,r).value = temp2
n = n + 1
if tots(4) <> 0 and temp1 <> 0 then
temp3 = rounding(lshares * tots(4) / tots(0), 3)
call CopyLine(trange, tsheet, 0, r-clrw(1), trange, tsheet, 0, r + 1-clrw(1), 0)
n = n + 1
oSheet.getcellbyposition(0,r+1).value = temp3
oSheet.getcellbyposition(0,r).value = temp1 - temp3
oSheet.getcellbyposition(9,r+1).value = rounding(temp2 * temp3 / temp1,2)
oSheet.getcellbyposition(9,r).value = temp2 - rounding(temp2 * temp3 / temp1,2)
oSheet.getcellbyposition(6,r).string = ten(6)
oSheet.getcellbyposition(6,r+1).string = "frac." & ten(6)
tots(4) = tots(4) - temp3
r = r + 1
end if
end if

tots(0) = tots(0) - lshares
tots(1) = tots(1) - lbasis
tots(2) = tots(2) - temp1 + lshares
tots(3) = tots(3) - temp2 + lbasis

if two then
r = r + 1
if ten(5) <> 0 and tots(5) <> 0 and tots(9) <> 0 then
temp1 = rounding(lshares * tots(5) / tots(9), 3)
temp2 = lbasis - temp2
end if

oSheet.getcellbyposition(0,r).value = temp1
oSheet.getcellbyposition(9,r).value = temp2
oSheet.getcellbyposition(1,r).string = tname2
oSheet.getcellbyposition(8,r).string = tbroker2
if tots(7) <> 0 and temp1 <> 0 then
temp3 = rounding(lshares * tots(7) / tots(9), 3)
call CopyLine(trange, tsheet, 0, r-clrw(1), trange, tsheet, 0, r + 1-clrw(1), 0)
if ten(1) = tname2 and ten(8) = tbroker2 then n = n + 1
oSheet.getcellbyposition(0,r).value = temp3
oSheet.getcellbyposition(0,r+1).value = temp1 - temp3
oSheet.getcellbyposition(9,r).value = rounding(temp2 * temp3 / temp1,2)
oSheet.getcellbyposition(9,r+1).value = temp2 - rounding(temp2 * temp3 / temp1,2)
oSheet.getcellbyposition(6,r).string = "frac." & ten(6)
tots(7) = tots(7) - temp1 + temp3
end if
tots(8) = tots(8) - lbasis
tots(6) = tots(6) - temp2
tots(9) = tots(9) - lshares
tots(5) = tots(5) - temp1
end if

loop
if ten(6) = "transfer" or ten(6) = "exchange" then
tbasis2 = tots(3)
' msgbox tbasis2
' msgbox temp2
ten(9) = tots(6)
oRange = ThisComponent.Sheets.getByName("Data").getCellRangeByName("All")
thiscomponent.getcurrentcontroller.select(oRange)

oRange.getcellbyposition(9,1).value = ten(9)
' msgbox ten(9)
Call CopyLine("all", "Data", 0, 1, trange2, tsheet, 0, 1, 0)
end if
'msgbox tbasis2
'msgbox tshares2
'for j = 0 to ubound(tots)
'msgbox tots(j) & " " & j
'next

'change name of fund sheet
If tfield = "fund" And oSheet.name <> tname2 Then
temp = InputBox("What is the full name of the the ""new"" fund?" & _
Chr(10) & "OK to leave blank.")
if temp = "" then temp = tname2
oSheet.rows.insertbyindex(0,1)
oSheet.getcellbyposition(0,0).string = temp
oSheet.getcellbyposition(0,0).CharColor = 16711680 'red
oSheet.name = tname2
End If

End Sub

Arrays as subroutine arguments

2006-11-10T13:18:00.000-08:00

In the last version of VBA that I used, if one wanted to use an array as an argument in a subroutine or function, one was limited to one array which had to be the last argument. This isn't the case with ooo basic. One or more arguments can be arrays, and arrays can be placed anywhere in the argument list.

start to Main

2006-11-09T07:09:00.000-08:00

This is the start to my main macro. I'm putting it here so it's less likely for me to lose it again. It just shows different ways to get info from a dialog.

dim ten(9)
oDSheet = ThisComponent.Sheets.getbyName("Data")
oDRange = ThisComponent.Sheets.getByName("Data").getCellbyposition(0,0)
ThisComponent.getCurrentController.select(oDRange)

with oDialog1
tfield = .getcontrol("ListField").getselecteditem()
dsn = .getcontrol("Label1").text
if .getcontrol("CheckNegAdj").state then
n = right(dsn,1)
if n = "1" then n = "0" else n = "1"
dsn = left(dsn,2) & n
end if
PPS = .getcontrol("NumPPS").value

ten(0) = .getcontrol("NumShares").value
if mid(dsn,2,1) then
if ten(0) = 0 then msgbox "number of shares?" : exit sub
if right(dsn,1) = "1" and ten(0) > 0 then ten(0) = 0 - ten(0)
if right(dsn,1) = "0" and ten(0) < 0 then ten(0) = 0 - ten(0)
else
ten(0) = ""
end if
ten(1) = .getcontrol("ComboName").text
if ten(1) = "" then
if .getcontrol("CheckChange").state then
answer = msgbox("Is this a broker name change?",4,"name change/transfer")
if answer <> 6 then msgbox tfield & " name?" : exit sub
else
msgbox tfield & " name?" : exit sub
end if
end if
ten(2) = .getcontrol("DateField1").text
if not isdate(ten(2)) then msgbox "date is not date" : exit sub
if datediff("d",ten(2),now) < 0 then msgbox "date is in future" : exit sub
date1 = ten(2)
ten(3) = year(ten(2))
ten(4) = choose(month(ten(2)),1,1,1,2,2,2,3,3,3,4,4,4)
if month(ten(2)) = 1 and day(ten(2)) < 5 then
answer = MsgBox("Should this be reported as previous year income?", 4, "Reportable")
If answer = 6 Then
ten(3) = ten(3) - 1
ten(4) = 4
ten(7) = "reportable " & ten(3) & " " & ten(7)
End If
end if
ten(5) = .getcontrol("NumCash").value
if left(dsn,1) then
if ten(5) = 0 then msgbox "cash amount?" : exit sub
if right(dsn,1) = "1" and ten(5) > 0 then ten(5) = 0 - ten(5)
if right(dsn,1) = "0" and ten(5) < 0 then ten(5) = 0 - ten(5)
else
ten(5) = ""
end if
ten(6) = .getcontrol("ComboType").text
if .getcontrol("CheckPPS").state then ten(6) = "PPS"
if .getcontrol("CheckLieu").state then ten(6) = "cash in lieu"
if tfield = "cash" and ten(6) = "div" then
temp = msgbox("Is this a mm div?",4,"mm div?")
if temp = 6 then ten(6) = "mm div"
if ten(6) = "buy" then ten(6) = "dep"
if ten(6) = "sell" then ten(6) = "wth"
end if
if ten(6) = "" then msgbox "transaction?" : exit sub
if .getcontrol("CheckNonTax").state then
ten(6) = "non " & ten(6)
temp = msgbox("Is this reportable?",4 + 256,"Reportable?")
if temp = 7 then ten(6) = "n " & ten(6)
end if
if .getcontrol("CheckChange").state then
ten(6) = "change"
else
if .getcontrol("CheckCorrection").state then
oldtype = ten(6)
ten(6) = .getcontrol("ComboCorrection").text
if ten(6) = "" then msgbox "new transaction type?" : exit sub
end if
end if

ten(7) = .getcontrol("TextComments").text
if .getcontrol("CheckNegAdj").state then ten(7) = "Correction--" & ten(7)
ten(8) = .getcontrol("ComboBroker").text
ten(9) = ""
if val(ten(5)) <> 0 then
if tfield = "cash" then
if ten(6) = "dep" or ten(6) = "wth" then ten(0) = ten(5)
if .getcontrol("CheckReinv").state then ten(0) = ten(5)
end if
if val(ten(0)) <> 0 then
ten(9) = ten(5)
if PPS = 0 then PPS = Rounding(ten(5)/ten(0),3)
end if
end if
end with

revised Search

2006-10-12T07:39:00.000-07:00

Here is the Search function from before, revised to include the macro SheetObj. SheetObj and SheetName are in other posts here. (Both the start and the end of the macro have been changed, the latter for date searches.)

Function FindTriple(trange, tsheet, c, r, tname, tname2, offset2, tname3, offset3, n, go)
'if c (or r) is a number, look for nth occurence in column c (or row r) of trange on tsheet
'with tname2 offset offset2 cols and tname3 offset offset3 cols
'columns and rows start at 0 ... c and r are initially range (not sheet) references
'before calling function, set r (or c) to "" to search all rows (or all columns)
'returns sheet (not range) col c and row r where found, or if not found first cell of searched range,
'or ... col and row of last cell where found (for n > 1)
'trange can be chosen as "" to search entire sheet or an entire column or row
'if both trange and tsheet are "", current selection is searched
'tname2 or tname3 can be chosen as "" for simpler searches

FindTriple = 0
oDoc = ThisComponent
dim clrw(3)
oSheet = SheetObj(trange,tsheet,clrw())

if isnumeric(c) then clrw(0) = clrw(0) + c : clrw(2) = clrw(0)'to search in only one column
if isnumeric(r) then clrw(1) = clrw(1) + r : clrw(3) = clrw(1)'to search in only one row
oRange = oSheet.getcellrangebyposition(clrw(0), clrw(1), clrw(2), clrw(3))
oCell = oSheet.getcellbyposition(clrw(0), clrw(1))
c = clrw(0)'change from range coordinates to sheet coordinates
r = clrw(1)

k = 0
if tname = oCell.string then gosub FoundOne

xSearchD = oRange.createSearchDescriptor()
With xSearchD
.SearchString = tname
.SearchCaseSensitive = false
.SearchWords = true 'forces the entire cell to contain only the search string
End With

do
xFound = oCell
xFound = oRange.findNext(xFound, xSearchD)
if IsNull(xFound) then exit do
oCell = xFound
gosub FoundOne
loop
exit function

FoundOne:
c = oCell.rangeaddress.startcolumn
r = oCell.rangeaddress.startrow
temp2 = oSheet.getcellbyposition(c + offset2,r).string
temp3 = oSheet.getcellbyposition(c + offset3,r).string

if len(tname2) > 2 and temp2 <> tname2 then return

if len(tname3) < 2 then
k = k + 1
else
if isdate(tname3) and isdate(temp3) then
temp = DateDiff("d",tname3,temp3)
if temp > 1 and temp < 5 then k = k + 1
else
if temp3 = tname3 then k = k + 1
end if
end if
If k = n Then
FindTriple = -1
If go Then
oCell = oSheet.getcellbyposition(c,r)
oDoc.getCurrentController.select(oCell)
End If
exit function
End If
Return
End Function

Open dialog

2006-10-10T08:04:00.000-07:00

From my limited experience, it seems that all the macros that refer to a given dialog have to be on the same page (ie the same basic module). A library can have quite a few other basic module (pages). Mine has modules named: Main, Merger, NewYear, Place, Refresh, Search, SimCity, SSum, and Taxes. (The macro in the SimCity module is to sort and reformat a range containing info on rewards and other plop-able "buildings" in SimCity4.) There is also a dialog module in the library called Dialog1. Here's the beginning of the Main module with a macro which opens a dialog with some hidden controls and a combo box populated with elements from a range in a document. The library itself is called "Accounts".

Dim oDialog1 as Object
dim oControl as Object
dim oDoc as Variant

Sub Dialog1Show '---------------open Dialog1, populate ComboBroker----------------------
oDoc = ThisComponent
DialogLibraries.LoadLibrary( "Standard" )
oDialog1 = CreateUnoDialog( DialogLibraries.Accounts.Dialog1 )
oControl = oDialog1.getcontrol("ComboBroker")

oSheet = ThisComponent.Sheets.getbyname("Misc")
oRange = oSheet.getcellrangebyname("Brokers")

for j = 1 to oRange.rows.count -1'first line is column heading
temp = oRange.getcellbyposition(0,j).string
oControl.additem(temp,0)
next

'make option buttons invisible
for j = 1 to 5
oDialog1.getcontrol("OptionButton" & j).setvisible(0)
next
oDialog1.getcontrol("ComboCorrection").setvisible(0)

oDialog1.Execute()

End Sub

Sheet object

2006-10-09T07:46:00.000-07:00

After I found myself copying the first few lines of the search macro into another macro, I decided to make them into their own macro.

Function SheetObj(trange,tsheet,clrw())
'trange can be chosen as "" to get entire sheet and coordinates for entire sheet, not just range on sheet
'if both trange and tsheet are "", current selection is searched
'do not "dim clrw(3)"here, it's equivalent to putting redim clrw(3) at the end of the macro
oSheets = ThisComponent.sheets
oRanges = ThisComponent.namedranges
if oSheets.hasbyname(tsheet) then
SheetObj = oSheets.getbyname(tsheet)
oRange = SheetObj'changed later if trange <> ""
else
if oRanges.hasbyname(trange) then
tsheet = SheetName(trange,0)
SheetObj = oSheets.getbyname(tsheet)
else
SheetObj = ThisComponent.CurrentController.getActiveSheet
oRange = ThisComponent.getcurrentselection()
end if
end if

if oRanges.hasbyname(trange) and trange <> "" then oRange = SheetObj.getcellrangebyname(trange)
if isnull(oRange) then oRange = SheetObj

with oRange.rangeaddress
clrw(0) = .startcolumn
clrw(1) = .startrow
clrw(2) = .endcolumn
clrw(3) = .endrow
end with
end function

Find number in string; also Sort

2006-10-06T14:02:00.000-07:00

The first macro finds the first number (not a single digit but an entire number) in a string. The second sorts a range which may or may not have a header.

Function FindNum(ttext)
'returns the first number expression in ttext
n = 1
Do While n <> " " then exit do
n = n + 1
Loop
FindNum = Val(Mid(ttext, n))
end Function

Sub Sorting(trange,tsheet,c1,c2,c3,dir1,dir2,dir3,header)
myarray1 = Array(c1,c2,c3)'range cols start w 0 as the first one
myarray2 = Array(dir1,dir2,dir3)'dir = true for ascending or false otherwise
n = -isnumeric(c1) - isnumeric(c2) - isnumeric(c3)
if n = 0 then exit sub
Dim aSortFields(n) as New com.sun.star.util.SortField
Dim aSortDesc(1) as New com.sun.star.beans.PropertyValue

oSheet = ThisComponent.Sheets.getByName(tsheet)
oRange = oSheet.getCellRangeByName(trange)

for j = 0 to n-1
aSortFields(j).Field = myarray1(j)
aSortFields(j).SortAscending = myarray2(j)
next

aSortDesc(0).Name = "SortFields"
aSortDesc(0).Value = aSortFields()
aSortDesc(1).Name = "ContainsHeader"
aSortDesc(1).Value = header

oRange.Sort(aSortDesc())
End Sub

Current / used area

2006-10-04T07:57:00.000-07:00

a very short macro

Sub FindRange(tsheet, c1, r1, c2, r2, ttype)
'finds end of either current or used area
oSheet = ThisComponent.sheets.getbyname(tsheet)
oCell = oSheet.getcellbyposition(c1,r1)
oCursor = oSheet.createCursorbyrange(oCell)

if ttype = "current" then
oCursor.gotoEnd
elseif ttype = "used" then
oCursor.gotoEndOfUsedArea(true)
end if

c2 = oCursor.rangeaddress.endcolumn
r2 = oCursor.rangeaddress.endrow
End Sub

Search

2006-10-03T07:35:00.000-07:00

This macro searches records in a range to find a record that matches up to 3 criteria. The function itself returns true or false. If the first criteria one wants to match is in range column 0, for example, before calling the function, set c = 0 and r = "". If the record is found, the sheet column and row of the found record will be returned as well. This also does simple searches. (FindSheet is a function. It returns the name of a sheet that a given range is on.)

Function FindTriple(trange, tsheet, c, r, tname, tname2, offset2, tname3, offset3, n, go) as Boolean
'if c (or r) is a number, look for nth occurence in column c (or row r) of trange on tsheet
'with tname2 offset offset2 cols and tname3 offset offset3 cols
'columns and rows start at 0 ... c and r are initially range (not sheet) references
'before calling function, set r (or c) to "" to search all rows (or all columns)
'returns sheet (not range) col c and row r where found, or if not found first cell of searched range,
'or ... col and row of last cell where found (for n > 1)
'trange can be chosen as "" to search entire sheet
'if both trange and tsheet are "", current selection is searched
'tname2 or tname3 can be chosen as "" for simpler searches

FindTriple = 0
oDoc = ThisComponent

if oDoc.sheets.hasbyname(tsheet) then
oSheet = oDoc.sheets.getbyname(tsheet)
if oDoc.NamedRanges.hasByName(trange) then
oRange = oSheet.getcellrangebyname(trange)
else
oRange = oSheet
end if
else
if oDoc.NamedRanges.hasByName(trange) then
tsheet = FindSheet(trange,0)
oSheet = oDoc.sheets.getbyname(tsheet)
oRange = oSheet.getcellrangebyname(trange)
else
oSheet = oDoc.CurrentController.getActiveSheet
oRange = oDoc.getcurrentselection()
if isnull(oRange) then oRange = oSheet
end if
end if

with oRange.rangeaddress
c1 = .startcolumn
c2 = .endcolumn
r1 = .startrow
r2 = .endrow
end with

if isnumeric(c) then c1 = c1 + c : c2 = c1'to search in only one column
if isnumeric(r) then r1 = r1 + r : r2 = r1'to search in only one row
oRange = oSheet.getcellrangebyposition(c1, r1, c2, r2)
oCell = oSheet.getcellbyposition(c1, r1)
c = c1
r = r1
k = 0
if tname = oCell.string then gosub FoundOne

xSearchD = oRange.createSearchDescriptor()
With xSearchD
.SearchString = tname
.SearchCaseSensitive = false
.SearchWords = true
REM SearchWords forces the entire cell to contain only the search string
'from Andrew Pitonyak's ooDocument. He used bWholeWord instead of true
'.SearchWords = bWholeWord
End With

do
xFound = oCell
xFound = oRange.findNext(xFound, xSearchD)
if IsNull(xFound) then exit do
oCell = xFound
gosub FoundOne
loop
exit function

FoundOne:
c = oCell.rangeaddress.startcolumn
r = oCell.rangeaddress.startrow
temp2 = oSheet.getcellbyposition(c + offset2,r).string
temp3 = oSheet.getcellbyposition(c + offset3,r).string
if (len(tname2) < 2 or temp2 = tname2) _
and (len(tname3) < 2 or temp3 = tname3) then k = k + 1
If k = n Then
FindTriple = -1
If go Then
oCell = oSheet.getcellbyposition(c,r)
oDoc.getCurrentController.select(oCell)
End If
exit function
End If
Return
End Function

Create range, Column number to letter, Rounding

2006-10-02T08:07:00.000-07:00

Three short macros ... The first creates a named range. The second converts a column number to a letter. (Note: Column 0 is column A, column 26 is column AA. Also, row 0 is row 1.) The third rounds a number to n decimal places.

Sub NewRange(trange, tsheet, c1, r1, c2, r2)
'defines trange on tsheet with these coordinates
if trange = "" then exit sub
r1 = r1 + 1 : r2 = r2 + 1
temp = "$" & tsheet &amp;amp; ".$" & Letter(c1) & "$" & r1 & ":$" & Letter(c2) & "$" & r2
with ThisComponent.NamedRanges
If .hasByName(trange) Then .removeByName (trange)
.addNewByName(trange, temp, createUnoStruct( "com.sun.star.table.CellAddress" ),0 )
end with
r1 = r1 - 1 : r2 = r2 - 1
End Sub

Function Letter(c)
temp = Int(c/26)
if temp > 0 then Letter = Chr(64 + temp) else Letter = ""
Letter = Letter & Chr(65 + c - 26 * temp)
End Function

Function Rounding(x,n)' rounds x to n decimal places
Rounding = int(x * 10^n)/10^n
end Function

saving a dialog

2006-09-29T18:44:00.000-07:00

I recently updated Open Office and clicked the wrong button and lost all the macros and the dialog that I had spent so long on. They were nowhere to be found. However, in looking through computer files and then finally doing a search at the OpenOffice Forum, I now know where my newly created macros are -- which doesn't help too much since they're pretty well buried.
However, while playing around, I found out that one can "export" a library of macros and dialogs. So, now I have the library that I'm recreating copied to a folder on the desktop. It could be worse. I still have my old Visual Basic macros and dialog.

load speed

2006-06-24T09:35:00.000-07:00

It's amazing what a little change can make. I was complaining to one of my sons about how slow Open Office loaded. He went to options and unchecked Java runtime environment. Now Open Office loads much more quickly.

objects

2006-03-06T08:29:00.000-08:00

I don't know if I just didn't know enough Visual Basic to make full use of objects. However, I'm finding that it's natural to change the find-something-or-other functions that I wrote to return an object instead of a boolean and then to check the object to see if it has the characteristic (such as the right string value) that I was searching for. (The FindFirst function that comes with OOo Basic returns an object -- a cell -- if the search was successful.) I'm also finding that I'm not creating temporary range names (as I did in VB) but instead just using oCell= or oRange= .
On another vein, I also find I'm liking using the Input function followed by a MsgBox function that says in effect "Are you sure?" as recommended in the Help info for Input function.

sorting

2006-03-03T07:24:00.000-08:00

I was reading the OpenOffice forum message boards and saw a question about sorting on more than 3 categories. I haven't joined the forum since I know very little about Basic but thought I'd at least write down my thoughts somewhere. To sort on, say, 5 categories, first sort on the last two then sort the result on the first 3.
Finally, I'm finding that it isn't as hard as I thought to rewrite macros from Visual Basic to OOo Basic. There are a few differences to keep one on one's toes, of course. A few of the provided macro functions are different (like IFF instead of IF for the function). The workbook functions are mostly the same -- except for using semicolons instead of commas to separate elements.

Changing controls in a dialog

2006-02-08T07:44:00.000-08:00

oControl = oDialog1.getControl("ComboBox1")
msgbox oControl.itemcount
msgbox oControl.text
oControl = oDialog1.GetControl("Label1")
msgbox oControl.text
oControl.text = "kitty"
oControl = oDialog1.GetControl("CheckBox1")
msgbox oControl.state
oDialog1Model = oDialog1.Model
oControlModel = oDialog1Model.CheckBox1
oControlModel.state = 1
oControlModel.label = "hello"

Range entries to listbox

2006-02-02T08:00:00.000-08:00

Again, there's probably an easier way to do this, but this works to copy entries from a spreadsheet range to a listbox (or combobox) in a dialog.

rngname="Jason" 'the name of the range (one column by unknown number of rows)
oNamedRange = ThisComponent.NamedRanges.getByName(rngname)
sName = oNamedRange.getContent() 'gives full address starting with $
xPos = InStr(1,sName,".") - 2 'sheet name ends with .
sName = Mid(sName,2,xPos)
oSheet = ThisComponent.Sheets.getByName(sName)
oRange = oSheet.getcellrangebyname(rngname)
rtot=oRange.rows.count -1
dim a(rtot)
for j=0 to rtot
oCell=oRange.getcellbyposition(0,j)
a(j)=ocell.string
next
DialogLibraries.LoadLibrary( "Standard" )
oDialog1 = CreateUnoDialog( DialogLibraries.Standard.Dialog1 )
oControl = oDialog1.GetControl("ComboBox1") 'provided it already exists
oControl.additems(a(),0)

oDialog1.Execute() 'entries can be added afterwards also
'but in a separate sub

Turn to sheet having range and select range

2006-01-31T11:38:00.000-08:00

This is not the most elegant way of doing things, but it works for turning to the sheet containing "rngname" and then selecting the range. It doesn't turn to the desired sheet until the last line.

oDoc = ThisComponent
oRanges = oDoc.NamedRanges
oNamedRange = oRanges.getByName(rngname)
oAddr = oNamedRange.getContent()
xPos = InStr(1,oAddr,".")-2
oAddr = Mid(oAddr,2,xPos)
oCells = ThisComponent.Sheets.getByName(oAddr).getCellRangeByName(rngname)
ThisComponent.getCurrentController.select(oCells)

Columns before rows

2006-01-31T08:11:00.000-08:00

In cell addresses, the column position comes before the row position. (Both start with 0.) So, I hope these are right,
oSheet.getCellByPosition(1,3)
is equivalent to
oSheet.getCellRangeByName("B4")
To get ranges:
oRange = ThisComponent.NamedRanges.getByName("Jason")
oRange = oSheet.getCellRangeByPosition(0,0,1,3)
oRange = oSheet.getCellRangeByName("a1:b3")
These are equivalent, provided Jason is a1:b3.
temp = oCell.getSpreadSheet().getName
temp2 = oCell.CellAddress.Column
temp3 = oCell.CellAddress.Row
get the name of the spreadsheet that oCell is on, the column number, and the row number.
temp = oRange.getSpreadSheet().getName
doesn't work.
oSheet = oDoc.Sheets.getByName("Brian")
oSheet = oDoc.Sheets.getByIndex(1)
are equivalent, provided the 2nd sheet is named Brian.

Make global

2006-01-29T08:59:00.000-08:00

Note to self: Make dialog and listbox global in order not to lose them when subs end.