Macro only works for english excel version

Question

I've a quick question for one of my vba codes , the code is basically consolidating 12 tables into one sheet called temp range B8. The problem is that if I run it using Excel 2016 English version it works fine on any computer but if the excel is 2016 spanish version , it will fail with error: " Execution Error 1004"

I've created 12 modules with macros and only one is failing using the spanish version for the following code

Range("B8").Consolidate Sources:=Array( _
        "Enero[#All]", "Febrero[#All]", "Marzo[#All]", "Abril[#All]", "Mayo[#All]", "Junio[#All]", "Julio[#All]", "Agosto[#All]", "Septiembre[#All]", "Octubre[#All]", "Noviembre[#All]", "Diciembre[#All]" _
        ), Function:=xlCount, TopRow:=True, LeftColumn:=False, CreateLinks:=True

I think I need to change the word All with Todo , im going to try that for now and update the qustion if that fix the issue — Samayoa, Aug 14 '20 at 05:25

score 1 · Answer 1 · answered Aug 14 '20 at 05:32

1

Changing the reference type from #All to #Todo was the fix , I-m good now

answered Aug 14 '20 at 05:32

Samayoa

185
1
2
12

score 1 · Answer 2 · answered Aug 14 '20 at 05:50

I'm glad you found a solution to your problem for the Italian version of Excel.

Perhaps, you could make some changes to your code so it could handle both cases (English and Italian). For instance, you could use the following function that returns the MsoLanguageID for Excel (inspired by this answer):

Function ExcelLanguage() As MsoLanguageID 
    ExcelLanguage = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
End Function

You could then use an if-statement to handle both cases like this:

If ExcelLanguage = msoLanguageIDItalian Then
    
    Range("B8").Consolidate Sources:=Array( _
        "Enero[#Todo]", "Febrero[#Todo]", "Marzo[#Todo]", "Abril[#Todo]", "Mayo[#Todo]", "Junio[#Todo]", "Julio[#Todo]", "Agosto[#Todo]", "Septiembre[#Todo]", "Octubre[#Todo]", "Noviembre[#Todo]", "Diciembre[#Todo]" _
        ), Function:=xlCount, TopRow:=True, LeftColumn:=False, CreateLinks:=True

Else

    Range("B8").Consolidate Sources:=Array( _
            "Enero[#All]", "Febrero[#All]", "Marzo[#All]", "Abril[#All]", "Mayo[#All]", "Junio[#All]", "Julio[#All]", "Agosto[#All]", "Septiembre[#All]", "Octubre[#All]", "Noviembre[#All]", "Diciembre[#All]" _
            ), Function:=xlCount, TopRow:=True, LeftColumn:=False, CreateLinks:=True

End If

For all the MsoLanguageID values, see MSDN Documentation.

DecimalTurn , super great answer, Im going to save this post in my favorite pages, This is new knowledge for me. I-m trying to understand if there is a way for excel to automatically do the change when it identify that language is different — Samayoa, Aug 14 '20 at 06:11
Instead of writing the whole Consolidate twice I recommend setting a variable depending on the language and concatenating the Consolidate like so: `"Enero" & stringVaribaleContainingLanguageSpecificWordInBrackets,` And so on — Nacorid, Aug 14 '20 at 07:12
@Nacorid – Yeah, I thought of doing this but if there are only 2 languages I don't think the gain is as important. If there were more than 2 languages, I would definitely do that. — DecimalTurn, Aug 14 '20 at 16:10
There could always be the possibility of more languages being added, depending on company size and all :) You are right though, if it’s certain that it stays with 2 languages there’s no reason to clutter the method with string concatenation — Nacorid, Aug 14 '20 at 16:13

Macro only works for english excel version

2 Answers2