PDA

View Full Version : Formula for a table which does not exist



fb7894
01-16-2014, 12:30 PM
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
01-16-2014, 01:12 PM
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
01-16-2014, 01:29 PM
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
01-16-2014, 03:20 PM
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
01-16-2014, 04:14 PM
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
01-16-2014, 05:28 PM
Did you try my second session?

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

fb7894
01-16-2014, 07:52 PM
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
01-17-2014, 05:19 AM
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.