writing output to the text file

Question

I need to find the first 20,000 random numbers generated by RND -1. I have written the following code, which allows me to see those numbers in the Immediate Window:

Sub xx()
Dim i As Long
Rnd -1
For i = 1 To 20000
    Debug.Print Rnd
Next i
End Sub

However, only the last 199 lines are stored there for me to copy and paste. How can I write the entire output to a text file instead?

Many thanks

or just write it out to a worksheet and copy/paste from there. In your FOR loop: `Sheet1.Range("A" & i).value = Rnd` Also suggest before that line that you use command `Randomize` to initialize the seed. Otherwise it won't be so random. — JNevill, May 14 '18 at 16:27
Thanks JNevill, that's a good suggestion and what I ended up doing — rw2, May 15 '18 at 13:07

score 1 · Answer 1 · answered May 14 '18 at 16:30

Since your goal seems to be to get a lot of random numbers in the clipboard, you can do the following:

in the VBA editor under tools/references, add a reference to Microsoft Forms 2.0 Object Library and then use:

Sub RandToClip(n As Long)
    Dim clip As New DataObject
    Dim A As Variant
    Dim i As Long

    ReDim A(1 To n)
    Randomize

    For i = 1 To n
        A(i) = Rnd()
    Next i

    clip.SetText Join(A, vbCrLf)
    clip.PutInClipboard
End Sub

If you then e.g. enter RandToClip 20000 in your clipboard available for pasting somewhere else.

Victor K · Answer 2 · 2018-05-14T20:46:39.873

Answering your question: here is the basic function for that task. Make sure to add checks of whether file exists, not locked, etc. But take a look at the solution provided by John Coleman as it may be a better solution for your task.

Public Function WritetoTXT (Byval Text as String, byval FilePath as String)    
    Dim TextFile As Long
    TextFile = FreeFile
    Open Path For Append As TextFile
    Print #TextFile, Text
    Close TextFile
End Function

In your code:

Sub xx()
Dim i As Long
Rnd -1
For i = 1 To 20000
    WritetoTXT  Rnd, "your file path here"
Next
End Sub

Edit: As pointed out in comments to decrease overhead you can combine your code to the following:

Sub xx()
Dim i As Long
Rnd -1
Dim TextFile As Long
TextFile = FreeFile
Open "your file path here" For Append As TextFile
For i = 1 To 20000
    Print #TextFile,  Rnd
Next
Close TextFile
End Sub

You'll want to open the file once, write everything into it, then close it. Open+Close IO overhead over 20K iterations sounds like a massive performance hit to take, no? — Mathieu Guindon, May 14 '18 at 17:08

score 1 · Answer 3 · answered May 14 '18 at 18:39

You can easily write to a text file (file stream) by using a FileSystemObject. See example below for working code in a module:

Global fso As New FileSystemObject

Public Function GenRandomNumbers(ByVal n As Long) As String
    Dim i As Long
    Dim res As String
    Rnd -1
    For i = 1 To n
        res = res & CStr(Rnd()) & vbCrLf
    Next i
    GenRandomNumbers = res
End Function

Public Sub WriteRandomNumbers(ByVal n As Long, ByVal filename As String)
    Dim fs As TextStream
    Set fs = fso.CreateTextFile(filename, Overwrite:=True)
    fs.Write GenRandomNumbers(n)
    fs.Close
End Sub

In the immediate window you can write:

WriteRandomNumbers 20000, "Result.txt"

Long strings concatenation has significant impact on performance. Use `Join` instead. — omegastripes, May 14 '18 at 21:58
I tested it with 20000 strings from the immediate window with no discernible delay whatsoever. So in theory yes, but practically it doesn't matter for this case. — John Alexiou, May 14 '18 at 22:33

writing output to the text file

3 Answers3