Normally you can supply built-in functions with information from local
strings or arrays, however, my Googling is suggesting that Excel DSum()
absolutely requires a range of cells in the criteria. (There are lots of
examples that supply a string to MS Access DSum() calls, but they have
different criteria requirements.)
What you do find in Googling for Excel solutions is people using other
mechanisms, such as SumProduct(), instead.
You might find that you're better off with an actual DSum() formula directly
in your worksheet, including a criteria range area. You could then change
your code into a sub and have it plug criteria into the criteria area, then
get it to grab the result of the DSum() formula. (You might need to force a
recalculate before retrieving the result.)
Regards, Dave S
----- Original Message -----
From: "BarryN" <brnaugle@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, November 04, 2011 2:27 AM
Subject: [ExcelVBA] Re: Assigning value to a cell
Thanks Dave - I think you've hit on it. I was trying to create a function
that would put inputs into a DSUM criteria range and then return the DSUM
result.
Peter and Derek - Unfortunately the code never got to the misspelling but
thanks for the heads-up. It's little things like that that kept me from
being a good programmer.
Barry
--- In ExcelVBA@yahoogroups.com, <david.smart@...> wrote:
>
> Aha ...
>
> Firstly, my comment about event handlers would apply here too. I assume
> you use sumit() in a formula. As such, it will execute during the
> recalculation events. Any error will simply make the execution go away.
>
> Now, your particular problem. Sorry, I missed the work "function" in your
> earlier explanations. That is where your problem lies. A function is not
> allowed to have side-effects such as changing the contents of cells, so is
> never going to allow you to do things like this.
>
> Are your cells() calls in there for debugging purposes? You could
> probably put the information into the status bar (I don't think that would
> constitute a side effect), or use MsgBox calls.
>
> If you are not calling this function from a formula, then you could move
> the cells() calls out to the calling sub.
>
>
> Regards, Dave S
>
>
> ________________________________
>
> From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
> Of BarryN
> Sent: Thursday, 3 November 2011 01:44
> To: ExcelVBA@yahoogroups.com
> Subject: [ExcelVBA] Re: Assigning value to a cell
>
>
>
>
> no protection....
>
> if i run it in a sub procedure it works fine, but i'm running it as a
> function procedure.
>
> This works:
>
> Public Sub sumit2()
>
> Dim d1 As Date
> Dim d2 As Date
> Dim cat As String
> Dim sumitanswer As Double
> Dim criteria2 As Range
> Dim rngN03 As Range
>
> d1 = "1 / 1 / 2011"
> d2 = "6 / 30 / 2011"
> cat = "Mortgage"
>
> Set rngN03 = Range("rngn03")
> Set criteria2 = Sheets("Adjustments").Range(Cells(1,3), Cells(3,12))
> Sheets("Adjustments").Cells(2, 3) = ">=" & d1
> Sheets("Adjustments").Cells(3, 3) = "<=" & d2
> Sheets("Adjustments").Cells(2, 8) = cat
>
> sumitanswer = WorksheetFunction.DSum(rngN03, "Amt", criteria2)
>
> End Sub
>
> This does not:
>
> Public Function sumit(Dat1 As Date, dat2 As Date, cat As String) As Double
>
> Dim criteria As Range
> Dim rngN03 As Range
>
> Set rngN03 = Range("rngn03")
> Set criteria = Sheets("Adjustments").Range(Cells(1, 3), Cells(3, 12))
> Sheets("Adjustments").Cells(3, 2) = ">=" & Dat1
> Sheets("Adjustments").Cells(3, 3) = "<=" & dat2
> Sheets("Adjustments").Cells(2, 8) = cat
> summit = WorksheetFunction.DSum(rngN03, "Amt", criteria)
>
> End Function
>
> --- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ,
> Derek Turner <g4swy@> wrote:
> >
> > Protection ?
> >
> >
> >
> > >________________________________
> > >From: "david.smart@" <david.smart@>
> > >To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > >Sent: Wednesday, 2 November 2011, 2:04
> > >Subject: RE: [ExcelVBA] Re: Assigning value to a cell
> > >
> > >
> > >Â
> > >Can you post more of the code. Certainly, this code works fine for me:
> > >
> > >Option Explicit
> > >
> > >Sub x()
> > >Dim D1 As Date
> > >D1 = Now()
> > >Sheets("Adjustments").Cells(3, 2) = ">=" & D1
> > >End Sub
> > >
> > >
> > >Regards, Dave S
> > >
> > >________________________________
> > >
> > >From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > >[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ]
> > >On
> > >Behalf Of BarryN
> > >Sent: Wednesday, 2 November 2011 11:49
> > >To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > >Subject: [ExcelVBA] Re: Assigning value to a cell
> > >
> > >Thanks Dave - but this is in a function procedure where D1 and D2 are
> > >inputs defined as Date. I've tried adding "Range", "Value" etc without
> > >success and suspect there is something in the references or elsewhere
> > >messing this up.
> > >
> > >Barry
> > >
> > >--- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > ><mailto:ExcelVBA%40yahoogroups.com> ,
> > ><david.smart@> wrote:
> > >>
> > >> Firstly ... why it disappears rather than throwing an error ... I'm
> > >> assuming that this line of code is in an event handler, e.g. a change
> > >> event. When Excel encounters a problem in an event handler, it
> > >> terminates the event without further notification.
> > >>
> > >>
> > >> > Sheets("Adjustments").cells(3,2)=">=" & D1
> > >>
> > >> This requires that you have a sheet called Adjustments and a variable
> > >> called D1. I suspect you actually want to refer to the cell D1, which
> > >> will require a Range("D1"), possibly also with a sheet qualifier.
> > >>
> > >> Also, please use .Value or .Formula as appropriate, so that Excel
> > >knows
> > >> where to put/get the information, so
> > >>
> > >> > Sheets("Adjustments").cells(3,2).Value = ">=" & Range("D1").Value
> > >>
> > >> Also, if D1 has a date in it, then you probably want to convert it to
> > >a
> > >> date string before you append it to the >=. Otherwise you'll just get
> > >> the numeric value of the date, I suspect.
> > >>
> > >>
> > >> Regards, Dave S
> > >>
> > >>
> > >>
> > >> ________________________________
> > >>
> > >> From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > >> <mailto:ExcelVBA%40yahoogroups.com>
> > >[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > ><mailto:ExcelVBA%40yahoogroups.com> ]
> > >On
> > >> Behalf Of BarryN
> > >> Sent: Wednesday, 2 November 2011 01:16
> > >> To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > >> <mailto:ExcelVBA%40yahoogroups.com>
> > >> Subject: [ExcelVBA] Assigning value to a cell
> > >>
> > >>
> > >>
> > >>
> > >> I've done this dozens of times but when I step through a function to
> > >> this line of code:
> > >>
> > >> Sheets("Adjustments").cells(3,2)=">=" & D1
> > >>
> > >> where D1 is a date, the yellow line disappears and the function
> > >> stops.
> > >>
> > >> Obvious solution but I can't see it - thanks!
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> ----------
> > >>
> > >> Visit our website at http://www.ubs.com
> > >>
> > >> This message contains confidential information and is intended only
> > >> for the individual named. If you are not the named addressee you
> > >> should not disseminate, distribute or copy this e-mail. Please
> > >> notify the sender immediately by e-mail if you have received this
> > >> e-mail by mistake and delete this e-mail from your system.
> > >>
> > >> E-mails are not encrypted and cannot be guaranteed to be secure or
> > >> error-free as information could be intercepted, corrupted, lost,
> > >> destroyed, arrive late or incomplete, or contain viruses. The sender
> > >> therefore does not accept liability for any errors or omissions in
> > >> the
> > >
> > >> contents of this message which arise as a result of e-mail
> > >transmission.
> > >> If verification is required please request a hard-copy version. This
> > >> message is provided for informational purposes and should not be
> > >> construed as a solicitation or offer to buy or sell any securities
> > >> or related financial instruments.
> > >>
> > >>
> > >> UBS reserves the right to retain all messages. Messages are protected
> > >> and accessed only in legally justified cases.
> > >>
> > >> [Non-text portions of this message have been removed]
> > >>
> > >
> > >----------
> > >
> > >Visit our website at http://www.ubs.com
> > >
> > >This message contains confidential information and is intended only
> > >for the individual named. If you are not the named addressee you
> > >should not disseminate, distribute or copy this e-mail. Please
> > >notify the sender immediately by e-mail if you have received this
> > >e-mail by mistake and delete this e-mail from your system.
> > >
> > >E-mails are not encrypted and cannot be guaranteed to be secure or
> > >error-free as information could be intercepted, corrupted, lost,
> > >destroyed, arrive late or incomplete, or contain viruses. The sender
> > >therefore does not accept liability for any errors or omissions in the
> > >contents of this message which arise as a result of e-mail
> > >transmission.
> > >If verification is required please request a hard-copy version. This
> > >message is provided for informational purposes and should not be
> > >construed as a solicitation or offer to buy or sell any securities
> > >or related financial instruments.
> > >
> > >UBS reserves the right to retain all messages. Messages are protected
> > >and accessed only in legally justified cases.
> > >
> > >[Non-text portions of this message have been removed]
> > >
> > >
> > >
> > >
> > >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> ----------
>
> Visit our website at http://www.ubs.com
>
> This message contains confidential information and is intended only
> for the individual named. If you are not the named addressee you
> should not disseminate, distribute or copy this e-mail. Please
> notify the sender immediately by e-mail if you have received this
> e-mail by mistake and delete this e-mail from your system.
>
> E-mails are not encrypted and cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The sender
> therefore does not accept liability for any errors or omissions in the
> contents of this message which arise as a result of e-mail transmission.
> If verification is required please request a hard-copy version. This
> message is provided for informational purposes and should not be
> construed as a solicitation or offer to buy or sell any securities
> or related financial instruments.
>
>
> UBS reserves the right to retain all messages. Messages are protected
> and accessed only in legally justified cases.
>
> [Non-text portions of this message have been removed]
>
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1411 / Virus Database: 1522/3993 - Release Date: 11/03/11
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar