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
----------------------------------
Tidak ada komentar:
Posting Komentar