#NAME? error when passing text in VBA script.

Hello, thanks to this forum I've gotten started in my first VBA script.

My long line that returns a #NAME? error is one that should return a date in a cell formatted as a date. Everything works except when I want to pass a SPACE character for failing a logical test. My original Excel calculation works fine when I pass " " but in the VBA it fails with a generic error. OK, it works when I put in an arbitrary number but that's not useful. I quickly changed it to ' ' since the entire calc is enclosed in double quotes which should have worked.

I've opted to putting in Chr(32) and it didn't error out except for the #NAME error. It seems to pass this value on failing tests.

I've been Googling for context and answers and it's not clear to me which way I should go. Does this make sense to you why it's happening? thanx, sam

It would probably help a lot if you shared the code and the formula that you hope to get out of it. It's probably as simple as doubling up the quotes.

Regards, TMS

OK, tell me what the formula should look like when it gets there safely ... and what cell you are trying to put it in.


Regards, TMS

Never mind ...

Please Login or Register  to view this content.

Regards, TMS

And it appears you're using the CHAR calculation instead of the CHR I had found.

CHAR in an Excel worksheet formula, Chr in VBA. For example, =CHAR(65) = "A" (no quotes) in Excel. ?chr(65) = "A" (no quotes) in VBA Immediate Window.

You use an underscore (_) as a continuation character. This allows you to split long lines of code across several lines to make it more readable. But you may then need ampersand (&) to concatenate the string segments.

So, if you had:

Please Login or Register  to view this content.

you could split that as

Please Login or Register  to view this content.

on the same line. And then, you could say:

Please Login or Register  to view this content.

It helps to be able to see how the various parts of the code or formulae line up.


The other options were just variations on a theme. If cell A1 is selected, using ActiveCell is the same as saying Range("A1"). The difference is that, with ActiveCell, you have no idea which cell is selected ... unless it is selected somewhere nearby.

It's better to control which cells you are accessing specifically rather than relying on the ActiveCell being where you think it should be. And, if you don't select cells, the ActiveCell won't change.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.