Selecting Python for a “weapon” of choice has many positive points, which I am not going to describe now. Still, if you want to run python code with the help of VBA and the Shell through the CommandPrompt of Windows, you are probably at the right place.
Let’s imagine, that you are trying to solve problem A from round 487 of CodeForces. Once, you realize what the problem is, you would make sure that you need to check 6 conditions per char, to have the answer YES. This is some working solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import sys def main(): string = sys.argv[1] result = "No" for i in range (2, len(string)): if ( string[i] != string[i-1] and string[i-1] != string[i-2] and string[i - 2] != string[i] and string[i] != '.' and string[i-1] != '.' and string[i-2] != '.' ): result = "Yes" print (result) if __name__ == '__main__': main() |
It reads the argument after the path and it passes it to the variable named “string”. Then it checks every letter and if even once the 6 conditions are ok, it returns “Yes”. Now, the task of the article is to take the code above and to run it 8 times, using the cells from column A as input. Column B on the screenshot below is the “Expected output” and column C is the result of the Python program. Column D is the status, comparing column B and C (expected and real result).
So what is needed to achieve a working program? First of all, we have to make the Shell script. Without VBA, plainly using the cmd, we have to run something like this:
C:\Python\CodeForces.py “AA..CB” >C:\Python\file2.txt
The “>” means that the export of the program would be written to file2.txt. Thus, if we write in VBA:
|
1 |
Shell "cmd.exe /S /c C:\Python\CodeForces.py ""AA..CB"" >C:\Python\file2.txt" |
The program would work quite ok, as expected. Now our only problem is to exchange the “AA..CB” and the paths with some variables, thus the program could be run easily multiple times. After we manage to do this, it is needed to open the file2.txt and to read the result from it. The result is then written in Excel, in the third column and in the forth column this result is checked vs the expected result. Thus, we have some primitive testing system and one may even call this TDD for Python 😀
The whole code is here:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Option Explicit Sub TestMe() Dim path As String: path = "C:\Python\" Dim pathExe As String Dim i As Long Dim txtStream As TextStream 'Library - Microsoft Scripting Runtime Dim fso As New FileSystemObject 'Library - Microsoft Scripting Runtime Dim fileName As String Columns("C:D").Clear For i = 1 To 8 fileName = "file" & i & ".txt" pathExe = path & "CodeForces.py" & " """ & Cells(i, 1) & """ >" & path & fileName Shell "cmd.exe /S /c " & pathExe Application.Wait Now + #12:00:01 AM# Set txtStream = fso.OpenTextFile(path & fileName) Cells(i, 3) = txtStream.ReadLine txtStream.Close 'Kill path & fileName If Cells(i, 3) = Cells(i, 2) Then Cells(i, 4) = "Pass..." Next i End Sub |