How to pass file references to a sub in VBA
 

This might be a simple question for you, but it's not for me even after years
of VB...

Within one of the procedures I am writing, at several different places I
need to flush the content of a string variable into a text file on disc.

Instead of repeating several times the same instructions which write the
variable to the file, I wonder whether it is possible to create a dedicated
SUB that does it for me. In this case, how would I pass the reference to the
file to that SUB?

See exemple below if needed.

Thank you.

Stefano Gatto


Sub CreateFileWithNiceNumbers()
Dim lngNumber As Long
Dim intCounter As Integer

Open "C:\temp\NiceNumbers.txt" For Output As #1
For intCounter = 1 To 250
lngNumber = Int(1000 * Rnd + 1) 'random number between 1 and 1000
If lngNumber 500 And lngNumber < 600 Then
Print #1, "A nice number is " & lngNumber
End If
Next intCounter

For intCounter = 1 To 80
lngNumber = Int(400 * Rnd + 1) + 300 'random number between 300 and
700
If lngNumber 500 And lngNumber < 600 Then
Print #1, "A nice number is " & lngNumber
End If
Next intCounter
End Sub

I would like to have this repeating code:

If lngNumber 500 And lngNumber < 600 Then
Print #1, "A nice number is " & lngNumber
End If

in one single place, like "Sub WriteToFile(lngNumber)". How would I pass the
file reference?

How to pass file references to a sub in VBA
 

Stefano,

Use code similar to the following:

Option Explicit
Public FileNumber As Integer

Sub OpenFile()
FileNumber = FreeFile
Open "C:\Test.txt" For Output Access Write As #FileNumber
End Sub

Sub WriteIt(Txt As String)
Print #FileNumber, Txt
End Sub

Sub CloseFile()
Close #FileNumber
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



"Stefano Gatto" wrote in message
...
This might be a simple question for you, but it's not for me even after
years
of VB...

Within one of the procedures I am writing, at several different places I
need to flush the content of a string variable into a text file on disc.

Instead of repeating several times the same instructions which write the
variable to the file, I wonder whether it is possible to create a
dedicated
SUB that does it for me. In this case, how would I pass the reference to
the
file to that SUB?

See exemple below if needed.

Thank you.

Stefano Gatto


Sub CreateFileWithNiceNumbers()
Dim lngNumber As Long
Dim intCounter As Integer

Open "C:\temp\NiceNumbers.txt" For Output As #1
For intCounter = 1 To 250
lngNumber = Int(1000 * Rnd + 1) 'random number between 1 and 1000
If lngNumber 500 And lngNumber < 600 Then
Print #1, "A nice number is " & lngNumber
End If
Next intCounter

For intCounter = 1 To 80
lngNumber = Int(400 * Rnd + 1) + 300 'random number between 300 and
700
If lngNumber 500 And lngNumber < 600 Then
Print #1, "A nice number is " & lngNumber
End If
Next intCounter
End Sub

I would like to have this repeating code:

If lngNumber 500 And lngNumber < 600 Then
Print #1, "A nice number is " & lngNumber
End If

in one single place, like "Sub WriteToFile(lngNumber)". How would I pass
the
file reference?