0

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).

Improve this question

2 Answers 2

Reset to default
1

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..

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

3 Comments

Thanks for sharing this. Good to know this option exists.
Thank you, Dorian. Greek letters are not the only ones I have that the editor cannot display, so this only partially solves my issue.
@Dorian With ref. to your EDIT, ChrW(914) actually returns the right unicode 914 character. However, it diplays it in the immediate window as a question mark. Try Range("A1").Value = ChrW(914) and you should get the right character in cell A1.
0

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.

Improve this answer

4 Comments

Thank you Super Symmetry, as you said the editor was the culprit. I see question marks in the Locals Window and in Message Boxes, but when I write the letter in a cell, I get back my beta.
I think this does not work if you didn't set the language to use.. Does this work for you typing : ? ChrW(914) in immediate window ?
@A-Sus to display it in message box you gotta follow the step provided on my answer ;)
@Dorian you're absolutely right I always think of a message box as part of the vba editor

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.