There is a task - to write a macro for MS Excel. Macros need to specify a folder, and it must find all Excel files in this folder and perform operations with them:

  • Open book
  • Copy certain cells
  • Paste their contents into another book.
  • Close the book

In general, to collect from all files one table. Unlike the usual Python, in the BWA is not clear everything. I read inquiries at the Microsoft office, but for some reason they give false descriptions.

  1. How to create an empty array so that any number of elements fit into it? I tried Dim ExcelFiles() As Variant , but nothing can be added to this array. If you specify the length, it works, but I would like to know how to create an empty array.
  2. How to find out the number of elements (length) of an array? The answers found only vague. Analog Array.length in Java Script, for example.
  3. How do cycles work? There is such a fragment:

For j = 0 To UBound(ExcelFiles) Workbooks.Open (ExcelFiles(j)) Workbooks(ExcelFiles(j)).Close SaveChanges:=False Next j

The length of the array is ExcelFiles = 3, but in fact the loop only executes Workbooks.Open (ExcelFiles(j)) for the first element of the array. It does not give any errors. Just pretends as if everything is done correctly.

And most importantly: with what can it be debugged?

  • View-Macros-Start recording ... Make your own manipulations. Stop recording, look at the code of the recorded macro, change what you need. Profit - Yura Ivanov
  • Will not go. The record is provided for specific steps and the array with all the file names in an arbitrary folder it does not write - Skotinin
  • "change what you need" you write from scratch, in the meantime after recording a blank macro, most of the questions should disappear. And there will be more specific questions like "how from vba to get a list of files in a folder?" or "how to open the file / directory selection dialog from vba?" who googling for sure. - Yura Ivanov
  • Read my question again, look at the answer below. Think about it. - Skotinin
  • Always happy to help. Hang in there ... - Yura Ivanov

1 answer 1

  1. You can specify the minimum array:

    ReDim ArrData (1 To 2, 1 To 1)

and add elements to it, but with saving the data you can only change the last dimension:

  ReDim Preserve ArrData (1 To 2, 1 To 5)

  1. The length of the array (the first dimension - the default, you can not specify):

    UBound (ArrData)

    UBound (ArrData, 2)

  2. Not a cycle to blame. A book is an object and you need to enter it into a variable as an object:

     Set wBook = Workbooks.Open(Filename:=j) With wBook ' действия в книге .Close End With

Using what to debug?

a) add. programs;

b) it is very useful to have the first line in the module

 Option Explicit

Install via Tools-Options

Catching syntax errors, undeclared variables, duplication of variables or procedure names. Och-ry-ry useful thing.

Example: " wBook " " wBook " - one letter "o" is Cyrillic. You can search for such an error for a long time, Option Explicit will indicate it immediately.

c) Debug.Print

In the right places of the code, using the operator, you can take data, parameters, variable values, intermediate calculations, and so on. and display the resulting Immediate window (show the window - Gtrl + G or via the View menu)