individual subs from within VBA

[zSplash]

Using Word, I get values from an external file, assign these values to
variables (5) and want to make subs based on these variables. (I need to
re-get the values an indetermine number of times -- based on the external
file, which changes daily.)

My question is how to write individual subs (indeterminate number of subs)
based on these variables. I'd use a "for-next" loop (or array??) and --
redim? I've never used redim, and don't really get it, if that's the case?
My problem is that I just don't know how to make a sub from within VBA, I
think.

I hope I've made my request clear. Can anyone help me?

TIA

 

[Jezebel]

What do you mean 'sub' ? In VBA it usually refers to declared code routines
(Subs and Functions) but this makes no obvious sense in relation to the rest
of your post.

 

[Peter Hewett]

Hi zSplash

It's as clear as mud! Instead of getting wrapped up in a process that may or may not work
describe for us what it is you are trying to achieve. I've no idea what data is in your
file, how much of it there is or what it's type is. Also what is it that you want to do
with it?

Cheers - Peter

Using Word, I get values from an external file, assign these values to
variables (5) and want to make subs based on these variables. (I need to
re-get the values an indetermine number of times -- based on the external
file, which changes daily.)

My question is how to write individual subs (indeterminate number of subs)
based on these variables. I'd use a "for-next" loop (or array??) and --
redim? I've never used redim, and don't really get it, if that's the case?
My problem is that I just don't know how to make a sub from within VBA, I
think.

I hope I've made my request clear. Can anyone help me?

TIA

HTH + Cheers - Peter

 

[Jonathan West]

Using Word, I get values from an external file, assign these values to
variables (5) and want to make subs based on these variables. (I need to
re-get the values an indetermine number of times -- based on the external
file, which changes daily.)

My question is how to write individual subs (indeterminate number of subs)
based on these variables. I'd use a "for-next" loop (or array??) and --
redim? I've never used redim, and don't really get it, if that's the case?
My problem is that I just don't know how to make a sub from within VBA, I
think.

I hope I've made my request clear. Can anyone help me?

No, because you haven't made the request clear.

Forget about VBA for the moment, and describe in plain English what you are
trying to do. i.e. what information you start with and how you want it to be
when you have finished.

Once there is a clear description as to what you want to do, then we may be
able to make suggestions as to how you should do it.

 

[zSplash]

Man, guys, forgive me for being so obtuse. Wow!

So, what I am trying to do is get data (a record consisting of 5 fields)
from an external file (which external file which might have 50-150 records).
With the first "record" (5 pieces of data), I want to write one "sub" (call
it "the1") that can be later invoked from Word. After writing "the1", I go
back to the external file to get the next record (5 distinct pieces of
data). Now, I want to write another "sub" (call it "the2") using that
second record, and so forth.

The end result is that I want to be able to run "the1" (through "the50")
macros from Word.

Is that any clearer?

TIA

 

[Charles Kenyon]

Is that any clearer?Not really. What do these macros do? Have you looked into mailmerge?

 

[Jonathan West]

Man, guys, forgive me for being so obtuse. Wow!

So, what I am trying to do is get data (a record consisting of 5 fields)
from an external file (which external file which might have 50-150 records).
With the first "record" (5 pieces of data), I want to write one "sub" (call
it "the1") that can be later invoked from Word. After writing "the1", I go
back to the external file to get the next record (5 distinct pieces of
data). Now, I want to write another "sub" (call it "the2") using that
second record, and so forth.

The end result is that I want to be able to run "the1" (through "the50")
macros from Word.

Is that any clearer?

Let me see if I understand. You are wanting to write a macro to generate 50
macros in Word, named "the1" to "the50", whose code is based on the content
of data retrieved from 50 separate records in an external file, and you want
to be able to run those macros subsequently and have them work.

Well, yes, you can do this, though I would strongly recemmend you think of
why you want to to and find an alternative way of doing whatever these
macros are supposed to do.

I've never done this, but if you go to Tools References in the VBA editor,
and set a reference to "Microsoft Visual Basic for Applications
Extensibility", you gain access to the VBE object and various objects and
methods based on it. With this, you can add new routines to existing
projects.

 

[zSplash]

Or, to put it more succinctly: I have 50 macros ("the1" to "the50"). They
are coded and work well. (They're identical, by the way, each with 5
variables.) Let's say the code for "the1" (and for "the2", "the2" ...,
"the50") is as follows:
sub the1
dim first as string, last as string, phone as string
dim dob as string, state as string
selection.typetext text:= first & " " & last
selection.typeparagraph
selection.typetext text:=phone
selection.typeparagraph
selection.typetext text:=dob
selection.typeparagraph
selection.typetext text:=state
end sub

What I want to do is get the values for the variables from an external file,
and insert those values into "the1" through "the50" so that when I run
"the1", I get the values for the five variables in Row1; when I run "the2",
I get the values from Row2, and so forth. The external file changes daily,
but I always want "the1" to come from Row1 of that external file, etc.

What I want, after I exit the code that "sets" the 50 subs ("the1" through
"the50") to have the values from the external file, is to be able to run
"the1" through "the50" macros/subs and have the values in the external file
at the time I "got" or "set" subs "the1" through "the50".

TIA

 

[Jezebel]

Now it's coming clear! Talk about reinventing the wheel ... you've invented
a stunningly difficult way to do something that is really easy using
existing techniques.

Fifty subs, identical apart from the variables! Why not use one sub that
takes five arguments?

But in any case, if all you're doing is creating 50 paragraphs, each with
name, DOB, etc drawn from your source file, why not use mailmerge? That's
what it's for; and you wouldn't need any code at all.

 

[zSplash]

Thanks for the suggestion. I will look into mail merge, but don't think
it'll serve my purposes.

you've invented a stunningly difficult way to do something that is really

easy using existing techniques.
Well, I'm glad you thought it was stunning, although it was dumb.

Fifty subs, identical apart from the variables! Why not use one sub that

takes five arguments?
That's what I was first wondering about, but nobody understood my
explanation. (I wanted to use an array). So, to get across my request, I
made it 50 subs.

...why not use mailmerge?

I don't think I can use mail merge because I don't want these paragraphs
to be added to a new document, but to be added to the current document.
(That is, I am using a single document, from which I invoke "the1", and want
"the1" to insert formatted data into the current document.)

 

[Jezebel]

If the task is as you describe it, mail merge will do exactly what you need.
What do you see as the problem?

 

[zSplash]

When I tried mail merge, I always had to "select" a new document to insert
the data. I want to be able to insert the paragraphs into the same
document, whenever I need them (that is, not in any uniform way, but only
when I "call" the particular paragraph).

st.

 

[Jezebel]

It would have helped if you'd explained all this at the outset. The problem
is, you pre-supposed a particular solution to your task, then sought help
with that particular solution. No offense, but your intended approach is
truly awful. It is feasible, just, but prodigiously difficult.

So the task is:

1. You have a file, changing daily, with the details of 50 people.
2. While editing, you need to be able to insert paragraphs, formatted in a
specific way, containing details of any of those 50 people.

There are many ways to approach this. The choice depends on how you like to
work, how many of these paragraphs you need to insert, and so on. I would
probably use a UserForm and a collection of class objects, each object
representing one row from your source file (ie one person). The first time
the form is loaded, in any Word session, it reads the source file to
populate the collection. When displayed, the form shows a list box of all
collection members: double-click to insert the paragraph for that member of
the collection. Create a single macro, associated with a hotkey, that
displays your UserForm.

 

[zSplash]

Sounds like an idea. Now, if only I can figure out how to do what you so
easily suggest. I thought my first question ("how to make a sub from within
VBA") was a good start. Now, essentially you suggest that, using a
collection of class objects. I will look into class objects.

I really appreciate your help, Jezebel, but you must realize not everyone
can know how to state their problem, and get to the solution, as well as you
must be able to. It's obvious I had no idea how to state my problem, so how
could I have "explained all this at the outset." How much is too much???
How little is too little??

Some of us flounder, try to figure out a possible solution, and when we
can't see the solution, ask for help from you experts. Then, when no one
understood me, I tried to refine my question, as well as a possible strategy
towards solution. I was looking for help; if I had known I should not look
for a solution, but merely pose the problem and let you experts solve it, I
would have done that. I did not think that was what these help forums were
all about. Please be gracious enough to understand a beginner, and someone
obviously not as advanced as you.

st.

 

[Jezebel]

Really, I meant no offense, and you have taken the posts in the right
spirit. The basic point is nothing to do with software at all, but about
stating problems in terms of what you want to end up with, not how you think
might be the best way to get there.

Otherwise, as you've seen, you get the software equivalent of "well, I
wouldn't start from here".

 

[Doug Robbins - Word MVP]

I've been following this thread, but I have to admit I am still not exactly
sure what you need to do. If it is to get all 50 records into the document
each day, or whenever you need to, it does however seem like starting with a
Directory or Catalog type mailmerge might be the easiest thing to do.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[zSplash]

Thanks, Jezebel and Doug, for your help. I will try to solve the problem
using each of your suggestions, and, as necessary, re-post an entirely new
thread, hopefully one that is clearer and poses my question/dilemna better.
I do appreciate all your assistance.

st.