[TowerTalk] Cable on a reel using a formula?

[Keith Dutson]

The VB in Excel is called VBA, or Visual Basic for Applications.  It is
somewhat limited compared to regular Visual Basic.  You should have no
problem implementing it if you already know Basic.  The main difference is
that code is grouped into functions rather than straight-line, and the
functions are mainly called by actions taken by mouse clicks, etc.

73, Keith NM5G

-----Original Message-----
From: towertalk-bounces at contesting.com
[mailto:towertalk-bounces at contesting.com] On Behalf Of Mark .
Sent: Friday, October 07, 2005 8:29 AM
To: towertalk at contesting.com
Subject: Re: [TowerTalk] Cable on a reel using a formula?

Kieth wrote:

<< Pi * r squared is area of a circle.  What you will need is 2 * pi * r, or
pi * diameter, which is circumference.  However, this is accurate only for
the turns on one radius.  You can get fairly close by counting the outer
turns across the reel, then estimate how many layers there are to the inner
layer, then adding all together.  Calculate circumference using r measured
from center of layers to center (axle) of reel, and multiply by total number
of turns...SNIP...73, Keith NM5G >>

Keith has got it, and this requires an iterative solution: estimate a
packing factor (for how neatly the coils nest), calculate the number of
layers, then the number of turns per layer,  and then calculate the cable
length for each layer, adding them up as you go.

  I wrote a handy 'hose length calculator' program to solve this problem
using BASIC in the late 80's. In planning the installation of a CCTV system,
I needed to estimate lengths of control cables on partially filled spools. 
Gee, that's going back a ways. It may be BASIC, but it's just as helpful now
as then.

I compiled the program into self-executable code and will email it to anyone
interested upon request. I will change the extension from EXE to CAL for the
attachment. After you download it, change the extension back to EXE and run
it.

After reading this thread, I began trying to port this into Excel to make a
more modern calculator tool. The problem is that I don't know how to make an
iterative loop calculation in a spreadsheet.

I see there is a visual BASIC editor in Excel. Do any of you have experience
importing (old) BASIC code into Excel with this method? I have several such
old compiled 'handy tools' that I'd like to modernize.

*** WARNING - nerd engineer nauseating detail imminent... stand by your DEL
key *** <*grin*>

Here are the equations in my solution:

ASSUMPTION: wound material has circular or nearly circular cross section

let DHUB = the diameter of the reel hub, in inches let DREEL = the outside
diameter of the reel, in inches let WIDE = the width of the inside of the
reel, in inches let DHOSE = the diameter of the hose, in inches let PF = a
packing factor that best describes the wound material:

0.7  =  NEARLY PERFECT NESTING; factory-wound cable, wire, twine, or thread
0.85 =  PARTIAL, OR SLOPPY NESTING; neatly re-wound air hose, cable, or
twine
1   =  NO NESTING, LOOSE WIND; quick and dirty rewind of material"

***  calculate the number of layers, NL  *** NL = Int(1 + (DREEL - DHUB - 2
* DHOSE) / (2 * DHOSE * PF))

***  calculate the number of coils per layer, C  *** C = Int(WIDE / DHOSE) -
0.5

for N = 1 To NL:
***  calculate the nominal coil diameter of each layer, DN  *** DN = DHUB +
(N - 1) * DHOSE * PF

***  calculate the length of hose per layer, LN  *** LN = 3.14159 * DN * C

*** last, sum up the lengths for all the layers and divide by 12 to get feet
***

Gee, did anyone else have to learn FORTRAN IV in college using punch cards,
bleary-eyed, falling asleep to the whop-whop-whop of the card reader, trying
to get your programming assignment to work?

My, how far computing has come.

--...MARK_N1LO...--


_______________________________________________

See: http://www.mscomputer.com  for "Self Supporting Towers", "Wireless
Weather Stations", and lot's more.  Call Toll Free, 1-800-333-9041 with any
questions and ask for Sherman, W2FLA.

_______________________________________________
TowerTalk mailing list
TowerTalk at contesting.com
http://lists.contesting.com/mailman/listinfo/towertalk