CODEWITHSUNDEEP
excelvbaencoding
A-Sus
A-Sus

Reputation: 3

Getting question marks when reading range from Excel in VBA (Encoding problem)

I'm trying to read a range from an Excel spreadsheet and store it in a bi-dimensional array with VBA.

Dim myRange As Range
Dim myArray() As Variant

myArray = myRange.Value2

Unfortunately a few cells contain characters that are not recognised by VBA, like a greek capital beta (Unicode 914). These characters are stored in my array as question marks (ASCII 63).

I'd like to perform some calculations on the array based on its values, and to write the modified array in another range, keeping the original letters.

I wonder if there is a simple way to import those characters, not having to loop over the single cells and encode the strings one by one (mostly because my range is large and I am concerned about the time this approach might take).

Upvotes: 0

Views: 1971

Answers (2)

TourEiffel
TourEiffel

Reputation: 4424

I think you got to do those steps to use greek character in VBA :

  1. To use the Greek language in VBA: Open Control Panel
  2. Click Region
  3. Click the Administrative tab
  4. Click the Change system locale button
  5. Select which language to use when displaying text in programs, such as VBE, that do not support Unicode. The setting will affect all user accounts on your computer.

Sources and credit here, this is the thing you need to correct your issue.

enter image description here

EDIT

ChrW(914)

Will also return ?

enter image description here

If you didn't follow previous steps..

Upvotes: 1

Super Symmetry
Super Symmetry

Reputation: 2875

VBA actually recognises these characters. However, it is the VBA editor that doesn't know how to display them and replaces them with question marks. Your myArray variable actually has the right characters. To test for a particular unicode character use AscW or ChrW VBA functions (W for wide I guess). For example (assuming each cell in myRange contains only 1 character you can use:

' Test by the unicode character
If myArray(1, 1) = ChrW(914) Then

or

' Test by the character unicode number
If AscW(myArray(1, 1)) = 914 Then

Furthermore, in excel formulae you can use UNICODE() and UNICHAR() worksheet functions to achieve similar results.

Upvotes: 0

Related Questions