Formula for a table which does not exist

[fb7894]

Hello. I have a chicken vs. egg problem....

I am creating a template which will be used in a macro. The macro will create a new table within the template.

I want to write a formula in my template which references my table (which hasn't been created yet). Excel will not allow me to do this.

Any suggestions on a way around this? Thank you.

[GTO]

I may well be missing something, but would you not simply have the macro write the formula after it has created the table?

[D_Marcel]

I'm trying to understand your need but I just can't. Can you post a sample of this? If you use something like.. "If table do not exist then create one"?

[Bob Phillips]

Create the table in your template, add the formulae referencing that table, then delete the thing. Your formulae will be returning errors, but should resolve themselves when the table is created.

Another way would be to have the table built, but with just one dummy row of data, then overwrite that table when you get the real data.

[fb7894]

Thanks for the replys everyone. This is hard to explain.

XLD, I tried your suggestion. But when the table gets deleted, my formulas turn into #REF errors. So when the table is recreated, the formulas don't work.

GTO, I could do your suggestion. But I really don't like that approach. If i hardcode a formula in my VBA code, then i change the columns of my template, the formula breaks. Therefore, I prefer to write the formula in the template.

D_Marcel. I'll try to explain in more detail. Let's say I have template that reports the average stock price for the past 10 days for 100 different stocks. My formula will use data from a table to compute the average. The data table will exist on a different tab within the same workbook. My VBA code will query an Access db, write the recordset to the excel sheet, and format the data as a table. So when i create my template, the table does not exist. Therefore, I cannot write the formula. I wouldn't even care if the formula was broken knowing it would work at run time. But excel will not let me create a formula using a table name which does not exist.

[Bob Phillips]

Did you try my second session?

BTW, when you say table, do you just mean a grid, or a structured table?

[fb7894]

XLD, here's the problem with you second suggestion....
My table will add columns over time. Right now, my table has 5 columns but in the future there will be many more. That is why I want to create the table using VBA.

The table is a structure table (I think). It's created manually by using the "format as table" icon. Does this make it a structured table?

[Bob Phillips]

Yes exactly.

You can extend the table with code quite easily. I tested it starting with a small table like so

Name.....Gender.....Amount
Bob............M....................1
Lynne.........F....................2
Amy............F....................3
Hannah......F....................4
Archie.........M...................5

I then used this code to rebuild it with an extra column and an extra row

Dim rng As Range

    Set rng = ActiveSheet.ListObjects(1).DataBodyRange
    Set rng = rng.Resize(rng.Rows.Count + 1, rng.Columns.Count + 1)
    
    rng.Value = [{"Bob","M",1,"Y";"Lynne","F",2,"Y";"Amy","F",3,"Y";"Hannah","F",4,"Y";"Archie","M",5,"Y";"Sita","F",6,"N"}]
    
    ActiveSheet.ListObjects(1).Resize rng.Offset(-1, 0).Resize(rng.Rows.Count + 1) 'include header row
    ActiveSheet.ListObjects(1).HeaderRowRange.Value = Array("Name", "Gender", "Amount", "ID")

and we ended up with a table like so

Name.....Gender.....Amount.....ID
Bob............M....................1......Y
Lynne.........F....................2......Y
Amy............F....................3......Y
Hannah......F....................4......Y
Archie.........M...................5......Y
Sita............F.....................6.....N

Obviously your code would need to be smarter about calculating the size, but it is definitely doable.