0

I have the following macro code:

    Dim Cenarios As Integer
    Worksheets(Interface.Name).Activate
    Call Ultima_Linha
    Cenarios = UL - 1 'Desconta a linha de cabeçalho

    'Armazena o caminho dos cenários a serem comparados
    Dim Cen_index As Variant
    Dim Cen_caminho As Variant
    Dim Ind As Integer

    'Dimensiona os arrays de dados
    'Todos os arrays estão em base 0
    ReDim Cen_index(0 To Cenarios - 1)
    ReDim Cen_caminho(0 To Cenarios - 1)
    For Ind = 0 To Cenarios - 1
    Cen_index(Ind) = Cells(2 + Ind, 1)
    Cen_caminho(Ind) = Cells(2 + Ind, 2)
    Next Ind

    'Provedor da conexão para consulta
    Dim Provedor As String
    Provedor = "Microsoft.ACE.OLEDB.12.0" 'String de parâmetros de conexão

    'Definição das variáveis
    Dim Caminho As String
    Dim Indi_cen As Integer
    Dim Resultados As Variant
    Dim Headers As Variant
    Dim Dados_Orig As Variant
    Dim Dados As Variant

    'Limites de Arcos

    'Limpa e seleciona a planilha de destino
    Worksheets("Arcos").Cells.Clear
    Worksheets("Arcos").Activate

For the sheet "Interface" I was able to use the Worksheets(sheet.name) reference, but for other sheets, like "Arcos" I could not. I had to use the Worksheets("sheet name") reference.

What can cause this kind of behaviour?

Why I could use only once the reference directly through the sheet's name? Using the later one makes the code prone to errors since the user can alter the name of the sheet and the code will crash.

VBA reports the runtime error 424 object required when I try to use for the second time the worksheets(sheet.name) reference, I mean instead of using worksheets("Arcos").Activate I would like to use Worksheets(Arcos.Name).Activate.

VBA Editor

Samuca
  • 13
  • 6
  • So you have defined a sheet whose `CodeName` is "Interface", that is why `Interface.Name` returns that worksheet's (regular) name. But you have not defined a sheet whose `CodeName` is `sheet` (nor should you, really), so `sheet.Name` is not a thing. You don't need all that in the first place because in this setup `Interface` is already the same object that would be returned from `Worksheets(Interface.Name)`. You should also put `Option Explicit` on top so that you are told at an early stage that `sheet` is not a thing. – GSerg Nov 04 '20 at 12:36
  • Actually, sheet is just an exaple. I have named all the worksheets with names, like Arcos, for instance. I'll edit the main post and add a screen capture of the VBA code, so you can see the structure. – Samuca Nov 04 '20 at 13:19
  • So you do have a sheet with codename Arcos. Then `Worksheets(Arcos.Name).Activate` will work, but it's pointless because it's the same as `Arcos.Activate`. – GSerg Nov 04 '20 at 13:37
  • Do I have to declare Arcos as worksheet? VBA is giving me a hard time. Every variation other than 'worksheets("Arcos").Activate' simply doesn't work. I mean, 'Arcos.Activate', 'Worksheets(Arcos.Name).Activate'. – Samuca Nov 04 '20 at 14:33
  • You can, but you don't have to. Given that you already have the sheet CodeNamed Arcos, `Arcos` is already a sheet variable that is always available. `Arcos.Activate` should then work. If it does not, then either there is a typo, or you are trying to do it from a different workbook. – GSerg Nov 04 '20 at 16:29
  • Well, GSerg, after a few non successful trials, I tried something different. I've changed the sheets names' at the Properties' Box. My guess was that, somehow, VB was confusing the sheet label with sheet name. It seams to work now. Apparentlly, having the same name and label makes a mess in the code. There is no logic, since the Interface sheet has the same name and label. Moreover, default sheet names and labels are the same. – Samuca Nov 06 '20 at 18:39
  • I've also noted that the orinal names weren't recognized by VB. I mean, after I've typed the dot after the name there wasn't the drop down list to select the action. With the new names, after the dot, the drop down list poped up and could select what to do. – Samuca Nov 06 '20 at 18:48
  • VBA never confuses Name and CodeName. If you re-typed the Codename and it worked, that means you originally [had a typo](https://stackoverflow.com/questions/64680104/vba-not-accepting-worksheet-reference-by-its-name?noredirect=1#comment114369619_64680104) in the CodeName, or some characters that were not what they looked like (e.g. [`o`](https://en.wikipedia.org/wiki/O) vs [`о`](https://en.wikipedia.org/wiki/O_(Cyrillic))). – GSerg Nov 06 '20 at 18:59

0 Answers0