vba ppt 2003 - table value lookup

[Tom Conrad]

I'm using ppt 2003 and I am a vba advanced beginner.
I need a powerpoint vba equivalent to Excel's vlookup or hlookup functions.

I need code to search a table or an embedded spreadsheet to find the data
found in a cell at a row/col intersection.

The user uses spin/scroll/option buttons to enter values that I use to
determine the correct row and col numbers.

I need to place the data table on the slide vice within the code. I looked
at establishing an array variable but I cannot create an array variable in
the vba code for proprietary reasons. If the data appears as a
table/spreadsheet within the presentation there is less likely hood that the
proprietary data will hide as meta data within the presentation.

I thought about using a group of label objects to create the table but this
seems painful.

Any help would be appreciated.

Happy New Year to all MVPs and user group correspondents.

 

[Tom Conrad]

Steve,

Thanks for the reply.
I believe that the best format for the table would be a ppt table. Although,
I could use whatever format uses the simplest code.
The data is positive/negative numeric of 1-3 digits in length.

My intent is to take the user entered value and then locate the cell.
The cell value will be used to rotate/draw a line.

 

[Brian Reilly, MVP]

Tom,
Thanks for the New Year greeting.
Nothing changes this year, Rindsberg and I continue to disagree on
approaches.(g)
I'd probably use an embedded XL workbook probably with two worksheets.
I'd probably make one xlVeryHidden to store the Propriety stuff. I'm
not really sure what you are trying to do but I avoid PPT Tables like
the plague. You could probably do this in PPT tables but Steve will
have to help you. I can't.

Brian Reilly, MVP

 

[Tom Conrad]

Not that I want to create a flame war, between you and Steve, but in your
opinion, why is your approach better? I ask because I have the flexibility
to use spreadsheet or ppt format. I am also asking because I expect to learn
something new.

I'm pretty certain that Word format would be way too painful.

 

[Brian Reilly, MVP]

Tom,
Steve and I just like to disagree with each other (rofl). Watch, he'll
disagree with that.

My preferences for using Excel vs. PPT tables comes from two sources:
1. I was already doing this in XL before PPT tables were almost worth
talking about, so there is a bit of the effect of inertia here.
2. PPT tables don't have all the functionality of Excel tables and
there are a lot fewer people who code in PPT tables so Google searches
turns up far fewer examples to modify.

Actually, Word tables sometimes are preferred if using decimal
alignment, but we have existing code for XL that does this too.

More on #2. PPT tables cannot do any calculations or conditional
formatting and XL can. It also has formulas and range names etc. which
make life easier to manage, leaving more time to harass Steve (g).

As an example of this, I am currently doing a charting project in PPT
for a client that programmatically creates literally thousands of
charts from Excel data. If I went with PPT tables, I'd probably get
messed up when the client changed the column order of the reports but
I dynamically create range names at workbook.open and as long as they
don't change the label of a column, I don't care what column it is in.
And they have these wierd charts, originally done in MS Grump that
have textboxes with text and a number variable in them placed right on
the bar. I would have quit that job if I had to create all these guys
in Grump every month. In Excel, I can concatenate text and a number,
e.g. N=42 and link it by formula to a range name and I haven't had to
write any code yet.

Get the point? (g)

Brian Reilly, MVP

Brian Reilly, MVP

 

[Tom Conrad]

I'm a little behind in my postings (amazing how a honey-do-list can derail a
project). Anyway, I appreciate your response, your time and your humor.

Steve is correct I just need to store the values in a table, and retrieve
them for calculations. As I said in the original post, I am restricted from
putting the values in the coding. If I delete the table I remove the
proprietary data, or I can subsitute dummy values into the table, to retain
the codes functionality.

I will fumble around with your suggestions and if necessary get back to y'all.

Thanks again,
Tom

 

[Brian Reilly, MVP]

Steve,
So if you know that song, just sail up the Hudson River from Manhattan
and you will get the repetitous bard of that song on the East side of
the Hudson. A wee bit above Yonkers.

For the non-naviational folks and non-NY'ers, that would be Sing Sing,
one of the serious Prisons in the NY State Correctional system.

I know Steve knows where it is. His cats are probably all from there
(Purrrrrfect, Purrrrfect).

Brian Reilly, MVP