classes and javaScript

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

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 . Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. 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, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.




Comments