Some questions re VBA in Excel

[Deryck]

Hi,

I'm relatively new to using VBA in Excel and hope that someone can help me
with the following:

1. I have written some scripts that work in one workbook but open another
for reading data. In order to make sure that this file is never corrupted I
close the file at the end of my scripts and make sure that any changes are
discarded:

Workbooks(dsName).Close SaveChanges:=False

Unfortunately I still get the message concerning a large amount of stuff
being in the clipboard and do I want to save it, Yes/No/Cancel? I want to
protect the user from this question (not least because the Cancel option
will crash my script!). How can I do this? Is there is a command to close a
file silently or is there a way to empty the clipboard? As a corollary to
that, is there a "best-practice" method of trapping errors so that the
scripts can fail gracefully?

2. I am sure that this is possible but I cannot figure it out: In a sheet
each row represents some money received. One column denotes what form this
money was received in, eg "Cheque" and column has all the cells blank until
one of the monies received is banked (when the user can put any text into
the cell). I would like to be able to count up all the cheques that have
been banked. Is it possible to do this with Countif? It is easy to count all
the monies that are cheques and all that have been banked but I am not sure
how to AND the query.

3. I have some class modules. In one particular use-case a class public
subroutine gets called and that calls another private subroutine in the
class. In the private subroutine the script would crash but if I went into
debug the highlighted code is the call to the first public subroutine. Why
does the debugger not go into the class module and is there a way that I can
make it do so? It would make debugging a lot easier!

Any help/advice would be much appreciated,


Deryck

 

[Don Guillett]

Have you tried
application.displayalerts=false

 

[Bernie Deitrick]

Deryck,

1)
Application.DisplayAlerts = False
Workbooks(dsName).Close SaveChanges:=False
Application.DisplayAlerts = True

2)
If your "Cheque" is in Column A, and something in column B denotes that it
has been banked, and the amount is in column C, and you have 100 rows of
data:

=SUMPRODUCT((A1:A100="Cheque")*(B1:B100<>"")*C1:C100)

3) Chip Pearson will need to answer that one ;-)

HTH,
Bernie
MS Excel MVP

 

[Deryck]

Thanks Bernie and Don.

Problem 1 is fixed and I will certainly remember DisplayAlerts in future.

Regarding problem 2, SUMPRODUCT looks useful but I didnt want to sum the
value of the banked cheques, merely count how many cheques had been banked
(as opposed to cash I guess). There is no COUNTPRODUCT equivalent according
to the help facilities so would you care to give me another clue? ;-)

Problem 3 is interesting, sometimes the debugger will take you to the line
in the code (in the case of an undefined variable name for example) but in
other cases it won't enter the class at all (eg, I misspelt columnwidth in
the line columns(2).columnwidth = 40, which crashed the script at the call
to the subroutine that called the subroutine that had the typo').

Thanks again,

Deryck

 

[Frank Kabel]

Hi
you CAN use SUMPRODUCT to count the values that match your
criteria. That is if you only have conditions within
SUMPRODUCT it COUNTS

 

[Bernie Deitrick]

Deryck,

Use something like this to count:

=SUMPRODUCT((A1:A100="Cheque")*(B1:B100<>""))

HTH,
Bernie
MS Excel MVP

 

[Deryck]

Thanks Bernie. Maybe I'm missing something but as I understand it SUMPRODUCT
is expecting something of the form

=SUMPRODUCT(A1:100, B1:B100)

and will multiply all the components of the two arrays and then adds the
products. This isnt want I'm trying to achieve.

I appreciate your response and I apologise if I'm being too dumb to see the
answer in your reply.

Deryck

 

[Bernie Deitrick]

Deryck,

SUMPRODUCT does exactly that (multiply two arrays), except in this case the
arrays that it is multiplying are actually arrays of TRUE and FALSE values:

((A1:A100="Cheque")*(B1:B100<>""))
becomes
((TRUE,FALSE, etc..)*(TRUE,TRUE,etc...))
where the elements are multiplied together,
TRUE * TRUE = 1, FALSE * anything else = 0

When it SUMS these, you get a count of the numbers of times that both
conditions are TRUE.

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

TRY IT!!!

 

[Deryck]

I'm not exactly sure what youre shouting about Don but yes, I have tried all
the suggestions given to me (and various permutations of them)....now if I
could just get it to work

Deryck

 

[Deryck]

Hi Bernie,

At last I got your solution working in a cell formula (I dont know why it
wasnt working before), it works just like you said it would

Now if I could just figure out why:

chequesbanked = WorksheetFunction.SumProduct(("F2:F100" = "Cheque") *
("G2:G100" <> ""))

gives me the error:

'Unable to get the SumProduct property of the WorksheetFunction class'

I could die happy!

Thanks again for all your help so far

Deryck

 

[Bernie Deitrick]

Deryck,

Now you're switching to VBA! Oh no!

Dim ChequesBanked As Variant
ChequesBanked = Application.Evaluate("SUMPRODUCT((F2:F100 = ""Cheque"")*
(G2:G100 <> """"))")
MsgBox ChequesBanked

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

I got the impression that you were not trying the suggestions, just arguing
that the suggestions wouldn't work. You did NOT mention that you were trying
to do this in vba.

You must either use vba to place the formula OR use the range within the
formula in vba
range("a1")="=yourformula"
or
msgbox application.vlookup(range("a1"),range("b2:b200),2,0)
HOWEVER, sumproduct can't be used this way so use the evaluate suggestion

 

[Deryck]

I got the impression that you were not trying the suggestions, just arguing
that the suggestions wouldn't work. You did NOT mention that you were trying
to do this in vba.

Errrrm, lets see now....

1) the original post was titled "Some questions re VBA in Excel"
2) the first question began "I have written some scripts...." and included a
line of...VBA code
3) the second question made no reference to VBA I agree, but since it fell
between 2 other questions about VBA, what do you think I might have been
trying to do?

The second post was ambiguous I agree. Bernie's suggestion seemed "off the
wall": using a sum-type function for counting , but yes I tried it and when
it didnt work (for some as yet unknown reason) I wasnt surprised, it seemed
to be the wrong type of function to be using. Ditto, the third post. For the
record, if someone takes the time to reply to any question I ask in any
forum, I always try it out (unless the consequences are life threatening!)
and I always acknowledge help given - I am fully aware that this group is
NOT a 24/7 help desk.

I will try your latest suggestions, but not tonight. Its late and they merit
a clear head.

But thanks for replying and suggesting....

Cheers


Deryck

 

[Deryck]

2. I am sure that this is possible but I cannot figure it out: In a sheet
each row represents some money received. One column denotes what form this
money was received in, eg "Cheque" and column has all the cells blank until
one of the monies received is banked (when the user can put any text into
the cell). I would like to be able to count up all the cheques that have
been banked. Is it possible to do this with Countif? It is easy to count all
the monies that are cheques and all that have been banked but I am not sure
how to AND the query.

I never found a "neat" way to do the above in VBA but this works just fine:

Dim TotalRows As Long
Dim chequesbanked As Long

TotalRows = WorksheetFunction.CountA(range("A:A"))
chequesbanked = 0

Dim i As Integer

'skip row 1, the header row

For i = 2 To TotalRows
If (Cells(i, 6).Value = "Cheque") And (Cells(i, 7) <> "") Then
chequesbanked = chequesbanked + 1
End If
Next i

Not the one-liner that I was hoping for but easy to code and understand.

My grateful thanks to all those who offered advice.

Cheers

Deryck

 

[Don Guillett]

Instead of code why not just use the sumproduct formula.
OR
if you want in code, use code to place the formula and change to value as
suggested earlier.

 

[Deryck]

Instead of code why not just use the sumproduct formula.
OR
if you want in code, use code to place the formula and change to value as
suggested earlier.

Because I never got it to work...I coded my solution up in 2 minutes and it
worked first time.
I need stuff that works and works quickly. I dont have the time to play
around with this stuff....I'm doing this free of charge for a local charity.
I want to help them as much as possible but I also have to earn money for a
living.



Cheers Don,

Deryck