Excel, JavaScript, VBA, VBScript

Excel Javascript 
VBA/VB6(.vb)
Alt+F11, Alt+I, M
Insert>Module     | Accesses macros and code
 
VBScript(.vbs or .vbe)
crscript wscript
Global var |  You can name cells by clicking

the Name Box (that thing next to the formula bar

which says "A1" for example) and typing a name

Stored array | A1:A2
Array constant | INDEX({1,2,3},1)
multiline string Alt+Enter

 
var a = [1, 2]
const a = [1, 2];// not exactly the same thing but close
var s = "testing\
1\
2";
a[1][2];
"{0}".replace("0","testing")
Range("MyBook.xls!MyRange").Font.Italic = True

Sub ClearRange(): Application.Goto Reference:="MyBook.xls!MyRange" : Selection.ClearContents : End Sub

Public globalv
Sub m(): globalv = 1: End Sub: m

TypeOf 1 Is Integer


CStr(Environ("USERPROFILE"))
Replace("testing", "{0}", "0")

								
' no multiline string support
							

Const Constant = Value
x = CInt("1")
' CBool, CByte, CCur, CDate, CDbl, CInt, CLng, CSng, CStr
WScript.Echo TypeName(x)
=DATE(1999,1,1) 
Form_Load = function(){
x = "Hello World"
  alert(x);
}
Form_Load()
getFullYear()	Get the year as a four digit number (yyyy)
getMonth()	Get the month as a number (0-11)
getDate()	Get the day as a number (1-31)
getHours()	Get the hour (0-23)
getMinutes()	Get the minute (0-59)
 
Private Sub Form_Load()
  Dim x As String ' type optional
  x = "Hello World"
  MyStr = Format(Time, "Long Time")
  MsgBox x
End Sub
Form_Load()
Format(#1/27/1993#, "yyyy-MM-dd Hh:mm:Ss")
MyStr = Format(Time, "Long Time")
MyStr = Format(Date, "Long Date")
MyStr = Format(MyTime, "h:m:s")    ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss am/pm")    ' Returns "05:04:23 pm".
MyStr = Format(MyTime, "hh:mm:ss AM/PM")    ' Returns "05:04:23 PM".
MyStr = Format(MyDate, "dddd, mmm d yyyy")    ' Returns "Wednesday, Jan 27 1993".
' If format is not supplied, a string is returned.
MyStr = Format(23)    ' Returns "23".
' Function can return value sub can not
' User-defined formats.
MyStr = Format(5459.4, "##,##0.00")    ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00")    ' Returns "334.90".
MyStr = Format(5, "0.00%")    ' Returns "500.00%".
MyStr = Format("HELLO", "<")    ' Returns "hello".
MyStr = Format("This is it", ">")    ' Returns "THIS IS IT".

<!doctype html><meta http-equiv="x-ua-compatible" content="IE=10">
<script type="text/vbscript">
msgbox "Hello World!"
</script>
						
Function CalculateArea(Radius)
    CalculateArea = Radius * Radius * 3.14159
End Function
=A3:A4=A3:A4 JavaScript Numbers are Always 64-bit Floating Point 
'Byte,Integer,Long,Single,Double,Currency,Decimal,
'String(n),Date,Boolean,Object,Variant
'requires you to define variable if explicit
Option Explicit On
Dim a, b, c, d, m, n As Integer
Const MyInteger As Integer = 42
Dim x, y As Double
Dim, Set, RefBy, ByVal
result = 5 < 10 ? "Yes it is" : "No it isn't" 
result = IIf(5 < 10, "Yes it is", "No it isn'")'
 
var message = "",
   count = 0;
if ( count == 0){
  message = "There are no items";
} else if(count == 1){
  message = "There is 1 item";
}else{
  mesage = "There are "+count+ " items.";
}
 
Dim count As Integer = 0
Dim message As String
If count = 0 Then
    message = "There are no items." 
ElseIf count = 1 Then
    message = "There is 1 item." 
Else
    message = "There are " & count & " items." 
End If
&& || ^ ! <> && ||  === 
And Or Xor Not <> = AndAlso OrElse 
Is
For(var i = 0;i<2;i++){

alert(i);

}

 
Dim i as Integer
For i = 0 To 2
  MsgBox i
Next i
Dim i as Variant:  i = 0   
While i <
    MsgBox i
    i = i + 1
Wend

fruits = Array("apple", "orange", "cherries")
Dim fruitnames As Variant
For Each Item In fruits
  fruitnames = fruitnames & Item & Chr(10)
Next
MsgBox fruitnames
Public Sub OnErrorDemo()
   On Error GoTo ErrorHandler    'Dim x, y, z As Integer
   x = 50
   y = 0
   z = x / y   ' Divide by ZERO Error Raises
  
   ErrorHandler:    ' Error-handling routine.
   Select Case Err.Number   ' Evaluate error number.
      Case 10   ' Divide by zero error
         MsgBox ("You attempted to divide by zero!")
      Case Else
         MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
   End Select
   Resume Next
End Sub
 Dim i
For i = 1 To 3
MsgBox i
Next 
debugger;

								
							
Stop
 
N/A
 
Function MyMax(ParamArray agrs())

End Function
function Class(){

alert("Init");

}

// Functions are like new objects

// No terminate fn

// types are like objects

 
'Classes don't really have a syntax exactly 
Private Sub Class_Initialize()
  MsgBox "Init"
End Sub
Private Sub Class_Terminate()
  MsgBox "Terminate"
End Sub
Dim C As Class

							
							Set C = New Class
'cpearson.com/excel/classes.aspx
Type Employee
    Name As String
    Salary As Double
End Type

Dim i As Employee
i.Name = "Joe Smith"
i.Salary = 40000
// LBound(a)==0 to UBound(a)==a.length 
Function Split1()
   Dim strTest As String
   Dim strArray() As String
   Dim intCount As Integer
  
   strTest = "Fred & Wilma & Barney & Betty"
   strArray = Split(strTest, "&")
   
   For intCount = LBound(strArray) To UBound(strArray)
      MsgBox Trim(strArray(intCount))
   Next
End Function
a = array("Red","Blue","Yellow")
b = join(a,"$")

Sub OptionalArgs(i As String, Optional s As String = "init") 
  'MsgBox 1
End Sub
Shell = new ActiveXObject("WScript.Shell");
DesktopPath = Shell.SpecialFolders("Desktop");
link = Shell.CreateShortcut(DesktopPath + "\\test.lnk");
// link.Arguments = "1 2 3";
link.Description = "test shortcut";
// link.HotKey = "CTRL+ALT+SHIFT+X";
// link.IconLocation = "app.exe,1";
link.TargetPath = "C:\\Program Files\\Mozilla Firefox\\firefox.exe";
// link.WindowStyle = 3;
// link.WorkingDirectory = "c:\\blah";
link.Save();		 ' Set Shell = CreateObject("WScript.Shell")
' DesktopPath = Shell.SpecialFolders("Desktop")
' Set link = Shell.CreateShortcut(DesktopPath & "\test.lnk")
' link.Arguments = "1 2 3"
' link.Description = "test shortcut"
' link.HotKey = "CTRL+ALT+SHIFT+X"
' link.IconLocation = "app.exe,1"
' link.TargetPath = "c:\blah\app.exe"
' link.WindowStyle = 3
' link.WorkingDirectory = "c:\blah"
' link.Save
window.onload = function(){ alert("onload"); };

document.getElementById("ComboBox1").onchange = function(){

aler("testing");

}

"1\"2" // double quotes

 Sub WorkBook_Open()

MsgBox "Open"

End Sub

Private Sub ComboBox1_Change()

MsgBox "testing"

End Sub

"1" & Chr(34) & "2"

https://stackoverflow.com/q/13221922

 Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Copy

With ActiveWorkbook

.SaveAs Filename:=Environ("TEMP") & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook

.Close SaveChanges:=False

End With

Sub Add_comment()

Dim cmt As String
cmt = “My comment”

If ActiveCell.Comment Is Nothing Then
  ActiveSheet.Range(Application.ActiveCell.Address).AddComment cmt
Else
  MsgBox “This cell already has a comment”
End If

End Sub

Sub Delete_comment()

ActiveSheet.Range(Application.ActiveCell.Address).Comment.Delete

End Subo

' https://stackoverflow.com/questions/14822672/parsing-a-json-object-array-in-excel-vba

' https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba
<input id=button type=button>

document.getElementById('button').onclick = function(){

;

}

var favDrink = prompt("What's your favorite cocktail drink?", "Default");

Private Sub CommandButton1_Click()

Worksheets(1).Cells(1, 1) = VBAProject.UserForm1.TextBox1
Worksheets(1).Cells(2, 1) = VBAProject.UserForm1.TextBox2

End Sub

UserForm1.Show
a = Split("Red $ Blue $ Yellow","$")
For i = 0 to ubound(a)
MsgBox i&" "&a(i)
Next 
Dim Arr() As Variant
Arr = Range("A1:B10")
Dim R As Long
Dim C As Long
For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
        Debug.Print Arr(R, C)
    Next C
Next R

var TypeDictionary = {
    0: 'vbEmpty',
    1: 'vbNull',
    2: 'vbInteger',
    3: 'vbLong',
    4: 'vbSingle',
    5: 'vbDouble',
    6: 'vbCurrency',
    7: 'vbDate',
    8: 'vbString',
    9: 'vbObject',
    10: 'vbError',
    11: 'vbBoolean',
    12: 'vbVariant',
    13: 'vbDataObject',
    17: 'vbByte'
};

var GetType = function() {
    return TypeDictionary[arguments[0]];
};
 
Dim TypeDictionary

Set TypeDictionary = CreateObject("Scripting.Dictionary")
TypeDictionary.Add 0, "vbEmpty"
TypeDictionary.Add 1, "vbNull"
TypeDictionary.Add 2, "vbInteger"
TypeDictionary.Add 3, "vbLong"
TypeDictionary.Add 4, "vbSingle"
TypeDictionary.Add 5, "vbDouble"
TypeDictionary.Add 6, "vbCurrency"
TypeDictionary.Add 7, "vbDate"
TypeDictionary.Add 8, "vbString"
TypeDictionary.Add 9, "vbObject"
TypeDictionary.Add 10, "vbError"
TypeDictionary.Add 11, "vbBoolean"
TypeDictionary.Add 12, "vbVariant"
TypeDictionary.Add 13, "vbDataObject"
TypeDictionary.Add 17, "vbByte"

Public Function GetType(argument)
    GetType = TypeDictionary.Item(VarType(argument))
End Function
 
https://stackoverflow.com/questions/37758275/vba-looking-for-a-fast-way-to-highlight-every-other-row
https://stackoverflow.com/questions/26356672/how-to-access-sql-server-from-vba-in-a-non-deprecated-way
https://stackoverflow.com/questions/2050505/way-to-run-excel-macros-from-command-line-or-batch-file
VB.NET, VBA and VBScript are all different langauges.  Unlike VBA, VBScript has only one data type — Variant, so you don't specify the data type when declaring a variable.
$.xlsm
Excel is stupid
 - you can't put multiple links in the same cell 
 - you can't put images(only links) in a cell, although you can attach it as a comments background
 - it is only scriptable with
   - VBA, VB.NET, C++, Powershell
   - Mathematica
   - http://xlwings.org
   - https://datanitro.com/pricing
https://tduhameau.wordpress.com
https://excelk.com/
http://excelmatters.com
http://www.automateexcel.com/functions
http://www.cpearson.com/Excel/ListFirstAndLastElements.aspx
http://businessfunctions.com/index.php?pageno=156
http://www.techonthenet.com/excel/formulas/pmt.php
https://www.tutorialspoint.com/vbscript/
https://www.automateexcel.com/vba-code-library
Reading Excel with Python python-excel.org
Excel Solver
f2 - Object Browser
.Cells(0,0) ' -1 -1 cell
Named Ranges Excel allows for stuff like Sum(Students)
Access macro actions represent only a subset of the commands available in VBA.
Redim Preserve  ' resize array
VBA: Visual Basic for Applications Different Versions
  			VSTO: Visual Studio Tools for Office
  			VSTA: Visual Studio Tools for Applications
  			VS.NET: Visual Studio.NET – currently version 2003. 2005 due next month.
  			IDE: Integrated Development Environment (in Excel, this is the visual basic editor)
  			
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "http://www.somedomain.com"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send("")

Dim fso as Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile as Object
Set oFile = FSO.CreateTextFile(".\test2.txt")
oFile.WriteLine "test" 
oFile.Close
Set fso = Nothing
Set oFile = Nothing

HouseCalc 99800, 43100
Call HouseCalc(380950, 49500)

named arguments
MsgBox Title:="Task Box", Prompt:="Task Completed!"

			

ActiveSheet.Range("A1").Select

			ActiveCell.CurrentRegion.Copy

			ActiveSheet.Range("A2").Select

			ActiveSheet.Paste

Option Explicit
'code loosely based on 'http://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop
'actual code from https://stackoverflow.com/questions/23580472/calling-javascript-files-from-vba
'code allows you to run JScript code in Excel/VBA
'Tools->References->
'MSScriptControl; Microsoft Script Control 1.0;  {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx
Private moScriptEngine As ScriptControl

Public Property Get ScriptEngine()
    If moScriptEngine Is Nothing Then
        Set moScriptEngine = New ScriptControl
        moScriptEngine.Language = "JScript"
        'moScriptEngine.AllowUI = True
    End If
    Set ScriptEngine = moScriptEngine
End Property

Do While True
    WScript.StdOut.Write("$ ")

    line = Trim(WScript.StdIn.ReadLine)

    If LCase(line) = "exit" Then Exit Do

    On Error Resume Next
    Execute line
    If Err.Number <> 0 Then
        WScript.StdErr.WriteLine Err.Description
    End If
    On Error Goto 0
Loop

Public Sub AddJSFile(ByVal sJSFilename As String)
    Debug.Assert LCase$(Right$(sJSFilename, 3)) = ".js"
    Dim fso As Object 'Scripting.FileSystemObject
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")

    Debug.Assert fso.FileExists(sJSFilename)
    Dim fil As Object 'Scripting.File
    Set fil = fso.GetFile(sJSFilename)

    Dim ins As Object 'Scripting.TextStream
    Set ins = fil.OpenAsTextStream(1)  '1=ForReading

    Dim sCode As String
    sCode = ins.ReadAll

    ins.Close
    Set ins = Nothing
    Set fil = Nothing
    Set fso = Nothing

    Debug.Assert Len(sCode) > 0
    ScriptEngine.AddCode sCode
End Sub

'!Fellow StackOverflow users, press F5 on this Sub as this is entry point!
Function JS(ByVal s As String)
    AddJSFile ActiveWorkbook.Path & "\testing.js"
    JS = ScriptEngine.Eval(s)
End Function

PS = Application.PathSeparator

CStr(Environ("USERPROFILE") & "\Application Data")
process.env[(process.platform == 'win32') ? 'USERPROFILE' : 'HOME']