Building a line in VBA 1

[teach314]

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
Teach314

 

[SkipVought]

Hi,

A line segment would have two x,y coordinates.

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

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)

 

[SkipVought]

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.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

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.

 

[teach314]

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
Teach314

 

[teach314]

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.

Teach314

 

[Blorf]

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​

next

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.

http://www.thelegacyreturns.com/

 

[teach314]

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)
    Next

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

Thanks
Teach314

 

[combo]

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.

combo

 

[strongm]

>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)

 

[teach314]

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.

Teach314

 

[strongm]

In which case you could use something like:

[blue]    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
    Next[/blue]

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

 

[teach314]

thanks strongm. Very helpful.