PDA

View Full Version : Solved: EASY VBA ISSUE TO FINALIZE THE IMPLEMENTATION OF THE MERSEENE TWISTER RNG



LG915
03-02-2013, 12:40 PM
Hello everyone, I have spent quite a while to implement a sophisticated random number generator in C++, I now need to make a user friendly excel/VBA spreadsheet DSFMT128VBA.xslx that uses the .csv file created by the .exe. I've never practiced VBA, I really need your help.

1st step:

donwload this tar.gz archive: http://www.mediafire.com/file/ycg936qfbc3c625/Merseene_Twiser_128_BIT_RNG.tar.gz (winrar can be used for extraction)

It contains 4 files:

MT128good.exe: The uniform random variable [0,1] generator that create a CSV called unif.csv:
unif.csv: This excel . csv is created with different valeus each time the .exe is launched
msvr110d.dll: a dll you need to execute: MT128good.exe:
DSFMT128VBA.xslx; The EXCEL/VBA RNG spreadsheet that needs to be created with a button.

2nd step:

Create in DSFMT128VBA.xlsx a macro that exectutes the .exe MT128good.exe (I think ShellExecute has to be used) => a new .csv file still called unif.csv is generated with new random numbers.

3rd step

Implement the following macro to replace "." by "," in the csv.
Sub ReplaceText()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c = Replace(c, "TextToFind", "TextToReplace")
Next
End Sub

why do I get this error when using this macro on the .csv?: do I have to add semi columns or commas at the end of each line in the raw c++ produced csv?: http://www.mediafire.com/view/?5si0tk286m118sa

4th step:

Make a macro that copy pasts the csv column in D8 IN DSFMT128VBA.xlsx AND refress formula outputs.

Many thanks for your help! :help

snb
03-02-2013, 03:24 PM
Why not producing random numbers in VBA or in Excel ?

LG915
03-02-2013, 03:36 PM
Because the merseene twister allows you to reach for precision excel can't match.

Can you please tell me why I get the error: http://www.mediafire.com/view/?5si0tk286m118sa

Compile error: Expected line number or label or statement or end of statement? do I need to add semi columns in my raw .csv? or commas?

when using:

Sub ReplaceText()
Dim c As Range
For Each c In Module1.UsedRange
c = Replace(c, ".", ",")
Next
End Sub

and module 1 is an imported csv which contains:

0.989492209703375
0.184145098978442
0.365063754808973
0.253380136210709
0.512303430515757
0.279467114522703
0.558206434256604
0.302325086649532
0.292949402615390
0.110894333578039
0.771168133470690
0.703253925307222
0.421389240839892
0.472149722783674
0.176969965078023
0.706427742557473

Here's the link to all necessary files for this little VBA project: http://www.mediafire.com/file/ycg936qfbc3c625/Merseene_Twiser_128_BIT_RNG.tar.gz

snb
03-02-2013, 04:08 PM
Use the macrorecorder during opening the csv file.

LG915
03-02-2013, 04:19 PM
And also use the macro recorder to execute the .exe? I was told using the macro recorder was not optimal. The error above do you know where it comes from? If you take a look at this the code appears in red: http://www.mediafire.com/view/?5si0tk286m118sa
The csv is generated from c++ code without adding commas or semi columns at the end of each line isn't this the problem?

Zack Barresse
03-02-2013, 04:20 PM
@LG915: I have closed your other threads. This is your only warning - do not duplicate posts. I don't care how urgent you think the matter is. We have forum rules for a reason. If you feel you need to step outside of those rules, you will contact a Moderator or you will post the request in your thread itself. You will not duplicate posts like that again just to get the ability to post links. We do not appreciate stupid postings like these to circumvent forum rules/policy. You would be wise to read the forum rules. I have closed your other threads. You're welcome for not deleting them, so you can keep your post count.

LG915
03-02-2013, 04:47 PM
Well thank you leaving the links Mr Barresse! :friends:

Zack Barresse
03-02-2013, 04:48 PM
Very welcome. ;)

LG915
03-02-2013, 05:14 PM
Adding commas at line ends doesn't change anything: http://www.mediafire.com/view/?22dd3d2st5yt8on
I still get the error Expected line number or label or statement or end of statement... Is it legal to use .csv files as modules in VBA?

I get the error with the subroutine:
Sub ReplaceText()
Dim c As Range
For Each c In Module1.UsedRange
c = Replace(c, ".", ".")
Next
End Sub

where module 1 is the .csv

SamT
03-02-2013, 08:28 PM
Is it legal to use .csv files as modules in VBA?

No.

You need to read your homework instructions again.

Use VBA to Run the Exe;
Use VBA to edit the CSV in situ;
Use VBA to import the edited CSV into Excel.

macropod
03-03-2013, 02:14 AM
LG915,

It's not clear that you need to use vba at all. You refer to having Excel open a file and load the data starting at D8. From what I can see from your data file, it's not a CSV file at all - it's just a plain text file with a bunch of numbers in one column and a CSV extension. As a CSV file, that you load everything into column A, starting at row 1. If you wan to be able to load the data into a new workbook, starting at D8, simply change your unif.csv's contents, thus:

,
,
,
,
,
,
,
,,,0.989492209703375
,,,0.184145098978442
etc.
Whatever process you're using to generate the file should be able to do that much.

Then, to start Excel and load the data, simply call Excel with the appropriate command-line switch. See:
http://office.microsoft.com/en-us/excel-help/command-line-switches-for-excel-HA010158030.aspx

OTOH, if you do need to load the data into an existing Excel workbook, and you want to execut the load from within Excel, the code (for use with your existing data files) could be something like:
Sub DataImport()
Application.ScreenUpdating = False
Dim LCol As Long, LRow As Long, DataSet As String, StrData As String
LCol = 4: LRow = 8
With ThisWorkbook
DataSet = .Path & "\unif.csv"
If Dir(DataSet) <> "" Then
With .Sheets(1)
.Columns(LCol).ClearContents
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, StrData
.Cells(LRow, LCol).Value = StrData
LRow = LRow + 1
Loop
End With
Close #1
DataSet = ""
End If
End With
Application.ScreenUpdating = True
End Sub
Of course, the ThisWorkbook reference implies the macro will be stored in the Workbook itself, which means you can't use the .xlsx format - you can use .xls or .xlsm. Plus, the code assumes the data file will be found in the same folder as the workbook.

LG915
03-03-2013, 04:59 AM
G'day Paul Mate! I'm both french and australian and was very excited by your answer. Go wallabies! However:

I've modified the output csv as you told me it now looks like:

,
,
,
,
,
,
,
, , , 0.069647303284102
, , , 0.378642091316199
, , , 0.739876794443058
, , , 0.268178322227615
, , , 0.122680542993836
, , , 0.425977440622468
, , , 0.676481210136024
, , , 0.682518665462209
, , , 0.792315370926578

The .csv file is as you said just a c++ ofstream file and the .csv extenstion is added the .csv as a string with code.

When Excel opens it the commas are all in the first column.

Your lovely sub then logically loads an empty column in D8.

Wouldn't the solution be to generate a simple txt file with all the numbers listed.

and slightly modify your sub to ready and copy past the .txt file in D8?

Sorry for bothering and thank you for your precious help: :beerchug:

New .exe and .xls files are in attachment.

LG915
03-03-2013, 05:06 AM
Or keep everything in the first column of the csv and do something like:

Sub DataImport()
Application.ScreenUpdating = False
Dim LCol As Long, LRow As Long, LRow2 As Long, DataSet As String, StrData As String
LCol = 4: LRow = 8 : LRow = 1
With ThisWorkbook
DataSet = .Path & "\unif.csv"
If Dir(DataSet) <> "" Then
With .Sheets(1)
.Columns(LCol).ClearContents
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, StrData
.Cells(LRow2, 1).Value = StrData
LRow2 = LRow2 + 1
Loop
End With
Close #1
DataSet = ""
End If
End With
Application.ScreenUpdating = True
End Sub

LG915
03-03-2013, 05:53 AM
Thanks Paul I've managed importing the data from the csv,
now what I'd like to add to the following routine is the equivalent of a shellexecute command that executes the .exe (MT128good.exe) in the parent folder, Waits for 1 second to be sure the .csv 's generation is over and goes on with copy pasting the csv into the first column of the .xls and finally add a button/swich in the excel spreadsheet to be able to launch the macro whenever....


Sub DataImport()
Application.ScreenUpdating = False
Dim LCol As Long, LRow As Long, DataSet As String, StrData As String
LCol = 4: LRow = 8: LRow2 = 1
With ThisWorkbook
DataSet = .Path & "\unif.csv"
If Dir(DataSet) <> "" Then
With .Sheets(1)
.Columns(LCol).ClearContents
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, StrData
.Cells(LRow2, 1).Value = StrData
LRow2 = LRow2 + 1
Loop
End With
Close #1
DataSet = ""
End If
End With
Application.ScreenUpdating = True
End Sub

LG915
03-03-2013, 06:12 AM
This is what I came up with:




Sub ShellExec()
Dim strFile As String
Dim strAction As String
Dim lngErr As Long
With ThisWorkbook
strFile = .Path & "\MT128good.exe"
strAction = "OPEN"

lngErr = ShellExecute(0, strAction, strFile, "", "", 0)
End With

End Sub

Sub Sleeep()


Sleep (2000)
End Sub


Sub DataImport()
Application.ScreenUpdating = False
Dim LCol As Long, LRow As Long, DataSet As String, StrData As String
LCol = 4: LRow = 8: LRow2 = 1
With ThisWorkbook
DataSet = .Path & "\unif.csv"
If Dir(DataSet) <> "" Then
With .Sheets(1)
.Columns(1).ClearContents
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, StrData
.Cells(LRow2, 1).Value = StrData
LRow2 = LRow2 + 1
Loop
End With
Close #1
DataSet = ""
End If
End With
Application.ScreenUpdating = True
End Sub




But Shellexecute doesn't execute the Exe anyone know why?

The attachement has been updated.

ShellExecute and sleep are defined in module one as follows:




Public Declare Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long


Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)



:help :banghead:

snb
03-03-2013, 07:05 AM
Please keep it simple :
To 'import' a csv file this suffices:


Sub M_snb()
workbooks.open "G:\OF\test.csv"
end sub

LG915
03-03-2013, 07:08 AM
Well Paul came up with his routine and it works perfectly, i didn't just need to import the csv. I needed it to be copy pasted in the xls workbook, but this issue is solved. My trouble now is with shellexecute. The .exe isn't executed by the sub described above.

LG915
03-03-2013, 07:55 AM
Using Shell() The exe is now executed but It's supposed to generate a .csv file. If you double click on it in the attached folder you will witness the creations of the .csv. However the .csv file isn't generated when the exe is launched using: RetVal = Shell(.Path & "\MT128good.exe", 1)

:banghead: What am I missing?


Sub DataImport()

Application.ScreenUpdating = False
Dim LCol As Long, LRow As Long, DataSet As String, StrData As String
LCol = 4: LRow = 8: LRow2 = 1
With ThisWorkbook
RetVal = Shell(.Path & "\MT128good.exe", 1)
DataSet = .Path & "\unif.csv"
If Dir(DataSet) <> "" Then
With .Sheets(1)
.Columns(1).ClearContents
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, StrData
.Cells(LRow2, 1).Value = StrData
LRow2 = LRow2 + 1
Loop
End With
Close #1
DataSet = ""
End If
End With
Application.ScreenUpdating = True
End Sub

snb
03-03-2013, 08:40 AM
So inefficiency is your goal ?

LG915
03-03-2013, 09:07 AM
The only thing missing was the path to the .exe. Everything works fine now.