Use a variable in Objectname for WITH statement

Question

I have a excel macro which reads content from a specific column in another sheet. I want to use a variable in the sheet name in a WITH statement but keep getting an error message "Error during runtime, Object required" in the For Each line

I already searched how to use variables in object names and tried that, but to no avail.

This code works

With Blad2
strData = Range(id & "1") & vbLf & vbLf
    For Each c In .Range(id & "2:" & id & "10")
        If c.Value <> "" Then
            strData = strData & " - " & c.Value & vbLf
        End If
    Next c
End With

This code fails. I know for sure variable bld has a numeric value, tested that with MsgBox

With ("Blad" & bld)
strData = Range(id & "1") & vbLf & vbLf
    For Each c In .Range(id & "2:" & id & "10")
        If c.Value <> "" Then
            strData = strData & " - " & c.Value & vbLf
        End If
    Next c
End With

Any clues how I can get this to work?

Hi @vincent G, that's clear so how can I use the variable bld in the objectname 'Blad2' (sheet 2 of the excel) — Jeroen Swets, Feb 04 '19 at 09:48

Vityata · Accepted Answer · 2019-02-04T10:08:36.057

3

A worksheet could be referred to in 4 ways:

with its index;
with its Name on the tab;
with its CodeName, which is by default the same as the name of the tab, unless the latter is changed;
with a variable;

Some time ago I have written this answer here - https://stackoverflow.com/a/52721327/5448626

When you write With Blad2, then Blad2 is the codename of the worksheet. E.g., the one here:

Thus, it is already a variable and Excel recognizes it. On the other hand, With ("Blad" & bld) is a string and not a worksheet variable.

To make it work, use With Worksheets("Blad" & bld), using the worksheet name of the worksheet.

edited Feb 04 '19 at 10:08

answered Feb 04 '19 at 09:48

Vityata

42,633
8
55
100

Now I get an error on the line With Worksheets("Blad" & bld), "the subscript is out of range" – Jeroen Swets Feb 04 '19 at 09:52
You can't use variables for codename like this. You can use for the name of of the worksheet though :) – Siddharth Rout Feb 04 '19 at 09:53
@SiddharthRout - I assume the name of the worksheet is exactly the same as the codename, as `Blad2` is the default for Dutch Excel. – Vityata Feb 04 '19 at 09:54
@JeroenSwets - what is the value of `bld`? – Vityata Feb 04 '19 at 09:54
The name of the worksheet as per your screenshot is `Tabelle1` :) – Siddharth Rout Feb 04 '19 at 09:55
The value of bld = 2 and indeed its Dutch Excel so worksheet 2 name = Blad2 – Jeroen Swets Feb 04 '19 at 09:59
@SiddharthRout - I am lazy :D – Vityata Feb 04 '19 at 10:00
Solved. I misunderstood. I had renamed the sheet. Now it works. – Jeroen Swets Feb 04 '19 at 10:07

Siddharth Rout · Answer 2 · 2019-02-04T10:01:23.493

2

What you are trying is to use Variable Variable for SheetCode Name. You cannot do it like that. To achieve what you want, try this...

Dim bld As Long: bld = 2
Dim shtCode As String

shtCode = "Blad" & bld

With Sheets(ThisWorkbook.VBProject.VBComponents(shtCode).Properties("Name").Value)
    Debug.Print .Name
End With

edited Feb 04 '19 at 10:01

answered Feb 04 '19 at 09:53

Siddharth Rout

147,039
17
206
250

1

Yes it works :) File |Options | Trust Center | Trust Center Settings | Macro Settings | Check the checkbox "Trust access to the VBA project object model" – Siddharth Rout Feb 04 '19 at 10:36
1

I do not trust it :D – Vityata Feb 04 '19 at 10:37

Use a variable in Objectname for WITH statement

2 Answers2