I Split a string into an array. The first element is blank/null/empty if the String starts with "BLAK"

Question

I've been coding VBA for 20 years and this has me stumped. I'm testing the code below. If I step through it:

Sub TestSplitting_1()
Dim sColors As String
Dim aColors() As String
Dim sColor As Variant

sColors = "BLAK, FTGR, NAVY, REDD, WHIT"
aColors = Split(sColors, ",")
firstElement = aColors(0)

End Sub

... and hover over the variable firstElement after it's set, it shows no value. Not even empty double quotes, which it should have because it's a string.

If I change the first part of the string to say "apple" instead of "BLAK", so it looks like the code below, and I step through it:

Sub TestSplitting_2()
Dim sColors As String
Dim aColors() As String
Dim sColor As Variant

sColors = "apple, FTGR, NAVY, REDD, WHIT"
aColors = Split(sColors, ",")
firstElement = aColors(0)

End Sub

... and hover over the variable firstElement after it's set, it shows "apple".

What's the difference?? Is "BLAK" suddenly a reserved word, even when it's in a string?

(BTW I know it appears correctly in MsgBox firstElement. But I can't put the value into a cell, which is what I'm trying to do.)

EDIT:

I added a space after "BLAK" and before the comma. Now it works as expected! What the heck? I'll probably use that as a workaround, but I still want to understand why it doesn't work.

But I can't put the value into a cell - what happens instead? — GSerg
– GSerg, Commented Apr 11 at 23:14
Can't reproduce this. Is firstElement declared somewhere, and if Yes then how/where? — Tim Williams
– Tim Williams, Commented Apr 11 at 23:15
"I'll probably use that as a workaround" - I would not do that: if what you describe is true then you need to resolve it rather than work around it. — Tim Williams
– Tim Williams, Commented Apr 11 at 23:36
The missing part is "I can't put the value into a cell" though... — Tim Williams
– Tim Williams, Commented Apr 12 at 0:01

Ron Rosenfeld · Accepted Answer · 2025-04-12 20:00:30Z

2

My guess is that the failure to show the contents when you hover is an issue in the VBA Editor.

Some testing reveals that any element of the array, if it contains BLA, will not show up when you hover. So 1234BLAabcd will also not show up when you hover.

It doesn't seem to matter if the array is formed by the Split function, or if the elements are assigned individually.

However

if you examine the locals window, you will see that does show that firstElement contains BLAK
and if if I add a line such as [A1] = firstElement then A1 will contain BLAK, so I don't know what you mean by I can't put the value into a cell

edited Apr 12 at 20:00

answered Apr 12 at 18:07

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CDP1802 Apr 13 at 11:32

"if it contains BLA, will not show up" . Can you replicate the OP's comment "I added a space after "BLAK" and before the comma. Now it works". Did you use Office 365 ?

Ron Rosenfeld Apr 13 at 13:12

@CDP1802 Yes. Non-space characters do not seem to have that effect, but the space character does restore the expected hovering behavior. I'm guessing some MS programmer was having fun with BLA,BLA,BLA, but I don't know for sure. Also, if a space precedes the BLA, the space will show but not the BLA when hovering.

Collectives™ on Stack Overflow

I Split a string into an array. The first element is blank/null/empty if the String starts with "BLAK"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related