Formula in excel and VBA

[Bob]

Hi Everyone:

In excel spreadsheet, the user has inputted an equation in the formula bar
that is based on several other cells. For example, in cell A1 it says:

=2*A2^A3+A4^2+5

now, in the VBA portion of excel, I need to write a function (Not a sub)
that uses a loop, and changes the value of cell A4, and based on some
decision to return a value for my function or continue the loop. The only
problem is that in a function we cannot write values to the spreadsheet and
change values of the cells. Does anyone know how I can write my function to
change the value of cell A4 in the loop?

Thanks for your help.

Bob

 

[Clif McIrvin]

Hi Everyone:

In excel spreadsheet, the user has inputted an equation in the formula
bar that is based on several other cells. For example, in cell A1 it
says:

=2*A2^A3+A4^2+5

now, in the VBA portion of excel, I need to write a function (Not a
sub) that uses a loop, and changes the value of cell A4, and based on
some decision to return a value for my function or continue the loop.
The only problem is that in a function we cannot write values to the
spreadsheet and change values of the cells. Does anyone know how I
can write my function to change the value of cell A4 in the loop?

you can't? I've never noticed any difference between subs and functions
with respect to manipulating workbooks and worksheets.

In Excel 2003 I use

public function SomeFunctionName (myVar1 as type, etc) as type
somecode
end function


I will frequently use a function instead of a sub simply so that I can
return a status flag from the operation.

 

[Bob]

Subs and functions behave differently in excel 2003. In a sub you can have
code like
range("A1").value=4, but you cannot have it in a function.

Does anyone know the answer to my question. Please help.

Bob

 

[Clif McIrvin]

Subs and functions behave differently in excel 2003. In a sub you can
have code like
range("A1").value=4, but you cannot have it in a function.

Does anyone know the answer to my question. Please help.

Bob

I don't know, Bob --

Function Macro8()
'
' Macro8 Macro
' Macro recorded 6/24/2008 by Clif McIrvin
'

'
Range("B1").Select
ActiveCell.FormulaR1C1 = "42"
Range("B2") = "43"
Range("B3").Value = "44"
End Function


works fine on my machine:

--
My environment is XP Pro SP2, Office 2003 SP3

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600
Application name Microsoft Excel
Version 11.0
Build 8211

 

[Bob]

Does anyone have any answer to my original question. Thanks;

Bob

 

[Bob]

Clif:

The reason your function works is because it does not have any arguments,
and internally, it behaves like a sub. Try the following and you will see.
1- create a module and insert the code below.
Public Function Macro8(x)
'
' Macro8 Macro
' Macro recorded 6/24/2008 by Clif McIrvin
'

'
Range("B1").Select
ActiveCell.FormulaR1C1 = "42"
Range("B2") = "43"
Range("B3").Value = "44"
Macro8=x
End Function
2- go back to the spreadsheet, and go to cell A5 and type the following
formula:
=macro8(8)
Notice that the function does not work. Now, if you comment out lines 2,3,4
and re-execute the function from the spreadsheet, notice that it works.

Bob

 

[Clif McIrvin]

Ah.... I see now.

In my limited development, all my functions are called by subs, which
are called by toolbar command buttons.

 

[jaf]

Bob,
You answered the question.
The only way is to use a sub.
Try calling a separate sub from inside your function.


sub foo()
cells(1,1)="Kilroy was here"
end sub

function pie()
pie=3.1416
foo
end function


John

 

[Clif McIrvin]

Bob - did you get this issue resolved? It has stayed in the back of my
mind and will not go away.

I wonder if you can use an event procedure to do what you are looking
for.

If you're not familiar with writing event procedures (I use them in
Access, have not tried in Excel) go to your VBA help window and look up
SheetCalculate Event -or-
SheetChange Event
for a starting place.

From the example code it appears that you can manipulate worksheets from
an event, unlike the restriction on functions.

 

[Clif McIrvin]

jaf,

I thought the same thing but it doesn't work either .. at least in the
case where you call the function by calling the function from a cell
formula:

ie; A5 = pie()

will not work as expected from your code; at least in Excel 2003.

 

[Bob]

Hi Clif:

No I have not tried anything. For now, I guess, I am sticking to using
arrays to return multiple function values.

Bob

 

[Bob]

I wish people would test their suggestions before giving incorrect answers.
This method does not work.

 

[Clif McIrvin]

Hi Clif:

No I have not tried anything. For now, I guess, I am sticking to
using arrays to return multiple function values.

Bob

Bob, I learned something new about using events with Excel this week
(I've been using them in Access, but so far not in Excel) and in case it
might be useful for you I'll pass it along:

In an earlier reply I mentioned that events needed to be enabled --
turns out I was only partially correct. Workbook and worksheet events
are enabled by default, so are quite easy to use and require no class
module. Event procedures reside in either the workbook or the worksheet
(so far all my code has been placed in my PERSONAL.XLS workbook.)

From what I understand of your original post you want to use VBA code to
manipulate cells in a worksheet in response to a recalculation (that's
what I understand by your reference to a workbook function.)

Place this simple procedure in the workbook code module and it will pop
the msgbox whenever (after!) any sheet in the workbook is recalculated:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "Calculate!", vbInformation
End Sub

If I'm understanding your situation correctly all you need to is call
your subroutine (it doesn't need to be a function) from this procedure.
For testing, I opened a new workbook, set A1 to =B1 and added the
SheetCalculate procedure. So far, so good.

It appears from your example that your function is recursive: ie, you
wish to be able to modify A4 when =2*A2^A3+A4^2+5 is evaluated.

As expected, adding Range("b1") = "A" to my test procedure
resulted in an infinite loop. This procedure demonstrates a simple test
for recursion:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "Calculate!", vbInformation
If Range("b1") <> "A" Then
Range("b1") = "A"
MsgBox "Calculate2!", vbInformation
End If
End Sub

and worked as expected.

As I mentioned, this is a Workbook event procedure, and is called (by
Excel) anytime the Worksheet calculate event 'fires' for any worksheet
in the workbook. There are also Worksheet event procedures which are
called only when the worksheet they reside in fires the corresponding
event.

(VBA code is part of a workbook and/or worksheet template if you need
newly created worksheets or workbooks to have event handling.)

Here's the worksheet calculate procedure I used to solve my issue:

Private Sub Worksheet_Calculate()
' Speak Height calculation result
If ActiveCell.Address = "$B$4" Then
Range("E6:F6").Speak
End If
End Sub

(I was working through a stack of production drawings verifying the
math -- and it ocurred to me that Excel likely could speak the
calculation result which would save me the hassle of moving my eye focus
from the drawing to the screen & back for each calculation. Worked like
a charm!)


An Excel Help search for workbook object events or worksheet object
events gives you a good starting point for further reading.


An issue that will come up (and while I live with it I don't feel
comfortable with my level of knowledge) is macro security. I'm not
comfortable with setting Macro Security to Low (poor understanding of
the risks? Likely.) so my solution so far has been to add a digital
signature to any workbook that I have added code to. (I don't know if
templates will pass a digital signature along when a new copy is saved.)


I don't know if any of this will help you or not --- but I was so
excited about how well using events worked to solve my little issue, and
how easy it was to do that I just 'had' to share it with someone, and
your question came to mind.

So, fwiw, I pass this along.

 

[xharel]

Bob, I learned something new about using events withExcelthis week
(I've been using them in Access, but so far not inExcel) and in case it
might be useful for you I'll pass it along:

In an earlier reply I mentioned that events needed to be enabled --  
turns out I was only partially correct. Workbook and worksheet events
are enabled by default, so are quite easy to use and require no class
module. Event procedures reside in either the workbook or the worksheet
(so far all my code has been placed in my PERSONAL.XLS workbook.)

From what I understand of your original post you want to useVBAcode to
manipulate cells in a worksheet in response to a recalculation (that's
what I understand by your reference to a workbook function.)

Place this simple procedure in the workbook code module and it will pop
the msgbox whenever (after!) any sheet in the workbook is recalculated:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    MsgBox "Calculate!", vbInformation
End Sub

If I'm understanding your situation correctly all you need to is call
your subroutine (it doesn't need to be a function) from this procedure.
For testing, I opened a new workbook, set A1 to =B1 and added the
SheetCalculate procedure. So far, so good.

It appears from your example that your function is recursive: ie, you
wish to be able to modify A4 when =2*A2^A3+A4^2+5 is evaluated.

As expected, adding       Range("b1") = "A" to my test procedure
resulted in an infinite loop.  This procedure demonstrates a simple test
for recursion:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    MsgBox "Calculate!", vbInformation
    If Range("b1") <> "A" Then
      Range("b1") = "A"
      MsgBox "Calculate2!", vbInformation
    End If
End Sub

and worked as expected.

As I mentioned, this is a Workbook event procedure, and is called (byExcel) anytime the Worksheet calculate event 'fires' for any worksheet
in the workbook.  There are also Worksheet event procedures which are
called only when the worksheet they reside in fires the corresponding
event.

(VBAcode is part of a workbook and/or worksheet template if you need
newly created worksheets or workbooks to have event handling.)

Here's the worksheet calculate procedure I used to solve my issue:

Private Sub Worksheet_Calculate()
' Speak Height calculation result
  If ActiveCell.Address = "$B$4" Then
    Range("E6:F6").Speak
  End If
End Sub

(I was working through a stack of production drawings verifying the
math -- and it ocurred to me thatExcellikely could speak the
calculation result which would save me the hassle of moving my eye focus
from the drawing to the screen & back for each calculation.  Worked like
a charm!)

AnExcelHelp search for workbook object events or worksheet object
events gives you a good starting point for further reading.

An issue that will come up (and while I live with it I don't feel
comfortable with my level of knowledge) ismacrosecurity.  I'm not
comfortable with settingMacroSecurity to Low (poor understanding of
the risks? Likely.) so my solution so far has been to add a digital
signature to any workbook that I have added code to. (I don't know if
templates will pass a digital signature along when a new copy is saved.)

I don't know if any of this will help you or not --- but I was so
excited about how well using events worked to solve my little issue, and
how easy it was to do that I just 'had' to share it with someone, and
your question came to mind.

So, fwiw, I pass this along.

--
Clif






- Show quoted text -

my understanding is that macros can only be subs with no input
parameters.

This is why if you create a sub in the vba code area with no
parameters, and then press alt-F8 from the spreadsheet, your sub will
be listed in a macro list window. But if you add parameters to your
sub, it won't be listed in the macro list window anymore.

So the problem is really that you want to call code to do that update
from the macro interface of excel. You have also the event interface.
You could call for example the Workbook_SheetBeforeDoubleClick event,
which will fire if you double click on a cell. Or you can use the
macro interface by calling a sub with no parameters, which in turn can
call other subs or functions with parameters. The 2nd solution is
typically my choice in your situation.

You can create a no-parameter sub (example, Run_Selected below) that
in turn calls another sub or function (example, UpdateCell(Target as
Range, strValue as String)) with as many parameters as you like to do
the cell value updates you need to do.

example - this will loop through selected cells and call a
parametrized sub, that in turn will update the contents of a passed in
cell to "AAAA" if it is A4.

public sub Run_Selected()
Dim rngCell As Range
Dim rngCur As Range
' find out if any cell is selected
Set rngCur = Selection ' store the selected cells in range
rngCur
if rngCur is nothing then
msgbox "Nothing is selected, exiting..."
else
For Each rngCell In rngCur
' call a sub with parameters
UpdateCell rngCell,"AAAA"
Next
end if
End Sub

Sub UpdateCell(Target as Range, strValue as String)
if Target.Column = 1 and Target.Row=4 then
Target.Value=strValue
end if
End Sub

 

[Clif McIrvin]

Bob, I learned something new about using events withExcelthis week
(I've been using them in Access, but so far not inExcel) and in case
it
might be useful for you I'll pass it along:

In an earlier reply I mentioned that events needed to be enabled --
turns out I was only partially correct. Workbook and worksheet events
are enabled by default, so are quite easy to use and require no class
module. Event procedures reside in either the workbook or the
worksheet
(so far all my code has been placed in my PERSONAL.XLS workbook.)

From what I understand of your original post you want to useVBAcode to
manipulate cells in a worksheet in response to a recalculation (that's
what I understand by your reference to a workbook function.)

Place this simple procedure in the workbook code module and it will
pop
the msgbox whenever (after!) any sheet in the workbook is
recalculated:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "Calculate!", vbInformation
End Sub

If I'm understanding your situation correctly all you need to is call
your subroutine (it doesn't need to be a function) from this
procedure.
For testing, I opened a new workbook, set A1 to =B1 and added the
SheetCalculate procedure. So far, so good.

It appears from your example that your function is recursive: ie, you
wish to be able to modify A4 when =2*A2^A3+A4^2+5 is evaluated.

As expected, adding Range("b1") = "A" to my test procedure
resulted in an infinite loop. This procedure demonstrates a simple
test
for recursion:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "Calculate!", vbInformation
If Range("b1") <> "A" Then
Range("b1") = "A"
MsgBox "Calculate2!", vbInformation
End If
End Sub

and worked as expected.

As I mentioned, this is a Workbook event procedure, and is called
(byExcel) anytime the Worksheet calculate event 'fires' for any
worksheet
in the workbook. There are also Worksheet event procedures which are
called only when the worksheet they reside in fires the corresponding
event.

(VBAcode is part of a workbook and/or worksheet template if you need
newly created worksheets or workbooks to have event handling.)

Here's the worksheet calculate procedure I used to solve my issue:

Private Sub Worksheet_Calculate()
' Speak Height calculation result
If ActiveCell.Address = "$B$4" Then
Range("E6:F6").Speak
End If
End Sub

(I was working through a stack of production drawings verifying the
math -- and it ocurred to me thatExcellikely could speak the
calculation result which would save me the hassle of moving my eye
focus
from the drawing to the screen & back for each calculation. Worked
like
a charm!)

AnExcelHelp search for workbook object events or worksheet object
events gives you a good starting point for further reading.

An issue that will come up (and while I live with it I don't feel
comfortable with my level of knowledge) ismacrosecurity. I'm not
comfortable with settingMacroSecurity to Low (poor understanding of
the risks? Likely.) so my solution so far has been to add a digital
signature to any workbook that I have added code to. (I don't know if
templates will pass a digital signature along when a new copy is
saved.)

I don't know if any of this will help you or not --- but I was so
excited about how well using events worked to solve my little issue,
and
how easy it was to do that I just 'had' to share it with someone, and
your question came to mind.

So, fwiw, I pass this along.

--
Clif






- Show quoted text -

my understanding is that macros can only be subs with no input
parameters.

This is why if you create a sub in the vba code area with no
parameters, and then press alt-F8 from the spreadsheet, your sub will
be listed in a macro list window. But if you add parameters to your
sub, it won't be listed in the macro list window anymore.

So the problem is really that you want to call code to do that update
from the macro interface of excel. You have also the event interface.
You could call for example the Workbook_SheetBeforeDoubleClick event,
which will fire if you double click on a cell. Or you can use the
macro interface by calling a sub with no parameters, which in turn can
call other subs or functions with parameters. The 2nd solution is
typically my choice in your situation.

You can create a no-parameter sub (example, Run_Selected below) that
in turn calls another sub or function (example, UpdateCell(Target as
Range, strValue as String)) with as many parameters as you like to do
the cell value updates you need to do.

example - this will loop through selected cells and call a
parametrized sub, that in turn will update the contents of a passed in
cell to "AAAA" if it is A4.

public sub Run_Selected()
Dim rngCell As Range
Dim rngCur As Range
' find out if any cell is selected
Set rngCur = Selection ' store the selected cells in range
rngCur
if rngCur is nothing then
msgbox "Nothing is selected, exiting..."
else
For Each rngCell In rngCur
' call a sub with parameters
UpdateCell rngCell,"AAAA"
Next
end if
End Sub

Sub UpdateCell(Target as Range, strValue as String)
if Target.Column = 1 and Target.Row=4 then
Target.Value=strValue
end if
End Sub

===================

xharel, it's always pleasant to see someone else confirm something that
I think I understand! <grin>

However, I'm still not certain we have solved OP's question as he wanted
to launch this whole process from a worksheet formula. Seems to me he
could make the sheet calculate event work, but then I really don't know
his exact circumstances.