Hey all,

Please let me know if it is possible to insert Macros in a formula.
That is "if this condition is true, then run macro1 else macro 2".

If the above is not possible, let me know a procedure for this.
1 I need to create a macro which
i) Segregates all the users by their name.
ii) Calculate the time taken for their work.
iii) Create a report for the time frame taken for the task.

Now the problem is
1) when filtered, and sorted,
i) The time when subratcted from the previous task to present task
works. But when the first user data is finished, the time shows
negative for the second users first task time. How can this be avoided.


Please let me know if there are any other better options than the one I
am following to do this task.

Quick help is appreciated.

Thanks,
Pravi


--
praveen_khm
------------------------------------------------------------------------
praveen_khm's Profile: http://www.excelforum.com/member.php...o&userid=30364
View this thread: http://www.excelforum.com/showthread...hreadid=500862

Hi.

If I understand well what you want, it's very easy :-)

You need to create Functions (which return values ) instead of Subroutines
(the commonly known Macros, the same you get if you use the "Record Macro").

For example: you want a formula which retuns the value you have in another
cell * 10:
In VBA add a new Module and then add the following code:

Function MultyplyPer10(inValue As Range) 'inValue represents a Range, in
this case it will be a cell
MultyplyPer10 = inValue * 10 'You assign the value inValue*10 to the
Fuction MultiplyPer10
End Function

Now, suppose you digit in your Sheet, cell A1=13 and in cell
B1=MultiplyPer10(A1), in B1 the Function will return 130 (13*10)

For your case, you could create 3 functions:
-one called Macro1
-one called Macro2
-one called Fun3, for example:

Function Fun3(inValue As Boolean)
If inValue = True Then
Call Macro1
Else
Call Macro2
End If
End Function

Sub Macro1()
MsgBox "Macro1"
End Sub

Sub Macro2()
MsgBox "Macro2"
End Sub

Hope this could help you (I'm Italian, please excuse me for errors...)


Lorenzo


"praveen_khm" ha
scritto nel messaggio
...

Hey all,

Please let me know if it is possible to insert Macros in a formula.
That is "if this condition is true, then run macro1 else macro 2".

If the above is not possible, let me know a procedure for this.
1 I need to create a macro which
i) Segregates all the users by their name.
ii) Calculate the time taken for their work.
iii) Create a report for the time frame taken for the task.

Now the problem is
1) when filtered, and sorted,
i) The time when subratcted from the previous task to present task
works. But when the first user data is finished, the time shows
negative for the second users first task time. How can this be avoided.


Please let me know if there are any other better options than the one I
am following to do this task.

Quick help is appreciated.

Thanks,
Pravi


--
praveen_khm
------------------------------------------------------------------------
praveen_khm's Profile:
http://www.excelforum.com/member.php...o&userid=30364
View this thread: http://www.excelforum.com/showthread...hreadid=500862

I can not speak for anything Lorenzo wrote. I think the other issue you were
discussing is not resolved. I.e., that the data used to make your
calculations is coming out wacky, like negative this, or adding too many
numbers together. If each of your macros reset all "necessary" data to zero,
blank, empty or whatever prior to returning the values you want, then you
should be able to avoid incorrect summations. (Of course I also can't say I
totally understand what is happening, but seems like a good place to start.)

"Lorenzo" wrote:

Hi.

If I understand well what you want, it's very easy :-)

You need to create Functions (which return values ) instead of Subroutines
(the commonly known Macros, the same you get if you use the "Record Macro").

For example: you want a formula which retuns the value you have in another
cell * 10:
In VBA add a new Module and then add the following code:

Function MultyplyPer10(inValue As Range) 'inValue represents a Range, in
this case it will be a cell
MultyplyPer10 = inValue * 10 'You assign the value inValue*10 to the
Fuction MultiplyPer10
End Function

Now, suppose you digit in your Sheet, cell A1=13 and in cell
B1=MultiplyPer10(A1), in B1 the Function will return 130 (13*10)

For your case, you could create 3 functions:
-one called Macro1
-one called Macro2
-one called Fun3, for example:

Function Fun3(inValue As Boolean)
If inValue = True Then
Call Macro1
Else
Call Macro2
End If
End Function

Sub Macro1()
MsgBox "Macro1"
End Sub

Sub Macro2()
MsgBox "Macro2"
End Sub

Hope this could help you (I'm Italian, please excuse me for errors...)


Lorenzo


"praveen_khm" ha
scritto nel messaggio
...

Hey all,

Please let me know if it is possible to insert Macros in a formula.
That is "if this condition is true, then run macro1 else macro 2".

If the above is not possible, let me know a procedure for this.
1 I need to create a macro which
i) Segregates all the users by their name.
ii) Calculate the time taken for their work.
iii) Create a report for the time frame taken for the task.

Now the problem is
1) when filtered, and sorted,
i) The time when subratcted from the previous task to present task
works. But when the first user data is finished, the time shows
negative for the second users first task time. How can this be avoided.


Please let me know if there are any other better options than the one I
am following to do this task.

Quick help is appreciated.

Thanks,
Pravi


--
praveen_khm
------------------------------------------------------------------------
praveen_khm's Profile:
http://www.excelforum.com/member.php...o&userid=30364
View this thread: http://www.excelforum.com/showthread...hreadid=500862

Thanks Lorenzo and GB,

I have completed the project. However, Lornezo, I tried to create tw
macros within a function. It worked. Today, I ll be presenting th
project I did. Thanks, both for your kind replies. :

--
praveen_kh
-----------------------------------------------------------------------
praveen_khm's Profile: http://www.excelforum.com/member.php...fo&userid=3036
View this thread: http://www.excelforum.com/showthread.php?threadid=50086