CODEWITHSUNDEEP
vbaexcelmacrosnon-ascii-characters
Lobos
Lobos

Reputation: 21

Excel VBA - my VBA Code got messed up: why the spanish characters (á,é,í,ó,ú,ñ,¡,¿) mutated?

This is my fisrt question here, so please, bear with me...

As I tried to explain on the title, my VBA code in an Excel workbook got all messed up. The workbook was shared to a lot of people so they could type in some data (it's more complex, but let's just leave it at that), and some of the files that I received are not working: I get the 400 error, no code seems to work.

Say I use named ranges to identify where the sensitive data is allocated, but most of the macros now won't work because the names with any special spanish letters (á, é, í, ó, ú, ñ) in the code have switched. Do you get it? No? Ugh, well, I'm doing my best to explain the problem... let's try with an image.

See how even the name of the Macro is just wrong?! It is just not cool :(

When I try to open my UserForm, I get the 400 error. When it loads, it looks for some values located in Tables on a worksheet, but the names of the table's columns in the code do not match with the actual ones on the worksheet: This is one of the many Excel Tables with column names that use the special characters. In this case, the name of the column in the code is "Observaci-n"... they just won't match.

I would like to know WHY this happened and what can I do to fix and to prevent this from happening again (besides not using the eñe (ñ) and all the rest of the special characters).

I thank you all in advance for helping me with my frustration.

¡¡Aguante, Stack!!

Upvotes: 2

Views: 716

Answers (2)

Vityata
Vityata

Reputation: 43593

If you wanted to use this characters in your code as strings e.g. like 

dim s_my_string as string
s_my_string = "mañana"

a better way to use it is to refer it to a cell like this:

dim s_my_string as string
s_my_string = Range("A1").Text

In cell "A1" you may write "mañana" or anything special. Generally this is considered a good practice in VBA Application Development.

Edit: Saw your case, you should rewrite the names to standard latin, if you are the person to maintain and work with it.

Upvotes: 0

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

Best solution:

NEVER use non US-ASCII characters in names of subroutines or variables! Programming languages have never been made to be locale-sensitive.

You can use these non US-ASCII characters in names of headers etc. in your sheets but if you encode these names in your code too, then they might be changed. You should be able to manipulate them as strings though.

The US-ASCII characters are 0..127. All higher characters require code pages to translate them.

Upvotes: 1

Related Questions