Stuck with multi function Part 2

[Cimjet]

If I post a link to the file in question would someone have a look at it and see
if they can make it work.
Regards
Cimjet

Hi Clif
Let me try to answer those questions.I'm not sure what is a Volatile UDF. I know what is a UDF ans I can't use that
because the problem is that they keep removing the formula in the cells and
they don't want lock cells (No Protection ) Too many people protecting
worksheets then moving away and no longer can work with the file.It's ok if the formula returns by itself, that's one reason why I use the
Sheet-change.The answer is "Yes
------------------------
My last post with Javed is possibly the answer if we can run the formula for
two different cells e.g. "I50 & I51"
Thank you for your help
Regards
Cimjet

Hi Joeu2004
I appreciate your patient with me and will start over with one code.
It gets to confusing with all the codes we have tried.

Cimjet ...

First, I appreciate that you are working in two unfamiliar languages at the
same time: English *and* VBA.

If you noticed, Javed and joeu2004 both mentioned that using the
WorksheetChange event is causing you trouble.

I encourage you to work your way through joeu2004's list of questions (I
included them below) and answer them in-line. When you understand his
questions you will have learned much more about working with VBA; and when
you answer his questions it will be much easier for us to help you.

Keeping the questions and answers together in the reply makes it much easier
for everyone to keep track of the details.


[ ]

Why is this a SheetChange event macro?
[....]
Perhaps you are trying to emulate a volatile UDF
(VBA user-defined function).

Cimjet, does the following meet your needs?

If not, please explain why it does not. That might provide useful
insight into your requirements.

Put the following UDF into a VBA module, __not__ into a Sheet or
ThisWorkbook Excel object.

The UDF does not have to be volatile if the ranges are specified as
parameters, as intended. (But see alternative below.)

Function myCountIf(s As String, ParamArray a())
Dim r As Variant
For Each r In a
myCountIf = _
myCountIf + WorksheetFunction.CountIf(r, s)
Next
End Function

Then put the following formulas into the appropriate Excel cells:

I50: =mycountif("½V",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47)
I51: =mycountif("V",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47)
I52: =mycountif("i",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47)
I53: =mycountif("½i",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47)

Note that myCountIf is not limited to 3 ranges. You can have a many
as you want -- well, up to 29 in XL2003. (More in XL2007 and later.)

However, in another thread, you wrote:
I prefer not to copy the formula in the cell
but if it's the only way then ok

As I noted previously, it is unclear why you "prefer" not to copy the
formula. It is not the "only" way; but it might be the best way.

If you prefer, you can hardcode the ranges in the UDF, just as you
were doing in the SheetChange event macro.

But in that case, the UDF must be made volatile. To wit:

Function myCountIf(s As String)
Dim r As Variant
Application.Volatile
For Each r In Array("$B$6:$AF$17", "$B$21:$AF$32", "$B$36:$AF$47")
myCountIf = _
myCountIf + WorksheetFunction.CountIf(Range(r), s)
Next
End Function

However, the only advantage of the volatile UDF is that the strings do
not have to be hardcoded.

If you prefer to hardcode the strings as well, there is no advantage.

Moreover, the advantage of the SheetChange event macro over a volatile
UDF is the ability to limit when the computation is performed by
comparing with the sheet name (Sh.Name) and Target.

If you want to limit the SheetChange computation based on Target, you
need to provide more information, namely: when do you want to perform
the computation? That is, when which cells (ranges) are edited?

I am guessing only when $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47 are
edited. Right?

 

[joeu2004]

If I post a link to the file in question would someone
have a look at it and see if they can make it work.

Yes. That is a "good practice". If you do not have a public file-
sharing website to use, see the list at the bottom. Be sure the file
is "shared" or "sharable". It would be best if you tested the URL
("link") by logging out of the website and simply entering the URL
into a browser to be sure that we will be able to download the file.

For my benefit, it would be best if you uploaded an XL2003-compatible
file. But if that is too much trouble, upload a normal file. If I
cannot open it, someone else probably can.

However, you still need to answer a critical question: when is it
necessary to recompute I50:I53? Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47?

Cimjet wrote elsewhere:

If I remove the formula for the Range ("I50") and
leave Range ("I51") working, it works perfectly
but with both Range it seem to go in a loop

I believe I answered that, although I would expect that you would get
an infinite loop with either Range assignment.

If you use a SheetChange event macro, you need to disable events in
the macro so that the assignment to a range within the macro does not
trigger another event.

Cimjet wrote elsewhere:

I'm not sure what is a Volatile UDF.
I know what is a UDF

A volatile UDF (user-defined function) is a VBA function that is
executed each time any cell in the workbook is modified by some user
operation (editing, sorting, deleting, etc). It is VBA function that
starts with Application.Volatile.

I can't use that because the problem is that they
keep removing the formula in the cells and they
don't want lock cells (No Protection ) Too many
people protecting worksheets then moving away and
no longer can work with the file.

That's an operational problem you and your management should try to
fix. When an employee, contractor or volunteer leaves, part of the
exit procedure should be to sit down with them and be sure that all
critical files are usable without them.

Usually, it is not difficult to "break" Excel's cell protection
mechanism. Some procedures are described on websites. Search for
"excel cell protection removal" without quotes.

Some free file-sharing websites that people have suggested or used....

Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box.Net: http://www.box.net/files

I use bot.net myself.

 

[Clif McIrvin]

[ ]
However, you still need to answer a critical question: when is it
necessary to recompute I50:I53? Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47?

Hi Clif
Let me try to answer those questions. [ ]

If you want to limit the SheetChange computation based on Target,
you
need to provide more information, namely: when do you want to
perform
the computation? That is, when which cells (ranges) are edited?
I am guessing only when $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47
are
edited. Right?

The answer is "Yes

 

[Cimjet]

Hi Joeu2004
To answer your questions.
"Yes" Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47? but not critical if it makes
any different.
I'm using XL2003.
I'm retired, its my wife's office and the people that set rules are not always
the "sharpest tool in the shed" if you know what I mean.
This is the link; http://cjoint.com/?AEmvV7hxz6s
You'll see all the different macro I tried.
Thanks
Cimjet

If I post a link to the file in question would someone
have a look at it and see if they can make it work.

Yes. That is a "good practice". If you do not have a public file-
sharing website to use, see the list at the bottom. Be sure the file
is "shared" or "sharable". It would be best if you tested the URL
("link") by logging out of the website and simply entering the URL
into a browser to be sure that we will be able to download the file.

For my benefit, it would be best if you uploaded an XL2003-compatible
file. But if that is too much trouble, upload a normal file. If I
cannot open it, someone else probably can.

However, you still need to answer a critical question: when is it
necessary to recompute I50:I53? Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47?

Cimjet wrote elsewhere:

If I remove the formula for the Range ("I50") and
leave Range ("I51") working, it works perfectly
but with both Range it seem to go in a loop

I believe I answered that, although I would expect that you would get
an infinite loop with either Range assignment.

If you use a SheetChange event macro, you need to disable events in
the macro so that the assignment to a range within the macro does not
trigger another event.

Cimjet wrote elsewhere:

I'm not sure what is a Volatile UDF.
I know what is a UDF

A volatile UDF (user-defined function) is a VBA function that is
executed each time any cell in the workbook is modified by some user
operation (editing, sorting, deleting, etc). It is VBA function that
starts with Application.Volatile.

I can't use that because the problem is that they
keep removing the formula in the cells and they
don't want lock cells (No Protection ) Too many
people protecting worksheets then moving away and
no longer can work with the file.

That's an operational problem you and your management should try to
fix. When an employee, contractor or volunteer leaves, part of the
exit procedure should be to sit down with them and be sure that all
critical files are usable without them.

Usually, it is not difficult to "break" Excel's cell protection
mechanism. Some procedures are described on websites. Search for
"excel cell protection removal" without quotes.

Some free file-sharing websites that people have suggested or used....

Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box.Net: http://www.box.net/files

I use bot.net myself.

 

[joeu2004]

However, you still need to answer a critical question:  when is it
necessary to recompute I50:I53? [....]

Thanks, Clif. "Couldn't see the forest for the trees" ;-). My bad!

 

[joeu2004]

This is the link;  http://cjoint.com/?AEmvV7hxz6s

See my modified version at http://www.box.net/shared/x1r3a1mv03 .

Note: You can use that file as-is. But for the final "product", I
suggest that you copy the text of my macros and paste them into your
file.

Reason: When I saved the modified file on my system, I got some
warnings. I did not bother to think about the consequences of those
warnings, if any.

My point is: my file might not be identical to yours in all
respects. It would be better to use it as a prototype, not a final
"product".

My modified file contains 3 macros:

Workbook_SheetChange in ThisWorkbook:
- simply calls fillFormulas

fillFormulas in Module1:
- does all the work

chkFormulas in Module1:
- calls fillFormulas for all appropriate worksheets.
- "appropriate" means: I49 contains the word "Recorded".

If you choose to use chkFormulas, the SheetChange event macro might
not be needed. That's a judgment call -- yours.

The SheetChange event macro puts the formulas into only the worksheet
that caused the event -- that is, the changed worksheet -- if
necessary.

I put very few comments into the macros, in part to make them concise
and readable. I am hoping that the programming is clear.

If you have any questions, you can write to me at joeu2004 "at"
hotmail.com.

Essentially, fillFormulas put the appropriate formulas into I50:I54.
It is best to let Excel perform the computation, if possible.

When the formulas are in place, the SheetChange event macro will do
almost nothing. That makes the overhead of the event macro very
little.

Note that I guessed at what is an "appropriate" formula. I hope I got
it right. For each of I50:I54, I count the number of "I" for I50, "V"
for I51, "P" for I52 etc. I also count 1/2 the number of "½I" for
I50, "½V" for I51, "½P" for I52 etc.

Thus, for example, the formula in I50 becomes:

=COUNTIF(B6:AF17,"I")+COUNTIF(B6:AF17,"I")
+COUNTIF(B21:AF32,"I")+COUNTIF(B36:AF47,"I")
+ (COUNTIF(B6:AF17,"½I")+COUNTIF(B6:AF17,"½I")
+COUNTIF(B21:AF32,"½I")+COUNTIF(B36:AF47,"½I"))/2

I hope I guessed your intentions correctly. If not, send me the
correct formula to use for each of I50:I54, and I will make the
necessary change if you cannot do it yourself.

Hope this is a step in the right direction. Please let me know one
way or the other (email or a reply here).

 

[Vacuum Sealed]

Hi all

Just as a kinda off-topic contribution..

Cimjet

there is an Excel file available that actually unlocks protected sheets.

the files is titled "AllInternalPasswords.xls"

Breaks worksheet and workbook structure passwords.
Bob McCormick probably originator of base code algorithm modified for
coverage of workbook structure / windows passwords and for multiple
passwords.

Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1)
Modified 2003-Apr-04 by JEM: All msgs to constants, and eliminate one
Exit Sub (Version 1.1.1)

Reveals hashed passwords NOT original passwords


I can't remember where I downloaded it, but it has come in very handy,
especially since I have had to unlock workbooks that have been protected by
redundant employees.

HTH
Mick

 

[Vacuum Sealed]

Cimjet

Here is the link should you or anyone else be interested.

http://www.mcgimpsey.com/excel/removepwords.html

Mick

 

[Cimjet]

Thank you Vacuum Sealed