"Chip Pearson" wrote:
Jim,
I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel
functions don't seem to operate in the fashion that C functions
operate in. The := stuff is really bugging me, big time. It almost
appears that they originally were thinking that RowAbsolute:=True
and ColumnAbsolute:=True could be placed anywhere in the
function parameter list in any order, but I doubt that's the case.
In fact, that is indeed the case. When you use Named Arguments, you can
put
them in any order you like. However, once you use one named argument in a
function call, you must use named arguments for all subsequent arguments.
For example, the following calls are syntactically correct,
r = f(1,2,3,4)
r = f(1, 2, c:=1, d:=4)
but
r =f(1,2,c:=1,4)
is not.
That explains a lot of the confusion I was going through. Thanks for
the explanation!
Would you know if all varieties of VBApp are like that? That is,
Access and PowerPoint? I know vbScript runs through a different
interpreter and is useful for networking, scheduling, email, user login
information, etc and most vbScript is CreateObject("object") and
then using the object.properties / methods.
Right now I'm referencing DAO through Excel to grab data and
put the information into a few Excel spreadsheets. Things are
working out very well, better than I could have ever imagined.
VBApp is a little slow, but I'm going to try setting AutoCalc off
as I've read in some of Ogrevies statements in this newsgroup.
;-) He reminds me of an ogre and thus I remember his name.
I promise not to refer to you as Frito-Lay. Maybe Ruffles, but
definitely not Frito-Lay. ;-)
You don't have to use Named Argument if you prefer the "C-style"
positional syntax, but named arguments make the code vastly more
readable. For example, which is more self-documenting?
r = f(0,,,,,,,,,,,,,,,,1)
or
r = f(arg1:=0,arg15:=1)
Things sure do look different when I get a grip on what's really
happening. Thanks much.
--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
"Jim Carlock" wrote in message
...
Thanks Chip.
I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel
functions don't seem to operate in the fashion that C functions
operate in. The := stuff is really bugging me, big time. It almost
appears that they originally were thinking that RowAbsolute:=True
and ColumnAbsolute:=True could be placed anywhere in the
function parameter list in any order, but I doubt that's the case.
I can see the names being used as place holders which means...
it's all very interpretive.
I'm babbling now.
Guessing I'll need to create a function to check to check to see
if the strings contain $. InStr should work well enough for that.
Thanks for your time.
--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!
"Chip Pearson" wrote in message
...
Jim,
You can use the HasFormula property to determine whether a cell
contains
a
formula. E.g.,
If ActiveCell.HasFormula = True Then
' do something
End If
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
"Jim Carlock" wrote in message
...
In addition to determining if the cell is absolutely or relatively
referenced, what's the suggested mannerism for determining
if there's a formula in the cell?
If (cell holds a formula) Then
If (cell is relatively referenced) Then
ActiveCell.Formula = Application.ConvertFormula( _
ActiveCell.Formula, _
FromReferenceStyle:=xlA1, _
ToAbsolute:=True)
Else
ActiveCell.Formula = Application.ConvertFormula( _
ActiveCell.Formula, _
FromReferenceStyle:=xlA1, _
ToAbsolute:=False)
End If
End If
--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!
"Jim Carlock" wrote in message
...
Thanks Chip.
Ended up using the following to get what I wanted.
ActiveCell.Formula = Application.ConvertFormula( _
ActiveCell.Formula, _
FromReferenceStyle:=xlA1, _
ToAbsolute:=True)
Took me a while to figure out that xlA1 wasn't a cell and that it
was a style of referencing cells. I was going nuts wondering why
you gave an example that referenced Cell ("A1").
Now I'm wondering how to get to the ToAbsolute information
so that I can read that information.
--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!
"Chip Pearson" wrote in message
...
You can use the Application.ConvertFormula method to change a
formulas
reference style.
Range("A1").Formula =
Application.ConvertFormula(Range("A1").Formula,
_
FromReferenceStyle:=xlA1, ToAbsolute:=True)
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com