Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Building a line in VBA

Building a line in VBA

Building a line in VBA

hello to all

I think this should be easy, but I'm missing something.

I have a function that has an Argument (nColumns AS Long).

In the function are two lines that depend on the value of nColumns.
Let's say nColumns = 5. So, I'd like the two lines to look like this...


FldStr = Array("Row",   "C1", "C2", "C3", "C4", "C5")
FldWidth = Array(1000,   500, 500, 500, 500, 500) 

How can I BUILD these two lines in VBA???

Thanks in advance

RE: Building a line in VBA


A line segment would have two x,y coordinates.

Maybe I'm missing something. Could you please you explain your question?


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Building a line in VBA

Skip, the OP wants to dynamically build the lines of code within the function (although it isn't entirely clear why, since if we can build the lines we can populate the arrays directly)

RE: Building a line in VBA

My how myopic of moi!

I remember a teacher in my assembler code class, telling of an employee of Tandy Corp, I believe, who wrote code modifying programs, how difficult it was to debug. Well not quite the same here.

teach314, please clarify. Declare arrays, Redim and load from tables for instance.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Building a line in VBA

Many years ago (about 30!!!) I wrote some self-modifying machine code as part of a floppy disk protection scheme. I think that's about the only time I did it in a production environment.

RE: Building a line in VBA

hi strongm and SkipVought

My code produces hundreds of Temp tables as part of a research project. I like to format the column widths for clarity when viewing these tables. Because I'm the only one using these tables, I don't want to bother preparing Reports. It works perfectly on all of the tables except those where the number of columns can vary.

Hope this helps

RE: Building a line in VBA

hi again

I could solve the problem by using a Select Case for each possible number of columns. But, in the case I'm interested in, there are 48 possible values of nColumns. I was hoping to just dynamically build the code as strongm mentioned.


RE: Building a line in VBA

I don't know the code needed to set the field width in a table. Its not the same as a datasheet form, me.whatever.columnwidth = 500.

Either way, if you have a table, and know the code to set the field widths, then maybe something like this?

set r = currentdb.openrecordset("MyTable")

for I = 1 to R.Fields.Count
R.Fields(I).ColumnWidth = 500

I know the fields.columnwith won't work with a table, but as I mentioned, not sure what code you use to set widths in a table.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.


RE: Building a line in VBA

thanks for responding, Blorf

I'll try to incorporate your ideas!

What I have been doing (it works perfectly) is...


Dim fldStr() As Variant,  fldArray() As Variant

... stuff here ...

    'set Table COLUMN WIDTHS
    FldStr = Array("SchCol", "SchRow", "v", "ABSv", "PARv")
    FldWidth = Array(800, 800, 1400, 600, 600)
    For FldIndex = 0 To UBound(FldStr)
        Set fld = tdf.Fields(FldStr(FldIndex))
        Set prp = fld.CreateProperty("ColumnWidth", DAO.DataTypeEnum.dbInteger, 3)
        fld.Properties.Append prp
        fld.Properties("ColumnWidth") = FldWidth(FldIndex)

My problem was how to modify the FldStr and FldArray lines. I'll give your ideas a spin.


RE: Building a line in VBA

Instead of using Array function for variant FldStr and FldWidth, you can use dynamic arrays. With ReDim you can set their required size, it's easier to fill them, they can also be input arguments for functions. If it could be helpful, dynamic arrays can be components of user defined type and used as "As" declaration in function definition, in this case a whole structure can be returned by function.


RE: Building a line in VBA

>how to modify the FldStr and FldArray lines

You've told us how you know how many columns to deal with (nColumns), but how do you then know which specific headers and which specific column widths you need to use? There must be a source for this info to allow you to dynamically build the lines (and which I - and clearly others - do not think is necessarily the best approach)

RE: Building a line in VBA

good question strongm. The columns headers are ALWAYS Row (1000), then C1 (500), C2 (500), C3 (500), etc, as in the original posting but not in my later code example.

So, if nColumns = 20, then there will be Row (width 1000), then C1 to C20, all having width 500.


RE: Building a line in VBA

In which case you could use something like:


    Dim fldStr() As String, fldArray() As Long
    Dim lp As Long
    ReDim fldStr(nColumns) As String
    ReDim fldArray(nColumns) As Long
    fldStr(0) = "Rows"
    fldArray(0) = 1000
    For lp = 1 To nColumns
        fldStr(lp) = "C" & lp
        fldArray(lp) = 500

(although in reality I'd probably not bother with the arrays at all, frankly)

RE: Building a line in VBA

thanks strongm. Very helpful.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close