Visual Basic for Applications/Array Output Transfers

Summary
'''This VBA code module demonstrates four basic methods of array display. It is intended to run in MS Excel, though with the exception of the first method, transfer to the worksheet, it could be adapted easily for MS Word or other MS Office applications that run VBA.'''

Code Notes

 * The code first loads an array with selected random data.  Then the entire array is transferred to the worksheet.   The data is further formatted and displayed in the immediate window, in well spaced columns.   An additional copy of the formatted output is passed to the clipboard for further external use, and it is also sent to a text file to illustrate the method.
 * RndDataToArr can load an array with random data. The data types can be set as parameters, and further limits can be found within the procedure itself. Alpha, integer, decimal, dates, and mixed data are available, most random in both length and content.
 * Arr1Dor2DtoWorksheet can transfer either a one-dimensional or two dimensional array to a worksheet. It can be positioned at any location.   It checks that an array exists,that it is allocated, and its number of dimensions in setting the transfer range.
 * DispArrInImmWindow formats and displays a 2D array in the immediate window of the VBA editor.  It takes account of all data length in setting the well aligned columns.    There are parameters to set maximum decimal places and the choice of decimal point alignment or raw data.  The layout can handle mixed columns of text and numbers, though it has best appearance when all data of a column is of the same type.  The whole array's formatted output is available as a single string for external use, useful for arrays beyond 199 rows, as too big for the immediate window.
 * CopyToClip is used to pass a string to the clipboard.  It is used here to upload the formatted array string. The clipboard will retain the contents only until the calling application (Excel) closes.   It should be noted that other clipboard procedures in this series retain their content until the Windows platform closes.
 * GetFromClip retrieves the contents of the clipboard. It is used here purely for demonstration.  It passes the entire formatted string of the array to a text file.
 * WriteToFile opens and writes to a named text file.  It completely replaces any text that it already contains.  If the file does not exist the procedure makes it, in the same directory as the Excel file itself.

The VBA Code Module
Copy the entire code listing into an Excel VBA module, and run the top procedure to test the four array transfer methods. Save the file as type xlsm. The code writes to Sheet1, and to the immediate window of the VBA editor. Further array listings will be found on the clipboard and in a text file made for the purpose.