CODEWITHSUNDEEP
excelvbagoogle-apps-scriptgoogle-sheets
Jason Neel
Jason Neel

Reputation: 11

Script Running in an Excel File and I want it to work in Google Sheets

I have an excel file (it is a long score sheet) that runs a script that tells the file to show the top portion of the file, and after a few seconds the file shifts down, and shows the bottom edge of the Excel file. This excel file is a score sheet for a trivia night and I am trying to put the excel file in google sheets so the judges can enter the scores in live. Right now I have the file in Sheets so that is good, but when I go to copy the script from the excel file and put it in the google script editor I keep getting an error message when attempting to save or run the script. (Missing ; before statement. (line 1, file "Code")Dismiss)

Below are two examples. The first example is the script copied right from the excel file and the second example is what the script looks like in googles script editor. Any ideas how I can get this script to work and run properly? ' Example 1:

Private Sub CommandButton1_Click()
    Call scrollPage
End Sub

Sub scrollPageForced()
    Dim rowStart As Integer
    Dim rowMax As Integer
    rowStart = 3    'The first row index to scroll to
    'rowMax = 41     'The maximum row to scroll to (that will be visible )

    rowMax = ActiveSheet.Range("AA2").Value

    Dim MyW As Window
    Dim rowI As Integer

    Set MyW = Windows(1)
    rowI = MyW.VisibleRange.Cells.Row + (MyW.VisibleRange.Cells.Rows.Count - 1)

    If rowI > rowMax Then rowI = rowStart
    Range("A" + CStr(rowI)).Select
    Application.Goto Reference:=ActiveCell, Scroll:=True
    Range("BB" + CStr(rowI)).Select  'Hide selection by selecting a bogus element on the SAME ROW
    ActiveWindow.SmallScroll ToLeft:=100 'Scroll back left to A (BB ==44)

    Application.OnTime Now + TimeValue("00:00:" + CStr(ActiveSheet.Range("AC2").Value)), "!Sheet1.scrollPage"
End Sub

Sub scrollPage()

    If ActiveSheet.Range("K2").Value <> 1 Then Call scrollPageForced

End Sub

Example 2:

Private Sub Command Button1_Click()
    Call scrollPage
End Sub

Sub scrollPageForced()
    Dim rowStart As Integer
    Dim rowMax As Integer
    rowStart = 3    'The first row index to scroll to
    'rowMax = 41     'The maximum row to scroll to (that will be visible )

    rowMax = ActiveSheet.Range("AA2").Value

    Dim MyW As Window
    Dim rowI As Integer

    Set MyW = Windows(1)
    rowI = MyW.VisibleRange.Cells.Row + (MyW.VisibleRange.Cells.Rows.Count - 1)

    If rowI > rowMax Then rowI = rowStart
    Range("A" + CStr(rowI)).Select
    Application.Goto Reference:=ActiveCell, Scroll:=True
    Range("BB" + CStr(rowI)).Select  'Hide selection by selecting a bogus element on the SAME ROW
    ActiveWindow.SmallScroll ToLeft:=100 'Scroll back left to A (BB ==44)

    Application.OnTime Now + TimeValue("00:00:" + CStr(ActiveSheet.Range("AC2").Value)), "!Sheet1.scrollPage"
End Sub

Sub scrollPage()

    If ActiveSheet.Range("K2").Value <> 1 Then Call scrollPageForced

End Sub

Upvotes: 1

Views: 85

Answers (1)

Raserhin
Raserhin

Reputation: 2676

You should know that the VBA code in Excel is not executable in Google Sheets.

To execute code in Google Sheets you need to use the proprietary coding language Apps Script, basically Javascript with built-in classes and functions to use Google products like (Sheets, Docs, Slides, Drive, etc.)

Here you have all the references for the SpreadSheetApp, the class that deals with Google Sheets functions, classes, etc.

To tour specific case you could take take a look at the getActiveRange()

You can look further in how to migrate those applications to Apps Script looking at Google or here at Stack, for example I found this question regarding this.

Upvotes: 1

Related Questions