Inexplicable VBA errors in formulas

[Dallman Ross]

I use Excel 2002 under XP Pro SP2.

Perhaps there are some memory limits, in particular to do with
named expression/ranges -- which I use extensively -- that could
be causing unexplained corruption in my formulas when I populate
ranges via VBA.

Does anyone know, for example, why row ranges in formulas might
suddenly start showing up as Roman numerals, and relative references
go kablooey (highly technical term, there)?

Here's a code snippet:

' data validation for Order #
With .Range("B2", Cells(myRow, 2))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(B:B,B2)>1"
.FormatConditions(1).Interior.ColorIndex = 22 ' pink

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
.FormatConditions(2).Interior.ColorIndex = 24
End With

I run the macro. Things that should be pink aren't. I look in
the conditonal formatting for the cells in question. I see,
e.g., for cell B2:

=COUNTIF(II:II,HI3)>1


Can someone explain that?!

I change that manually to

=COUNTIF(B:B,B2)>1

and the row's cells format as I expected and wanted.

There are other relative-reference errors as well. I have a couple
of fairly long named expressions, which I created with the free
Add-In "Names Manger" (the current version). Could I be overrunning
some program limit and corrupting memory or something? Any ideas
would be most appreciated.

A long related posting of mine from the other day that exlains some of
the other errors is here in this group as
Message-ID: <[email protected]>, which I posted on
the 17 July 2007.

 

[Dave O]

Those aren't roman numerals, they're column ii (eye-eye) references.
The snippet you posted doesn't have any copy paste code in it, but my
guess is somewhere along the line certain cells are pasted and the
references shift accordingly. Some debugging will be required to
figure this out: can you post the entire routine?

 

[Dallman Ross]

Those aren't roman numerals, they're column ii (eye-eye)
references. The snippet you posted doesn't have any copy paste

Hi, Dave,

Well, while I appreciate your answer and willingness to help, I
am left wondering about your citation. I Google on "column ii"
and "eye-eye" with "reference" and "Excel" and come up with
nothing useful or common that I can see. Moreover, how did
they get turned on, and how do I turn them off?

code in it, but my guess is somewhere along the line certain
cells are pasted and the references shift accordingly. Some
debugging will be required to figure this out: can you post the
entire routine?

Also, my code snippet does indeed populate Column B with the
conditional-formatting formula I gave. (Or rather, a corrupt
version of it.) I'm certainly willing to post the whole megillah,
but I'm a bit skeptical that it will help shed much light here.

Basically, relative references that my VBA code produce to populate
cells (whether the cells themselves or conditional formatting
formulas) no longer work right for me in this workbook. I can't
even get OFFSET, ADDRESS, or INDIRECT to work in their stead
anymore in this VBA. It comes out shifted or wrong. Again,
the only explanation I can conceive of is memory corruption,
as in I've probably exceeded some upper bound somewhere and
am causing the VBA to overwrite itself or something.

I'll put the whole thing on a web page and leave it up for
a couple of weeks at least. Okay, the macro is at

http://heliotropos.com/xl/tmp/deBruinMerge.txt

If you search on

' data validation

or even just "validation" in there, you'll find the
snippet I posted in the previous message.

The worksheet the VBA produces looks like this example:

http://heliotropos.com/xl/tmp/OpenOrders.jpg

How long may "name" formulas in Excel 2002 be, anyway?
There is one long one in particular that, when I look at
it in Names Manager, only shows up in part. I'm pretty sure
it's longer than 256 chars, for example. I'm concerned
that I can't see the end of the expression. This leads
to my speculation above about corruption of memory addresses.

Any help resolving this mystery will be most appreciated!

Dallman

========================
[My previous message in pertinent part:]

 

[Dallman Ross]

In <[email protected]>, Dave O
<[email protected]> spake thusly:

Basically, relative references that my VBA code produce to
populate cells (whether the cells themselves or conditional
formatting formulas) no longer work right for me in this
workbook. [. . .]

I've tested things more by removing the long named expressions
from the Names Manager and performing a couple of other tests.
That was not the problem. The references shift each time
the code is run. If I close the book and re-open and run
again, it moves back to the beginning. But the strange
"II:II" references (that you called "eye-eye") are still there,
and the conditonal-format formulas won't work until I change
those to B:B (manually). And when I run the code again
the relative references shift out of sync, and I get
a dozen or so extra blank columns on the right end of
the sheet. (See "kludge" in my code.)

Maybe where I have "clear" (for the cells on the sheet)
I will need to delete. But then my named expressions
lose their references and have them replaced by error messages.

I'll put the whole thing on a web page and leave it up for a
couple of weeks at least. Okay, the macro is at

http://heliotropos.com/xl/tmp/deBruinMerge.txt

If you search on

' data validation

or even just "validation" in there, you'll find the snippet I
posted in the previous message.

The worksheet the VBA produces looks like this example:

http://heliotropos.com/xl/tmp/OpenOrders.jpg

Dallman

 

[Dallman Ross]

Those aren't roman numerals, they're column ii (eye-eye)
references.

Dave, can you clarify further about that? I can't find anything
useful via the usual search engines on the web for "eye-eye."
In any case, they cause my formatting formula not to work.

Anyway, though, here is a happy update: I solved my problem. I
changed the code simply by making the column references absolute
rather than relative, and the "II:II" stuff went away and "$B:$B"
showed up and my conditonal formats are now working. I can't
explain it, though, and I'd sure like to understand it.

Again, here was the code. "myRow" is the last row in the column:

' data validation for Order #
With .Range("B2", Cells(myRow, 2))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(B:B,B2)>1"
.FormatConditions(1).Interior.ColorIndex = 22 ' watermelon-pink

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
.FormatConditions(2).Interior.ColorIndex = 24
End With


All I changed is,

"=COUNTIF($B:$B,$B2)>1"

and now it works.

I had written:

I run the macro. Things that should be pink aren't. I look in
the conditonal formatting for the cells in question. I see,
e.g., for cell B2:

=COUNTIF(II:II,HI3)>1

Can someone explain that?!

[snip]

Oh, and that "3" instead of "2" was no typo, either. But now it
says 2, since I switched to absolute column references. I can't
explain it!

 

[Dallman Ross]

Dave, can you clarify further about that? I can't find anything
useful via the usual search engines on the web for "eye-eye." In
any case, they cause my formatting formula not to work.

Following up here to see if Dave is reading this and might still
answer this old question.

 

[Don Guillett]

I really can't tell what you are talking about because there is no clue in
the post. Might have been
for i =1 to 21
for ii=3 to 81
could have just as easily been
for dman=3 to 81
or whatever you want to call it.

 

[Dallman Ross]

I really can't tell what you are talking about because there is
no clue in the post. Might have been

Oh, my. Sorry. I assumed others could grab messages from the
References headers as easily as I can. I've only ever used
a Unix Newsreader. Now I'm beginning to understand the penchant
for top-posting around here.

for i =1 to 21
for ii=3 to 81
could have just as easily been
for dman=3 to 81
or whatever you want to call it.

No, but I'll post the entire thing again below.
Basically, my relative-reference named ranges
weren't working right. But not only that,
they were saying "II:II" instead of anything
comprehensible to me. Dave gave me the answer
I quoted, but I couldn't find any trace of
information about such via Google and asked him to
explain. But he didn't come back to the thread.

Here, then, is the post that clarifies it best.
The original thing I asked is way down near the
bottom.

Muchas gracias, and sorry to have perplexed anyone:

=dman=

===============
Path: reader2.panix.com!panix!panix.com!dman
From: Dallman Ross <dman@localhost.>
Newsgroups: microsoft.public.excel.misc
Subject: Re: Inexplicable VBA errors in formulas
Date: Sun, 22 Jul 2007 11:38:52 +0000 (UTC)
Organization: Crossover Fuzzy
Lines: 56
Message-ID: <[email protected]>
References: <[email protected]> <[email protected]>
NNTP-Posting-Host: panix5.panix.com
X-Trace: reader2.panix.com 1185104332 13611 166.84.1.5 (22 Jul 2007 11:38:52 GMT)
X-Complaints-To: (e-mail address removed)
NNTP-Posting-Date: Sun, 22 Jul 2007 11:38:52 +0000 (UTC)
User-Agent: tin/1.6.2-20030910 ("Pabbay") (UNIX) (NetBSD/3.1_RC3 (i386))
Xref: panix microsoft.public.excel.misc:564042

Those aren't roman numerals, they're column ii (eye-eye)
references.

Dave, can you clarify further about that? I can't find anything
useful via the usual search engines on the web for "eye-eye."
In any case, they cause my formatting formula not to work.

Anyway, though, here is a happy update: I solved my problem. I
changed the code simply by making the column references absolute
rather than relative, and the "II:II" stuff went away and "$B:$B"
showed up and my conditonal formats are now working. I can't
explain it, though, and I'd sure like to understand it.

Again, here was the code. "myRow" is the last row in the column:

' data validation for Order #
With .Range("B2", Cells(myRow, 2))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(B:B,B2)>1"
.FormatConditions(1).Interior.ColorIndex = 22 ' watermelon-pink

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
.FormatConditions(2).Interior.ColorIndex = 24
End With


All I changed is,

"=COUNTIF($B:$B,$B2)>1"

and now it works.

I had written:

I run the macro. Things that should be pink aren't. I look in
the conditonal formatting for the cells in question. I see,
e.g., for cell B2:

=COUNTIF(II:II,HI3)>1

Can someone explain that?!

[snip]

Oh, and that "3" instead of "2" was no typo, either. But now it
says 2, since I switched to absolute column references. I can't
explain it!