XL2BB - Excel Range to BBCode 2.1

[smozgur]

Hi @smozgur

Thankyou for your reply. Here is a link to the workbook in OneDrive.

The Data Table is created by Data | What-if Analysis | Data Table.

Ok, thank you. I saw the workbook, and the function. I can't use TABLE() function as it is not recognized as an Excel function, however the function and the array structure should mean something special internally to Excel when it is used with What-If Analysis.

However, the cells have formula, and those are array formulas, so .HasArray() returns True. This is a very rare situation that XL2BB currently has nothing to do. It is looking at the content to make sure if it contains a formula, and then looking if it is an array, dynamic, or a basic formula, then it acts accordingly. There is no unrecognized function definition in this equation - unless you could tell me how I can detect "this" particular situation in VBA.

 

[DRSteele]

Ok, thank you. I saw the workbook, and the function. I can't use TABLE() function as it is not recognized as an Excel function, however the function and the array structure should mean something special internally to Excel when it is used with What-If Analysis.

However, the cells have formula, and those are array formulas, so .HasArray() returns True. This is a very rare situation that XL2BB currently has nothing to do. It is looking at the content to make sure if it contains a formula, and then looking if it is an array, dynamic, or a basic formula, then it acts accordingly. There is no unrecognized function definition in this equation - unless you could tell me how I can detect "this" particular situation in VBA.

Data Tables are created by that set of clicks, and when they are created the function TABLE gets put into all the cells, which can then not be edited except to delete them all. Mike Girvin ate ExcelIsFun has some good videos. Here is one to serve as a primer.

I don't know a thing about VBA expect what it stands for.

 

[smozgur]

Data Tables are created by that set of clicks, and when they are created the function TABLE gets put into all the cells, which can then not be edited except to delete them all.

Oh yes, I already tested to see how it works in the workbook you shared. That's what I can find out after analyzing the resulting array formula in the Data Table range. It gives me nothing more than an array formula, but I can't even create the same array formula manually.

As I mentioned, it should be something internal that Excel understands but unfortunately is not being recognized by VBA (even applicable manually in cell context). It should be something like Goal Seek that Excel internally understands what it means but not for us users.

 

[DRSteele]

Ok. I guess that anytime I post a sample that has a Data Table I will include instructions that it is not a formula that users can enter; rather it's a formula that gets created by Excel's Data Table operation only.

 

[smozgur]

Ok. I guess that anytime I post a sample that has a Data Table I will include instructions that it is not a formula that users can enter; rather it's a formula that gets created by Excel's Data Table operation only.

Could you please tell me the link that you posted this mini-sheet?

 

[DRSteele]

Sure can. Loan Amortisation Schedule

 

[smozgur]

Sure can. Loan Amortisation Schedule

I see it is an Excel Article. I can manually edit it to hide the formula in that range, and make it like this:

However, then visitors will copy and paste the mini-sheet with totally not recognized TABLE function (actually it will happen anyway).

Does this help for this article?

 

[smozgur]

Or perhaps something like this:

 

[DRSteele]

Well, my question here today was academic. I frequently use Data Tables and became concerned that they would cause confusion in the minisheet in XL2BB.

That is a good idea, editing the minisheet. Thanks.

 

[smozgur]

Well, my question here today was academic. I frequently use Data Tables and became concerned that they would cause confusion in the minisheet in XL2BB.

Yes, that's why I tried to come up with a solid solution. This way we can keep XL2BB up-to-date like we applied dynamic ranges. But this one is a bit confusing since I can't access it in VBA.

The only solution that I can think could be providing a "selectable formulas in the meta table", removing formulas that user doesn't want to include, but I can say that it is only a plan for XL2BB 2.0 version. Not sure, but perhaps in the birthday of XL2BB (11/16).