Creating and working with classes in VBA is very different from working with js classes. In fact, JavaScript is a classless language – there is no such thing as a class. This is not so scary as it sounds though, as there are various ways to make JavaScript behave as if there were.
There are plenty of articles on VBA classes on this site, or if they are new to you, perhaps you should take a look at Getting Started with Classes
Much of the code referred to here is the mcpher library. You can find out how to include it in your project here.
Allocating memory for an instance of the class
VBA
Set dcell = New cCell
javaScript
var cc = new cCell();
Not so different there, but notice that the class in js is actually a call to a function. The constructor for this class looks like this, and if necessary any housekeeping associated with creating a new instance could be done inside this function. Note that defining a function in this way is the only time you need to put a semi-colon after the curly brackets (some interpreters don’t care, but strictly speaking it should be there
var cCell = function () {
};
Properties
In VBA there is a fair amount of fuss around declaring get/let/set properties, and the allocation private space for them inside the class instance, but there is a good reason for this – to be able to separate readonly from read/write properties. Here is a couple of properties of the cCell class
Option Explicit
private pValue As Variant ' value of cell when first loaded
private pColumn As Long ' column number
private pKeepFresh As Boolean ' update with value when accessed
private pParent As cDataRow ' cDataRow to which this belongs
Public Property Get Row() As Long
Row = pParent.Row
End Property
Public Property Get Column() As Long
Column = pColumn
End Property
in js, you declare a property just by mentioning it, but in doing so, it becomes readable and writable. Here is the .create method of the ccell class
cCell.prototype.create = function( par , colNum, rCell, keepFresh, v){
// deal with optional parameters
this.value = fixOptional ( v , rCell.getValue() );
this.keepFresh = fixOptional(keepFresh, false);
this.column = colNum;
this.parent = par;
this.row = par.row;
this.where = rcell;
return this;
};
by assigning values to this.column, this.parent etc., we create the property and can access it later.
Readonly properties
If you want to duplicate the behavior of VBA, you need to simulate the js class in another way. Here’s an example protecting a private copy of a property and exposing its value through a related method. The only thing with this is that you would need to define all methods inside the constructor for this class (rather than extending the prototype), so that they could have the same visibility to the private property, as they have in VBA
cCell = function() {
var pParent = 0;
this.parent = function() {
return pParent;
}
};
Methods
In VBA, public methods are procedures associated with that class and get associated simply by being in the same class module.
As previously mentioned, js doesn’t really have classes but there are a few ways of simulating class-like behavior. I prefer to use a function to define a class constructor, and then add methods to that function’s constructor using its prototype. You can see that in the create method above. Here is the entire ccell class converted to google apps script. It ends up being more concise than the VBA version.
var cCell = function () {
};
cCell.prototype.create = function( par , colNum, rCell, keepFresh, v){
// deal with optional parameters
this.value = fixOptional ( v , rCell.getValue() );
this.keepFresh = fixOptional(keepFresh, false);
this.column = colNum;
this.parent = par;
this.row = par.row;
this.where = rcell;
return this;
};
cCell.prototype.commit = function( p){
if (!isUndefined(p)) this.value = p;
this.where.setValue(this.value);
return this.refresh();
};
cCell.prototype.needSwap = function( cc,e){
switch (e) {
case eSort.eSortAscending:
return LCase(this.toString) > LCase(cc.toString);
case eSort.eSortDescending:
return LCase(this.toString) < LCase(cc.toString);
default:
return false;
}
};
cCell.prototype.refresh = function(){
this.value = this.where.getValue();
return this.value;
}
cCell.prototype.toString = function(sFormat){
if (isUndefined(sFormat)) {
return this.value.toString();
}
else {
//TODO
}
}
and for comparision, here is the VBA version
Option Explicit
Private pValue As Variant ' value of cell when first loaded
Private pColumn As Long ' column number
Private pKeepFresh As Boolean ' whether or not to update with current cell value when accessed
Private pParent As cDataRow ' cDataRow to which this belongs
Public Property Get Row() As Long
Row = pParent.Row
End Property
Public Property Get Column() As Long
Column = pColumn
End Property
Public Property Get Parent() As cDataRow
Set Parent = pParent
End Property
Public Property Get where() As Range ' return the range from whence it came
If Row = 0 Then
' its a heading
Set where = pParent.where.Offset(Row, pColumn - 1).Resize(1, 1)
Else
Set where = pParent.where.Offset(, pColumn - 1).Resize(1, 1)
End If
End Property
Public Property Get Refresh() As Variant ' refresh the current value and return it
pValue = where.Value
Refresh = pValue
End Property
Public Property Get toString(Optional sFormat As String = vbNullString) As String ' Convert to a string, applying a format if supplied
Dim s As String
If Len(sFormat) > 0 Then
toString = Format(Value, sFormat)
Else
s = where.NumberFormat
If Len(s) > 0 And s <> "General" Then
toString = Format(Value, s)
Else
toString = CStr(Value)
End If
End If
End Property
Public Property Get Value() As Variant ' return the value, refreshing it if necessary
If pKeepFresh Then
Value = Refresh
Else
Value = pValue
End If
End Property
Public Property Let Value(p As Variant)
If pKeepFresh Then
Commit = p
Else
pValue = p
End If
End Property
Public Function needSwap(cc As cCell, e As eSort) As Boolean
' this can be used from a sorting alogirthm
Select Case e
Case eSortAscending
needSwap = LCase(toString) > LCase(cc.toString)
Case eSortDescending
needSwap = LCase(toString) < LCase(cc.toString)
Case Else
needSwap = False
End Select
End Function
Public Function Commit(Optional p As Variant) As Variant
Dim v As Variant
If Not IsMissing(p) Then
pValue = p
End If
where.Value = pValue
Commit = Refresh
End Function
Public Function create(par As cDataRow, colNum As Long, rCell As Range, _
Optional keepFresh As Boolean = False, _
Optional v As Variant) As cCell ' Fill the Cell up
' if v is specifed we knw the value without needing to access the sheet
If IsMissing(v) Then
pValue = rCell.Value
Else
pValue = v
End If
pColumn = colNum
pKeepFresh = keepFresh
Set pParent = par
Set create = Me ' return for convenience
End Function
For more like this, see From VBA to Google Apps Script . Much of the code referred to here is the mcpher library. You can find out how to include it in your project here.
Transitioning is covered more comprehensively in my my book, Going Gas – from VBA to Apps script