Hi
This does what Graham wants. It captures a range of cells into a variant array in line 1 to 3, and all the further processing is done on the array.
Public Function GallonsPerMonthCharge(vGallonsUsed As Variant) As Variant
Dim nRow As Long
Dim vChargeTable As Variant
Const USE = 1
Const CHARGE = 2
'
1 With Worksheets("Sheet1").Range("a1:b6")
2 vChargeTable = .Value
3 End With
For nRow = 1 To UBound(vChargeTable)
If vGallonsUsed < vChargeTable(nRow, USE) Then
GallonsPerMonthCharge = vChargeTable(nRow, CHARGE)
Exit For
End If
Next nRow
End Function
Notice that the table used is two columns not the original three as that was ambiguous.
2,000 3.75
5,000 5.50
10,000 7.40
25,000 10.00
50,000 15.00
200,000 25.00
Also, as it stands the function returns 3.75 if the gallons used is zero, which may or may not be what you want. Similarly it returns 0 if the use is greater than 200,000 gallons.
Originally I wanted line 1 to look like :-
With Worksheets("Sheet1").Range("a1").CurrentRegion
This worked fine while I was testing the function as a subroutine, but as soon as I made it a function it failed because vChargeTable = .Value in line 2 resolved to the contents of cell A1, not a1 to B6.
Can anyone explain this ? (I am using Excel 2010 in a 64 bit Windows 7 PC)
Regards
Derek +++
From: gdmpearson <me@grahampearson.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Friday, 30 December 2011, 18:51
>Subject: [ExcelVBA] Implementing a Table in Visual Basic
>
>
>
>Hello folks:
>
>This is my first post in this group. Please excuse me if it's too long. I've tried searching previous posts and I don't find a clear illustration of what I'm trying to do.
>
>I've been asked to write a Visual Basic function for an Excel Workbook that calculates dollars based on monthly water usage. Here's what the input table looks like:
>
>Gallons per month Charge per 1,000 gallons
>0 - 2,000 $3.75
>2,000 - 5,000 $5.50
>5,000 - 10,000 $7.40
>10,000 - 25,000 $10.00
>25,000 - 50,000 $15.00
>50,000 - 200,000 $25.00
>
>The function must calculate the total dollars based on a specified number of gallons used in a month.
>
>I intend to include an area in the Workbook with 3 consecutive columns, say A1:A6 is 0, 2000, 5000, etc., B1:B6 is 2000, 5000, 10000, etc., and C1:C6 is 3.75, 5.50, 7.40, etc.
>
>Regarding passing parameters to the Visual Basic function, I know how to pass the gallon usage as a double and return the calculated result as a double.
>
>What's the easiest way to pass the ranges A1:A6, B1:B6, and C1:C6?
>
>I'm an old C++ programmer so I can hack my way through the relevant Basic ocde to calculate the dollars.
>
>If there's a post on this forum that demonstrates some of the principles I need to use here, I would appreciate your letting me know the reference.
>
>Again, excuse the long post and I appreciate any help you can give me.
>Best regards,
>Graham
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar