Summary: A nice little utility that reads some javascript in a file and formats it into a VBA string defeinition ready to supplied to the ScriptControl
So, writing a number of these posts I have to say that editing the Javascript in a VBA string is quite painful. So, I found myself switching over to Visual Studio running a Node.js application and editing code there, to bring it back into VBA we need to wrap quotes around it and have line continuations characters to break the string definition over several lines. After doing this manually I saw the value in writing some helpful code to automate it it, and so it is give below. First though, some sample output where one can see the code formatted into a block
sProg = "function filterOdd(vbArray) { " & _
" var numbers = new VBArray(vbArray).toArray(); " & _
" var filtered = []; " & _
" if (isArray(numbers)) { " & _
" for (var i = 0; i < numbers.length; i++) { " & _
" if (numbers[i] % 2 === 1 ) { " & _
" filtered.push(numbers[i]); " & _
" } " & _
" } " & _
" } " & _
" return filtered.toVBArray(); " & _
"} "
The modJavascriptSourceToVBAString standard module
'---------------------------------------------------------------------------------------
' Module : modJavascriptSourceToVBAString
' DateTime : 15/02/2018 16:55
' Author : Simon
' Purpose : Use ShellNotepadTempFile() to shell Notepad to a working temporary file where
' one can paste in javascript and then run ConvertToVBAString() to convert to
' something one can paste into VBA code.
'
' For previously created strings that also needs formatting use Reformat()
'
'---------------------------------------------------------------------------------------
Option Explicit
'* Tools->References
'* Scripting Microsoft Scripting Runtime C:\Windows\SysWOW64\scrrun.dll
'* IWshRuntimeLibrary Windows Script Host Object Model C:\Windows\SysWOW64\wshom.ocx
Private fso As New Scripting.FileSystemObject
Private Const clPAD As Long = 112
Private Const mcsJScriptFile As String = "JScriptFormatter.txt"
'---------------------------------------------------------------------------------------
' Procedure : ShellNotepadTempFile
' DateTime : 15/02/2018 16:45
' Author : Simon
' Purpose : Opens Notepad on our working file
'---------------------------------------------------------------------------------------
'
Private Sub ShellNotepadTempFile()
Dim sJScriptFilePath As String
sJScriptFilePath = JScriptFileFullPath
If Not fso.FileExists(sJScriptFilePath) Then
fso.CreateTextFile(sJScriptFilePath).Write _
"//paste javascript here, save and then run ConvertToVBAString()"
End If
Dim oShell32 As IWshRuntimeLibrary.WshShell
Set oShell32 = New IWshRuntimeLibrary.WshShell
oShell32.Run "notepad.exe " & sJScriptFilePath
End Sub
'---------------------------------------------------------------------------------------
' Procedure : ConvertToVBAString
' DateTime : 15/02/2018 16:42
' Author : Simon
' Purpose : Reads the file containing the Javascript and converts to a VBA string definition
'---------------------------------------------------------------------------------------
'
Private Sub ConvertToVBAString()
Dim vLinesOriginal As Variant, plLineCount As Long
vLinesOriginal = ReadFilesIntoArray(JScriptFileFullPath, plLineCount)
Dim vLines As Variant
vLines = vLinesOriginal
Dim lLineLoop As Variant
For lLineLoop = 0 To plLineCount - 1
vLines(lLineLoop) = VBALine(PadAndQuoteJavascript(vLines(lLineLoop), clPAD), lLineLoop, plLineCount)
Next
Debug.Print Join(vLines, vbNewLine)
'Stop
End Sub
'---------------------------------------------------------------------------------------
' Procedure : Reformat
' DateTime : 15/02/2018 16:50
' Author : Simon
' Purpose : Reads the file with a VBA string definition of javascript and reformats it
'---------------------------------------------------------------------------------------
'
Private Sub Reformat()
Dim vLinesOriginal As Variant, plLineCount As Long
vLinesOriginal = ReadFilesIntoArray(JScriptFileFullPath, plLineCount)
Dim vLines As Variant
vLines = vLinesOriginal
Dim lLineLoop As Variant
For lLineLoop = 0 To plLineCount - 1
vLines(lLineLoop) = Trim(vLines(lLineLoop))
Const csTrail As String = """ & _"
Const csLead0 As String = "sProg = """
If StrComp(Left$(vLines(lLineLoop), Len(csLead0)), csLead0, vbTextCompare) = 0 And _
StrComp(Right$(vLines(lLineLoop), Len(csTrail)), csTrail) = 0 Then
Dim vSplit1 As Variant
vSplit1 = VBA.Split(vLines(lLineLoop), csTrail)(0)
Dim vSplit2 As Variant
vSplit2 = VBA.Split(vSplit1, csLead0)(1)
ElseIf Left$(vLines(lLineLoop), 1) = """" And _
StrComp(Right$(vLines(lLineLoop), Len(csTrail)), csTrail) = 0 Then
vSplit2 = Mid$(vLines(lLineLoop), 2, Len(vLines(lLineLoop)) - Len(csTrail) - 1)
ElseIf Left$(vLines(lLineLoop), 1) = """" And Right$(vLines(lLineLoop), 1) = """" Then
vSplit2 = Mid$(vLines(lLineLoop), 2, Len(vLines(lLineLoop)) - 2)
Else
'bug
Stop
End If
vLines(lLineLoop) = VBALine(PadAndQuoteJavascript(vSplit2, clPAD), lLineLoop, plLineCount)
Next
Debug.Print Join(vLines, vbNewLine)
'Stop
End Sub
'---------------------------------------------------------------------------------------
' ____ ___ __ .__.__ .__ __ ___________ __ .__
'| | _/ |_|__| | |__|/ |_ ___.__. _ _____/_ __ ____ _____/ |_|__| ____ ____ ______
'| | / __ | | | __< | | | __)| | / _/ ___ __ |/ _ / / ___/
'| | / | | | | |_| || | ___ | | | | / | ___| | | ( <_> ) | \___
'|______/ |__| |__|____/__||__| / ____| ___ / |____/|___| /___ >__| |__|____/|___| /____ >
' / / / / / /
' DateTime : 15/02/2018 16:46
' Author : Simon
' Purpose : Utility Functions
'---------------------------------------------------------------------------------------
'
Private Function PadAndQuoteJavascript(ByVal sJavascript As String, ByVal lPad As Long)
PadAndQuoteJavascript = """" & Left$(sJavascript & String(lPad, " "), lPad) & """ "
End Function
Private Function VBALine(ByVal sJavascript As String, ByVal lLineIdx As Long, _
ByVal lLineCount As Long) As String
If lLineIdx = 0 Then
VBALine = VBATopLine(sJavascript)
ElseIf lLineIdx = lLineCount - 1 Then '* 0 based
VBALine = VBABottomLine(sJavascript)
Else
VBALine = VBAMidLine(sJavascript)
End If
End Function
Private Function VBABottomLine(ByVal sJavascript As String) As String
VBABottomLine = vbTab & vbTab & vbTab & sJavascript
End Function
Private Function VBAMidLine(ByVal sJavascript As String) As String
VBAMidLine = vbTab & vbTab & vbTab & sJavascript & " & _"
End Function
Private Function VBATopLine(ByVal sJavascript As String) As String
VBATopLine = vbTab & "sProg = " & sJavascript & " & _"
End Function
Private Function JScriptFileFullPath() As String
JScriptFileFullPath = fso.BuildPath(Environ$("tmp"), mcsJScriptFile)
End Function
Private Function ReadFilesIntoArray(ByVal sFilePath As String, ByRef plLineCount As Long)
Dim txtIn As Scripting.TextStream
Set txtIn = fso.OpenTextFile(sFilePath, ForReading, False, TristateUseDefault)
Dim dicLines As Scripting.Dictionary
Set dicLines = New Scripting.Dictionary
While Not txtIn.AtEndOfStream
dicLines.Add dicLines.Count, txtIn.ReadLine
DoEvents
Wend
txtIn.Close
Set txtIn = Nothing
plLineCount = dicLines.Count
ReadFilesIntoArray = dicLines.Items
End Function
No comments:
Post a Comment