One of these things is not like the other…

Sometimes when I copy code from the web and paste it into VBA, I get something like this:
 
Error
 

…and I’m damned if I know why. It’s not the usual culprit of incorrect quote marks. It’ something else, and I don’t know what.

Usually I just retype the offending line:
 
No error
 
…and after looking in vain for a difference, simply delete the bad one, and put it down to inexperience.

But not today. Because I’m tired of being compiled around. So today, I’m gonna find out why. And I’m going to use Excel to keep VBE honest. Conquer and #DIV/0, I say.

If I paste the two formulas into separate cells in Excel, then I can clearly see that something is amiss. I just can’t clearly see the actual something:
 
Excel 1
 

Okay, let’s get nasty, and atomise these suckers so I can compare their DNA:

=MID(A,ROW(A1:INDEX(A:A,LEN(A))),1)
=MID(B,ROW(B1:INDEX(B:B,LEN(A))),1)

Array
 

Well take a look at that…there’s the culprit:
 
False
 
No wonder I couldn’t see it:
 
Different

In this particular case, the culprit looks like Ken Pul’s Blog platform…I stole the code from a comment Jan Karel left there, and note that the code has no similar issue if I lift it directly from Jan Karel’s site.

There you go. Busted.

Makes me feel like singing:

Singing

5 Comments

  1. Ken Puls says:

    Interesting. I wonder if that’s because of some WordPress upgrade issues I had at one point. I can see that page still shows some odd characters, and it may be that I tried to do a find/replace on some and got the wrong character.

  2. Chris Macro says:

    This reminds me of a time when I had spent hours trying to figure out what was wrong with some text I had copied and the culprit ended up being those damn Smart Quotes!

  3. Tristan says:

    OK, so where’s the nifty tool to auto correct? Knowledge is power, but power users need advanced features. Or something.

  4. Ah, now I know why that code looked so familiar :-)

  5. Jeff Weir says:

    Tristan…funny you should say that. I’m working on a VBA tools addin for VBA newbies that among other things will clean up stuff like this. It will be ready just before VBA is retired, mind.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: