Byte array as String representation in VBA

Question

I'm struggling with my byte array. Instead of converting to a string, it's just giving question marks and I think there might be something wrong with the first byte b(0). Any chance someone could have a look at my code for me? Thanks.

Public Sub RunMe()
    Const R As Integer = 6
    Dim e() As Byte
    Dim i As Integer

    e = GetBytes
    For i = 1 To UBound(e) Step 2
        e(i) = Abs(e(i) - (i / 2 + 1) - R)
    Next

    MsgBox e

End Sub
Private Function GetBytes() As Variant
    Dim b(197) As Byte

    b(0) = 77: b(2) = 109: b(4) = 123: b(6) = 124: b(8) = 132: b(10) = 44
    b(12) = 80: b(14) = 118: b(16) = 129: b(18) = 121: b(20) = 132: b(22) = 134
    b(24) = 128: b(26) = 117: b(28) = 136: b(30) = 54: b(32) = 139: b(34) = 135
    b(36) = 57: b(38) = 123: b(40) = 135: b(42) = 136: b(44) = 61: b(46) = 127
    b(48) = 147: b(50) = 64: b(52) = 116: b(54) = 150: b(56) = 132: b(58) = 135
    b(60) = 144: b(62) = 149: b(64) = 157: b(66) = 141: b(68) = 155: b(70) = 144
    b(72) = 151: b(74) = 155: b(76) = 164: b(78) = 92: b(80) = 60: b(82) = 58
    b(84) = 62: b(86) = 60: b(88) = 128: b(90) = 149: b(92) = 174: b(94) = 86
    b(96) = 176: b(98) = 167: b(100) = 174: b(102) = 90: b(104) = 161: b(106) = 165
    b(108) = 171: b(110) = 162: b(112) = 95: b(114) = 168: b(116) = 162: b(118) = 178
    b(120) = 179: b(122) = 173: b(124) = 179: b(126) = 171: b(128) = 186: b(130) = 187
    b(132) = 105: b(134) = 179: b(136) = 185: b(138) = 108: b(140) = 127: b(142) = 126
    b(144) = 128: b(146) = 134: b(148) = 94: b(150) = 92: b(152) = 123: b(154) = 181
    b(156) = 204: b(158) = 183: b(160) = 208: b(162) = 120: b(164) = 191: b(166) = 204
    b(168) = 202: b(170) = 201: b(172) = 125: b(174) = 191: b(176) = 127: b(178) = 203
    b(180) = 198: b(182) = 219: b(184) = 197: b(186) = 211: b(188) = 198: b(190) = 216
    b(192) = 203: b(194) = 137: b(196) = 146

    GetBytes = b
End Function

For i = 1 To UBound(e) Step 2 should be For i = 0 To UBound(e) Step 2 and the first byte should be 84 rather than 77 ;) Nice message though. — Rory
– Rory, Commented Dec 24, 2015 at 8:54
Ah @Rory, I was beginning to wonder if anyone would get it! Just found out about this double byte array for strings, so thought I'd give it a go with a bit of fun. Am in NZ so Xmas days starts soon. Have a good one. — Ambie
– Ambie, Commented Dec 24, 2015 at 9:20
@Rory Totally missed it :-) Added a spoiler to my answer and corrected the first character as Rory suggested. — Automate This
– Automate This, Commented Dec 24, 2015 at 14:20

Automate This · Accepted Answer · 2015-12-24 14:18:01Z

1

You're trying to display a byte but it's actually an array of bytes.

Option 1

Change the type from Byte to Variant and use Join. You must change e and b variables to Variant data type for Join to work. The advantage is that you avoid a loop and is simple to follow.

MsgBox Join(e, "")

Option 2

Loop through array and display each value.

Dim itm As Variant
For Each itm In e
    Debug.Print itm
Next itm

Bonus

You can also output the array to cells.

Dim Destination As Range
Set Destination = Worksheets("Sheet1").Range("A1")
Set Destination = Destination.Resize(1, UBound(e))
Destination.Value = e

Results when outputting the Ascii character (Mouse over to reveal spoiler)

edited Dec 24, 2015 at 14:18

answered Dec 24, 2015 at 5:10

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

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

1 Comment

Ambie Over a year ago

Thanks for full response. Agree with everything you say; only thing I'd add is that I've only just discovered that strings are stored as byte arrays where each character is two bytes large, byte(0) is ascii value and byte(1) is zero. Each byte can be manipulated individually and, what really surprised me, outputting it as a string requires no loop, hence MsgBox e works fine. Thanks again for your answer.

Collectives™ on Stack Overflow

Byte array as String representation in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related