1

I have this function written in VBA that I would like to convert to Apps Script, but I have not found anything similar to declare a variable of static type to keep the range object saved in the rngLast variable with multiple executions of function onSelectionChange (e):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rngLast As Range
If Not Application.Intersect(Target, Range("A1:Z100")) Is Nothing Then
    If Not rngLast Is Nothing Then
    If rngLast.Address <> Target.Address Then
    MsgBox "Lost focus " & rngLast.Address
    End If
    End If
    
    MsgBox "Got focus " & Target.Address
    Set rngLast = Target
End If
End Sub

In Apps Script I wrote it like this:

var rngLast;

function onSelectionChange(e) {
var spreadsheet = SpreadsheetApp.getActive();
var sh = spreadsheet.getActiveSheet();
var rowActCell = e.range.getRow();
var colActCell = e.range.getColumn();

  if((rowActCell >=1 && rowActCell <=100) && (colActCell >=1 && colActCell <= 26)) {
    if(rngLast != undefined || rngLast != null) {
      if(rngLast.getA1Notation() != e.range.getA1Notation()) {
        Browser.msgBox("Lost focus " + rngLast.getA1Notation());
      }
    }
    Browser.msgBox("Got focus " + e.range.getA1Notation());
    rngLast = e.range
  }
}

but I can't keep the value of e.range saved in rngLast so it doesn't work. In practice, every time onSelectionChange (e) is activated by changing the selection cell, rngLast assumes the undefined state. To test how it works I replaced: var rngLast; with var rngLast = SpreadsheetApp.getActive().getActiveSheet().getRange("A1");

I hope with my bad English that I have explained myself ;-)

Thanks bye Gabriele

braX
  • 11,506
  • 5
  • 20
  • 33
Gabriele
  • 11
  • 2
  • I am curious, have you tried https://developers.google.com/apps-script/guides/macro-converter/convert-files ? – Kos Dec 18 '21 at 17:05
  • Use properties service to store previous row, column number data. – TheMaster Dec 18 '21 at 17:20
  • braX I've rolled back the tags, as I felt [vba] helps in categorizing the question and in future searches. – TheMaster Dec 18 '21 at 19:19
  • @TheMaster In general, having both tags just gives the impression that questions about converting code from one language to another are not out of scope and will encourage more out of scope questions. – braX Dec 18 '21 at 19:47
  • 1
    Previously: https://stackoverflow.com/questions/24721226/how-to-define-global-variable-in-google-apps-script or https://stackoverflow.com/questions/14039988/persistant-variable-in-google-apps-script for example – Tim Williams Dec 18 '21 at 19:58
  • @braX I agree full code conversion questions are offtopic, but this is a great example of questions that are welcome, as it focuses on a specific feature of [vba], that the author is trying to implement in apps script, while showing his efforts clearly. I would even recommend this question as a example to those asking a blatant full code conversion questions. – TheMaster Dec 18 '21 at 21:24

0 Answers0