Sum, Offset & Calculation 
Author Message
 Sum, Offset & Calculation

Hi,

Summary

I am using Win 2K and Office 2K.  I am trying to sum numbers offset from a
range without getting the "Calculation" symbol displayed when using manual
calculation.

Detailed Discussion

I have a spreadsheet which uses some long and complex user defined functions
(UDFs).  This spreadsheet has it calculation mode set to manual.  When I use
one UDF on a single cell, it usually leaves the "Calculation" sign on.  That
is to say, I go to the cell, press F2, and then press enter.  If I press F9
to calculate, I crash Excel, or Excel hangs.  I have been able to track down
where the offending line (this is the last line executed prior to crashing
or{*filter*}).  The line is as follows:

Var1 = WorksheetFunction.Sum(Range(vSpecTable).Offset(2, 1).Resize(3, 1))

As mentioned, I have the spreadsheet calculation set to manual.  If I set it
to auto, Excel crashes immediately.

To investigate the Worksheet.Function.Sum command, I started a new tiny
spreadsheet with a column of 15 numbers and gave it the name "rnRange".

I then created three similar but separate user defined functions (see
below).

I had the calculation set to manual in the spreadsheet.  In each case, after
I went to the cell and hit F2 enter, I got the correct answer, but was left
with a "Calculation" symbol displaying.  If I hit F9 again, the spreadsheet
quickly recalculated and the Calculation sign went away.

The first part is very similar to my current experience on my model.  If I
put my cursor on the cell, and press F2 then enter, I get the correct
answer, but am left with the calculation symbol.  If I hit F9 on my model, I
usually crash Excel.  In my "big" model, if I have the calculation set to
automatic, it dies.  So I ran the "simulation tests" above with manual in an
attempt to duplicate my findings.  (Incidentally, one the very small
spreadsheet, the automatic calculation seems to work fine.)

Is there some other approach that I can sum the numbers without getting into
this grief?  Perhaps one method is to get away from the offset resize is to
simply name the cells of interest, and then just use the cell names.  This
seems rather clunky to me, but perhaps it is a better method.  (I just tried
this, see KHSTest4, and it too gives me a "Calculate" after F2 enter with
manual calculation.)  Any other ideas?

Again, very appreciative of all help.

Best regards,
Kevin

Quick UDFs

Function KHSTest(vName As Variant)
Dim rnRange As Range

Set rnRange = Range(vName).Offset(3, 0).Resize(3, 1)

KHSTest = Application.WorksheetFunction.Sum(rnRange)

End Function

Function KHSTest2(vName As Variant)

KHSTest2 = Application.WorksheetFunction.Sum(Range(vName).Offset(3,
0).Resize(3, 1))

End Function

Function KHSTest3(strName As String)

KHSTest3 = Application.WorksheetFunction.Sum(Range(strName).Offset(3,
0).Resize(3, 1))

End Function

Function KHSTest4(strName1 As String, strName2 As String, strName3 As
String)
Dim rnCell1 As Range
Dim rnCell2 As Range
Dim rnCell3 As Range

Set rnCell1 = Range(strName1)
Set rnCell2 = Range(strName2)
Set rnCell3 = Range(strName3)

KHSTest4 = rnCell1.Value + rnCell2.Value + rnCell3.Value

End Function



Sat, 05 Nov 2005 03:48:51 GMT  
 Sum, Offset & Calculation
In answer to your main query, you can suppress the "Calculate" message
by taking control of the statusbar with something like :-

Application.StatusBar = True
Application.StatusBar = " " ........ ie. a space - or perhaps
something more helpful.

This does not answer your other question. I tried your code in XL97
without problem. You do not show how you are using the function. Vname
should be a string variable, presumably you are using this correctly
(?)

I would put a breakpoint early in the function and run the code, when
you would hopefully get a (more helpful) error message.

Regards
BrianB
=============================================

Quote:

> Hi,

> Summary

> I am using Win 2K and Office 2K.  I am trying to sum numbers offset from a
> range without getting the "Calculation" symbol displayed when using manual
> calculation.

> Detailed Discussion

> I have a spreadsheet which uses some long and complex user defined functions
> (UDFs).  This spreadsheet has it calculation mode set to manual.  When I use
> one UDF on a single cell, it usually leaves the "Calculation" sign on.  That
> is to say, I go to the cell, press F2, and then press enter.  If I press F9
> to calculate, I crash Excel, or Excel hangs.  I have been able to track down
> where the offending line (this is the last line executed prior to crashing
> or{*filter*}).  The line is as follows:

> Var1 = WorksheetFunction.Sum(Range(vSpecTable).Offset(2, 1).Resize(3, 1))

> As mentioned, I have the spreadsheet calculation set to manual.  If I set it
> to auto, Excel crashes immediately.

> To investigate the Worksheet.Function.Sum command, I started a new tiny
> spreadsheet with a column of 15 numbers and gave it the name "rnRange".

> I then created three similar but separate user defined functions (see
> below).

> I had the calculation set to manual in the spreadsheet.  In each case, after
> I went to the cell and hit F2 enter, I got the correct answer, but was left
> with a "Calculation" symbol displaying.  If I hit F9 again, the spreadsheet
> quickly recalculated and the Calculation sign went away.

> The first part is very similar to my current experience on my model.  If I
> put my cursor on the cell, and press F2 then enter, I get the correct
> answer, but am left with the calculation symbol.  If I hit F9 on my model, I
> usually crash Excel.  In my "big" model, if I have the calculation set to
> automatic, it dies.  So I ran the "simulation tests" above with manual in an
> attempt to duplicate my findings.  (Incidentally, one the very small
> spreadsheet, the automatic calculation seems to work fine.)

> Is there some other approach that I can sum the numbers without getting into
> this grief?  Perhaps one method is to get away from the offset resize is to
> simply name the cells of interest, and then just use the cell names.  This
> seems rather clunky to me, but perhaps it is a better method.  (I just tried
> this, see KHSTest4, and it too gives me a "Calculate" after F2 enter with
> manual calculation.)  Any other ideas?

> Again, very appreciative of all help.

> Best regards,
> Kevin

> Quick UDFs

> Function KHSTest(vName As Variant)
> Dim rnRange As Range

> Set rnRange = Range(vName).Offset(3, 0).Resize(3, 1)

> KHSTest = Application.WorksheetFunction.Sum(rnRange)

> End Function

> Function KHSTest2(vName As Variant)

> KHSTest2 = Application.WorksheetFunction.Sum(Range(vName).Offset(3,
> 0).Resize(3, 1))

> End Function

> Function KHSTest3(strName As String)

> KHSTest3 = Application.WorksheetFunction.Sum(Range(strName).Offset(3,
> 0).Resize(3, 1))

> End Function

> Function KHSTest4(strName1 As String, strName2 As String, strName3 As
> String)
> Dim rnCell1 As Range
> Dim rnCell2 As Range
> Dim rnCell3 As Range

> Set rnCell1 = Range(strName1)
> Set rnCell2 = Range(strName2)
> Set rnCell3 = Range(strName3)

> KHSTest4 = rnCell1.Value + rnCell2.Value + rnCell3.Value

> End Function



Sat, 05 Nov 2005 08:52:01 GMT  
 Sum, Offset & Calculation
Hi Kevin,

Using OFFSET within a UDF is probably not a good idea: Excel will not know
when to recalculate the UDF because the UDF refers to cells that are not in
the argument list.
if you really want to do this you should add Application.Volatile to your
function, but this will make the UDF always calculate, which will slow
things down.

best design is to make sure all the referenced cells are in the UDF
arguments
I also recommend adding On Error handling and checking that all the input
cells have been recalculated using IsEmpty.

(see http://www.*-*-*.com/ )

regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com


Quote:
> Hi,

> Summary

> I am using Win 2K and Office 2K.  I am trying to sum numbers offset from a
> range without getting the "Calculation" symbol displayed when using manual
> calculation.

> Detailed Discussion

> I have a spreadsheet which uses some long and complex user defined
functions
> (UDFs).  This spreadsheet has it calculation mode set to manual.  When I
use
> one UDF on a single cell, it usually leaves the "Calculation" sign on.
That
> is to say, I go to the cell, press F2, and then press enter.  If I press
F9
> to calculate, I crash Excel, or Excel hangs.  I have been able to track
down
> where the offending line (this is the last line executed prior to crashing
> or{*filter*}).  The line is as follows:

> Var1 = WorksheetFunction.Sum(Range(vSpecTable).Offset(2, 1).Resize(3, 1))

> As mentioned, I have the spreadsheet calculation set to manual.  If I set
it
> to auto, Excel crashes immediately.

> To investigate the Worksheet.Function.Sum command, I started a new tiny
> spreadsheet with a column of 15 numbers and gave it the name "rnRange".

> I then created three similar but separate user defined functions (see
> below).

> I had the calculation set to manual in the spreadsheet.  In each case,
after
> I went to the cell and hit F2 enter, I got the correct answer, but was
left
> with a "Calculation" symbol displaying.  If I hit F9 again, the
spreadsheet
> quickly recalculated and the Calculation sign went away.

> The first part is very similar to my current experience on my model.  If I
> put my cursor on the cell, and press F2 then enter, I get the correct
> answer, but am left with the calculation symbol.  If I hit F9 on my model,
I
> usually crash Excel.  In my "big" model, if I have the calculation set to
> automatic, it dies.  So I ran the "simulation tests" above with manual in
an
> attempt to duplicate my findings.  (Incidentally, one the very small
> spreadsheet, the automatic calculation seems to work fine.)

> Is there some other approach that I can sum the numbers without getting
into
> this grief?  Perhaps one method is to get away from the offset resize is
to
> simply name the cells of interest, and then just use the cell names.  This
> seems rather clunky to me, but perhaps it is a better method.  (I just
tried
> this, see KHSTest4, and it too gives me a "Calculate" after F2 enter with
> manual calculation.)  Any other ideas?

> Again, very appreciative of all help.

> Best regards,
> Kevin

> Quick UDFs

> Function KHSTest(vName As Variant)
> Dim rnRange As Range

> Set rnRange = Range(vName).Offset(3, 0).Resize(3, 1)

> KHSTest = Application.WorksheetFunction.Sum(rnRange)

> End Function

> Function KHSTest2(vName As Variant)

> KHSTest2 = Application.WorksheetFunction.Sum(Range(vName).Offset(3,
> 0).Resize(3, 1))

> End Function

> Function KHSTest3(strName As String)

> KHSTest3 = Application.WorksheetFunction.Sum(Range(strName).Offset(3,
> 0).Resize(3, 1))

> End Function

> Function KHSTest4(strName1 As String, strName2 As String, strName3 As
> String)
> Dim rnCell1 As Range
> Dim rnCell2 As Range
> Dim rnCell3 As Range

> Set rnCell1 = Range(strName1)
> Set rnCell2 = Range(strName2)
> Set rnCell3 = Range(strName3)

> KHSTest4 = rnCell1.Value + rnCell2.Value + rnCell3.Value

> End Function



Sat, 05 Nov 2005 10:17:38 GMT  
 Sum, Offset & Calculation
Hi Brian,

Thank you for the StatusBar code.  That will prove helpful.

With respect to Vname, yes it is a string variable for the very simple UDF.
I understand from others (CompuServe) that the simple UDFs did perform as
expected with the calculation status showing.

The error message for my large (not shown) UDF is simply "Excel is
generating errors and closing" or words to that effect.  There is nothing
that gives the user any heads up to what is wrong.

Jan Karel Pieterse on CompuServe did a test on my spreadsheet and found that
it appears to be corrupted.  His solution was to save the spreadsheet as an
HTML and then reopen it.  From there it appeared to work fine.  So I will
"clean" my spreadsheet using the method he suggested.

Also, I did use breakpoints; however, the program performed flawlessly when
using the breakpoint.  It only crashed when it was set to run for real.

What I did do to find where the code actually did is install a subroutine to
track the last step performed.  In my code, I simply wrote LogIt 100, LogIt
110 etc. for every block of code.  And then once I knew the block, I went
every statement and isolated the precise line of code.

'\Temporary debugging subroutine as suggested by
'\Bill Manville and Jan Karel Pieterse

Sub LogIt(ByVal stLine As String)
Open "C:\Test.txt" For Append As #1
Print #1, stLine
Close #1
End Sub

Best regards,
Kevin


Quote:
> In answer to your main query, you can suppress the "Calculate" message
> by taking control of the statusbar with something like :-

> Application.StatusBar = True
> Application.StatusBar = " " ........ ie. a space - or perhaps
> something more helpful.

> This does not answer your other question. I tried your code in XL97
> without problem. You do not show how you are using the function. Vname
> should be a string variable, presumably you are using this correctly
> (?)

> I would put a breakpoint early in the function and run the code, when
> you would hopefully get a (more helpful) error message.

> Regards
> BrianB
> =============================================




- Show quoted text -

Quote:
> > Hi,

> > Summary

> > I am using Win 2K and Office 2K.  I am trying to sum numbers offset from
a
> > range without getting the "Calculation" symbol displayed when using
manual
> > calculation.

> > Detailed Discussion

> > I have a spreadsheet which uses some long and complex user defined
functions
> > (UDFs).  This spreadsheet has it calculation mode set to manual.  When I
use
> > one UDF on a single cell, it usually leaves the "Calculation" sign on.
That
> > is to say, I go to the cell, press F2, and then press enter.  If I press
F9
> > to calculate, I crash Excel, or Excel hangs.  I have been able to track
down
> > where the offending line (this is the last line executed prior to
crashing
> > or{*filter*}).  The line is as follows:

> > Var1 = WorksheetFunction.Sum(Range(vSpecTable).Offset(2, 1).Resize(3,
1))

> > As mentioned, I have the spreadsheet calculation set to manual.  If I
set it
> > to auto, Excel crashes immediately.

> > To investigate the Worksheet.Function.Sum command, I started a new tiny
> > spreadsheet with a column of 15 numbers and gave it the name "rnRange".

> > I then created three similar but separate user defined functions (see
> > below).

> > I had the calculation set to manual in the spreadsheet.  In each case,
after
> > I went to the cell and hit F2 enter, I got the correct answer, but was
left
> > with a "Calculation" symbol displaying.  If I hit F9 again, the
spreadsheet
> > quickly recalculated and the Calculation sign went away.

> > The first part is very similar to my current experience on my model.  If
I
> > put my cursor on the cell, and press F2 then enter, I get the correct
> > answer, but am left with the calculation symbol.  If I hit F9 on my
model, I
> > usually crash Excel.  In my "big" model, if I have the calculation set
to
> > automatic, it dies.  So I ran the "simulation tests" above with manual
in an
> > attempt to duplicate my findings.  (Incidentally, one the very small
> > spreadsheet, the automatic calculation seems to work fine.)

> > Is there some other approach that I can sum the numbers without getting
into
> > this grief?  Perhaps one method is to get away from the offset resize is
to
> > simply name the cells of interest, and then just use the cell names.
This
> > seems rather clunky to me, but perhaps it is a better method.  (I just
tried
> > this, see KHSTest4, and it too gives me a "Calculate" after F2 enter
with
> > manual calculation.)  Any other ideas?

> > Again, very appreciative of all help.

> > Best regards,
> > Kevin

> > Quick UDFs

> > Function KHSTest(vName As Variant)
> > Dim rnRange As Range

> > Set rnRange = Range(vName).Offset(3, 0).Resize(3, 1)

> > KHSTest = Application.WorksheetFunction.Sum(rnRange)

> > End Function

> > Function KHSTest2(vName As Variant)

> > KHSTest2 = Application.WorksheetFunction.Sum(Range(vName).Offset(3,
> > 0).Resize(3, 1))

> > End Function

> > Function KHSTest3(strName As String)

> > KHSTest3 = Application.WorksheetFunction.Sum(Range(strName).Offset(3,
> > 0).Resize(3, 1))

> > End Function

> > Function KHSTest4(strName1 As String, strName2 As String, strName3 As
> > String)
> > Dim rnCell1 As Range
> > Dim rnCell2 As Range
> > Dim rnCell3 As Range

> > Set rnCell1 = Range(strName1)
> > Set rnCell2 = Range(strName2)
> > Set rnCell3 = Range(strName3)

> > KHSTest4 = rnCell1.Value + rnCell2.Value + rnCell3.Value

> > End Function



Sat, 05 Nov 2005 14:41:32 GMT  
 Sum, Offset & Calculation
Charles,

Thank you very much for responding.  I understand your reasoning for not
using Offsets in UDFs and will change.  I will also be visiting your link
below.

Again, thank you!

Best regards,
Kevin


Quote:
> Hi Kevin,

> Using OFFSET within a UDF is probably not a good idea: Excel will not know
> when to recalculate the UDF because the UDF refers to cells that are not
in
> the argument list.
> if you really want to do this you should add Application.Volatile to your
> function, but this will make the UDF always calculate, which will slow
> things down.

> best design is to make sure all the referenced cells are in the UDF
> arguments
> I also recommend adding On Error handling and checking that all the input
> cells have been recalculated using IsEmpty.

> (see http://www.*-*-*.com/ )

> regards
> Charles
> ______________________
> Decision Models
> The Excel Calculation Site
> www.DecisionModels.com



> > Hi,

> > Summary

> > I am using Win 2K and Office 2K.  I am trying to sum numbers offset from
a
> > range without getting the "Calculation" symbol displayed when using
manual
> > calculation.

> > Detailed Discussion

> > I have a spreadsheet which uses some long and complex user defined
> functions
> > (UDFs).  This spreadsheet has it calculation mode set to manual.  When I
> use
> > one UDF on a single cell, it usually leaves the "Calculation" sign on.
> That
> > is to say, I go to the cell, press F2, and then press enter.  If I press
> F9
> > to calculate, I crash Excel, or Excel hangs.  I have been able to track
> down
> > where the offending line (this is the last line executed prior to
crashing
> > or{*filter*}).  The line is as follows:

> > Var1 = WorksheetFunction.Sum(Range(vSpecTable).Offset(2, 1).Resize(3,
1))

> > As mentioned, I have the spreadsheet calculation set to manual.  If I
set
> it
> > to auto, Excel crashes immediately.

> > To investigate the Worksheet.Function.Sum command, I started a new tiny
> > spreadsheet with a column of 15 numbers and gave it the name "rnRange".

> > I then created three similar but separate user defined functions (see
> > below).

> > I had the calculation set to manual in the spreadsheet.  In each case,
> after
> > I went to the cell and hit F2 enter, I got the correct answer, but was
> left
> > with a "Calculation" symbol displaying.  If I hit F9 again, the
> spreadsheet
> > quickly recalculated and the Calculation sign went away.

> > The first part is very similar to my current experience on my model.  If
I
> > put my cursor on the cell, and press F2 then enter, I get the correct
> > answer, but am left with the calculation symbol.  If I hit F9 on my
model,
> I
> > usually crash Excel.  In my "big" model, if I have the calculation set
to
> > automatic, it dies.  So I ran the "simulation tests" above with manual
in
> an
> > attempt to duplicate my findings.  (Incidentally, one the very small
> > spreadsheet, the automatic calculation seems to work fine.)

> > Is there some other approach that I can sum the numbers without getting
> into
> > this grief?  Perhaps one method is to get away from the offset resize is
> to
> > simply name the cells of interest, and then just use the cell names.
This
> > seems rather clunky to me, but perhaps it is a better method.  (I just
> tried
> > this, see KHSTest4, and it too gives me a "Calculate" after F2 enter
with
> > manual calculation.)  Any other ideas?

> > Again, very appreciative of all help.

> > Best regards,
> > Kevin

> > Quick UDFs

> > Function KHSTest(vName As Variant)
> > Dim rnRange As Range

> > Set rnRange = Range(vName).Offset(3, 0).Resize(3, 1)

> > KHSTest = Application.WorksheetFunction.Sum(rnRange)

> > End Function

> > Function KHSTest2(vName As Variant)

> > KHSTest2 = Application.WorksheetFunction.Sum(Range(vName).Offset(3,
> > 0).Resize(3, 1))

> > End Function

> > Function KHSTest3(strName As String)

> > KHSTest3 = Application.WorksheetFunction.Sum(Range(strName).Offset(3,
> > 0).Resize(3, 1))

> > End Function

> > Function KHSTest4(strName1 As String, strName2 As String, strName3 As
> > String)
> > Dim rnCell1 As Range
> > Dim rnCell2 As Range
> > Dim rnCell3 As Range

> > Set rnCell1 = Range(strName1)
> > Set rnCell2 = Range(strName2)
> > Set rnCell3 = Range(strName3)

> > KHSTest4 = rnCell1.Value + rnCell2.Value + rnCell3.Value

> > End Function



Sat, 05 Nov 2005 15:04:02 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Sum & Offset

2. Records Offset calculation

3. Sum offset numbers

4. Help with sum from Offset(Match) formula

5. Cell Formula Help - Sum/Offset/Match

6. Help with Sum from Offset Match formula .

7. sum offset array formula returning NA

8. Sum(Offset) variable # of Rows

9. Sum-Offset-Indirect Function

10. OFFSET and Year to Date sums


 
Powered by phpBB® Forum Software © phpBB Group