Hello all, I would like some assistance.
Using Excel 2007 on Vista
Confused with RoudUp and Round in procedure
Some thing to do with remainder didgets. How do I name them as long or
whatever

Public Sub CashFlow()
Const dCashCommission As Double = 0.05
Const dCashTake As Double = 0.2
Const dCashInvest As Double = 0.6
Dim dDistribute(1 To 4) As double
Dim i As Long
dAmount = ActiveCell.Value
'This next line in the macro I would like to change to:-
dDistribute(1) = RoundUp(dAmount * dCashCommission,2)

'change from Int to RoundUp? Works with Int, can't get it to work with
RoundUp

dDistribute(1) = Int(dAmount * dCashCommission)
dDistribute(2) = Int(dAmount * dCashTake)
dDistribute(3) = Int(dAmount * dCashInvest)
dDistribute(4) = dAmount - dDistribute(1) - dDistribute(2) -
dDistribute(3)
With Range("T1:T4")
For i = 1 To 4
.cells(i).Value = .cells(i).Value + dDistribute(i)
Next i
End With
ActiveCell.Offset(1, -14).Range("A1").Select
End Sub

Any help welcome and thanks.
Skinman

On Mon, 25 Aug 2008 15:08:28 +1000, "Skinman" wrote:

Hello all, I would like some assistance.
Using Excel 2007 on Vista
Confused with RoudUp and Round in procedure
Some thing to do with remainder didgets. How do I name them as long or
whatever

Public Sub CashFlow()
Const dCashCommission As Double = 0.05
Const dCashTake As Double = 0.2
Const dCashInvest As Double = 0.6
Dim dDistribute(1 To 4) As double
Dim i As Long
dAmount = ActiveCell.Value
'This next line in the macro I would like to change to:-
dDistribute(1) = RoundUp(dAmount * dCashCommission,2)

'change from Int to RoundUp? Works with Int, can't get it to work with
RoundUp

dDistribute(1) = Int(dAmount * dCashCommission)
dDistribute(2) = Int(dAmount * dCashTake)
dDistribute(3) = Int(dAmount * dCashInvest)
dDistribute(4) = dAmount - dDistribute(1) - dDistribute(2) -
dDistribute(3)
With Range("T1:T4")
For i = 1 To 4
.cells(i).Value = .cells(i).Value + dDistribute(i)
Next i
End With
ActiveCell.Offset(1, -14).Range("A1").Select
End Sub

Any help welcome and thanks.
Skinman

For Int( , you can substitute:

Application.WorksheetFunction.RoundUp(

--ron

For Int( , you can substitute:

Application.WorksheetFunction.RoundUp(

--ron

Thanks Ron, Goes without saying that it works.
If Round, and Int work as first posted why not RoundUp?
They are all worksheet functions, are some worksheet functions not directly
available to
VBA without first calling . -- Application.WorksheetFunction?

Skinman

VBA was not written exclusively for Excel... all (at least I think all)
Office applications use it as well as several non-Microsoft products (for
example, AutoCAD and CorelDraw have it too). As a general programming
language, it has a basic set of functions built in... Int and Round are two
of them whereas RoundUp never was included in the language. To make VBA work
with the various applications it is built into, those applications have
extended libraries of functions that are specific to the individual
application itself. The mechanism Microsoft used to get at the worksheet
functions from within Excel, whether they have VBA equivalents or not, is
the WorksheetFunction property of the Application object.

Rick


"Skinman" wrote in message
...

For Int( , you can substitute:

Application.WorksheetFunction.RoundUp(

--ron

Thanks Ron, Goes without saying that it works.
If Round, and Int work as first posted why not RoundUp?
They are all worksheet functions, are some worksheet functions not
directly available to
VBA without first calling . -- Application.WorksheetFunction?

Skinman

On Mon, 25 Aug 2008 10:03:24 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

VBA was not written exclusively for Excel... all (at least I think all)
Office applications use it as well as several non-Microsoft products (for
example, AutoCAD and CorelDraw have it too). As a general programming
language, it has a basic set of functions built in... Int and Round are two
of them whereas RoundUp never was included in the language. To make VBA work
with the various applications it is built into, those applications have
extended libraries of functions that are specific to the individual
application itself. The mechanism Microsoft used to get at the worksheet
functions from within Excel, whether they have VBA equivalents or not, is
the WorksheetFunction property of the Application object.

Rick



Also note that the VBA Round function and the Excel Round worksheet function
are NOT the same. The former uses "bankers rounding" (although I don't know of
any bankers that actually used if :-))
--ron

On Mon, 25 Aug 2008 22:05:45 +1000, "Skinman" wrote:


For Int( , you can substitute:

Application.WorksheetFunction.RoundUp(

--ron

Thanks Ron, Goes without saying that it works.
If Round, and Int work as first posted why not RoundUp?
They are all worksheet functions, are some worksheet functions not directly
available to
VBA without first calling . -- Application.WorksheetFunction?

Skinman

Although they may have the same name, they may not necessarily be
worksheetfunctions.

The VBA Round function and the Excel ROUND worksheet function are NOT the same.
They round differently.

For example, with zero (0) as the second argument, the Excel Round worksheet
function rounds to the nearest integer; whereas the VBA Round function rounds
to the nearest even integer.

e.g.:

N=6.5

VBA Round -- 6
Excel Round -- 7

On the other hand, the VBA INT function is the same as the Excel worksheet
function INT, so it is not even a part of the worksheetfunction group! When
you call INT, you are calling a VBA function that happens to behave the same as
the Excel worksheet function with the same name.

--ron