You're asking a question about VBA, so you've come to the right place!
You said"When I try to "get" the return in VBA, it ends up being the formula,
not the executed function's value."
that shouldn't happen.
How are you trying to "get" it?
For a given cell (D4):
If the cell is an excel formula (like =vlookup() or a calculation),
Range("D4").Formula
will return the formula but
Range("D4").Value
should return the resulting value.
I'm not sure how you're going to get past using an event function though.
I'll have to think on it.
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: "[email protected]" <[email protected]>
>To: [email protected]
>Sent: Friday, September 5, 2014 9:22 AM
>Subject: $$Excel-Macros$$ Can UDF entered as a formula in names return a value
>to VBA?
>
>
>
>Hi folks, hope you don't mind the cross-posting, other
group's read my post twice and replied none where I originally put it on 9/3
and after scanning the other recent postings it appears they don't seem to
center on the kind of issue I've asked about
>
>.
>.
>.
>
>I've got a function requiring a user to enter a table and
my UDF needs to know when the table gets created/changed/updated. It can then
be uploaded and used in a public memory
variable which should speed up the spreadsheet.
>
>So far, the main idea is to run a simple checksum
algorithm on it, but I'm trying to avoid placing the checksum under the table
or "invisible" on a sheet, so instead I placed it as a formula in
names.
>
>When I try to "get" the return in VBA, it ends
up being the formula, not the executed function's value. An entry on the
spreadsheet sees it as a
function call and works fine.
>I like the idea of using the names domain because I can
check if it exists to facilitate initialization (function tells user to enter a
keystroke to invoke a macro, which sorts the keys table, etc. and creates/runs a
checksum). Further, I'm trying to avoid
an event driven solution because I want to offer the function to others at work
only in the form of an import as module (can't import to ThisWorkbook or have
the macro write code in ThisWorkbook without some form of user instructions to
add appropriate additional
library access, right?).
>Is there a way to execute the function in names from a
UDF in a VBA module, make it update if the table (computed checksum) changes,
or does anyone have any suggestion that will allow for the same initialization
scenario
without having to code beyond a module/UDF?
>TIA,
>KK's Dad (my rugrats account)
>P.S. If I've done
it again ... posted to a group that focuses on other topics ... please suggest
a more appropriate newsgroup?
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to [email protected].
>To post to this group, send email to [email protected].
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
