איך ליצור משתנה גלובלי ב Google sheets

Y.Excel.Access

אני מנסה ליצור משתנה גלובלי בפונקציה מותאמת אישית (UDF) בשיטס.
האפשרויות שמצאתי הם להשתמש ב CacheService כמו כאן:

function GetOfVarGlobal(Key = "_") {
  var ValueGlobal = CacheService.getScriptCache();
  return ValueGlobal.get(Key);
}

אבל זה רק STRING ואני צריך שיאחסן גם מערכים.

מה הצורך בדיוק?
בקידוד בSHEETS ואקסל יש הרבה פעמים צורך להקצות עמודות לערכי ביניים. אני מנסה להכניס אופציה של יצירת משתנים וכך להפוך את הגליון האלקטרוני ליותר דומה לשפת תכנות.
מה זה מועיל?

סדר, בעיקר. גם אפשרות לכתוב הערות.
אופטימיזציה של מקום אחסון. לדוגמה בשיטס אפשר רק עד 2 מליון תאים, שזה עלול להגמר בחישובים מסובכים.

אשמח לדעת

האם זה יעיל?
והאם זה אפשרי.

תודה רבה.

מצרף כאן קוד שלי באקסל (באמצע פיתוח - עדיין לא עובד, אבל אפשר להבין את הכיוון. גם אני רוצה להחליף את הCollection במערכים עם ReDim).
אשמח להערות.

Option Explicit

Public Function SubInFunction(ParamArray ReturnOnlyLest() As Variant)
    Dim temp As Variant
    For Each temp In ReturnOnlyLest
        SubInFunction = temp
    Next
    temp = StaticVars("Remove all temp")
End Function

Public Function SetVar(Value As Variant, Optional Name As String = "") As Variant
' לכתוב למשתנה סטטי = ליצור משתנה או לערוך אותו
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    SetVar = StaticVars("Set", Name, Value)
End Function

Public Function GetVar(Optional Name As String = "") As Variant
' לקרוא ממשתנה סטטי = להחזיר את הערך שלו
    GetVar = StaticVars("Get", Name)
End Function

Public Function RemoveVar(Optional Name As String = "") As Variant
' למחוק משתנה סטטי
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    If Name <> "All" Then
        RemoveVar = StaticVars("Remove", Name)
    Else
        RemoveVar = StaticVars("Remove all")
    End If
End Function

Public Function SetTempVar(Value As Variant, Optional Name As String = "") As Variant
' לכתוב למשתנה סטטי = ליצור משתנה או לערוך אותו
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    SetTempVar = StaticVars("Set temp", Name, Value)
End Function

Public Function GetTempVar(Optional Name As String = "") As Variant
' לקרוא ממשתנה סטטי = להחזיר את הערך שלו
    GetTempVar = StaticVars("Get temp", Name)
End Function

Public Function RemoveTempVar(Optional Name As String = "") As Variant
' למחוק משתנה סטטי
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    If Name <> "All" Then
        RemoveTempVar = StaticVars("Remove temp", Name)
    Else
        RemoveTempVar = StaticVars("Remove all temp")
    End If
End Function


Private Function StaticVars(Optional ActType As String = "Get", Optional VarName As String = "", Optional Value_ForSetOrForRemove As Variant = "") As Variant
' האחסון וכל הפעולות על משתנים סטטיים
' פונקציה זו היא לפעמים משמשת כשגרה ללא החזרת ערך (רק כך אפשר לעבוד בשפה זו עם משתנים סטטיים)
Static Vars As New Collection
Static VarsName As New Collection
Static TempVars As New Collection
Static TempVarsName As New Collection

Select Case (ActType)
    Case "Get"
        StaticVars = GetValueInCollectionOfName(VarName, Vars, VarsName)
    Case "Set"
        SetValueInCollectionOfName Value_ForSetOrForRemove, VarName, Vars, VarsName
        StaticVars = 0
    Case "Get temp"
        StaticVars = GetValueInCollectionOfName(VarName, TempVars, TempVarsName)
    Case "Set temp"
        SetValueInCollectionOfName Value_ForSetOrForRemove, VarName, TempVars, TempVarsName
        StaticVars = 0
    Case "Remove"
        RemoveValueInCollectionOfName VarName, Vars, VarsName
        StaticVars = 0
    Case "Remove temp"
        RemoveValueInCollectionOfName VarName, TempVars, TempVarsName
        StaticVars = 0
    Case "Remove all"
        RemoveAllValueInCollection Vars
        RemoveAllValueInCollection VarsName
        StaticVars = 0
    Case "Remove all temp"
        RemoveAllValueInCollection TempVars
        RemoveAllValueInCollection TempVarsName
        StaticVars = 0
End Select

End Function

Private Function GetValueInCollectionOfName(NameValue As String, CollectionValues As Collection, CollectionNames As Collection) As Variant
    Dim i As Integer
    For i = 1 To CollectionNames.Count
        If CollectionNames(i) = NameValue Then GetValueInCollectionOfName = CollectionValues.Item(i): Exit Function
    Next i
    GetValueInCollectionOfName = 0
End Function

Private Sub SetValueInCollectionOfName(Value As Variant, NameValue As String, CollectionValues As Collection, CollectionNames As Collection)
    Dim i As Integer
    For i = 1 To CollectionNames.Count
        If CollectionNames(i) = NameValue Then collection_ApdateValue CollectionValues, i, Value: Exit Sub
    Next i
    
    CollectionValues.Add Value
    CollectionNames.Add NameValue
End Sub

Private Sub RemoveValueInCollectionOfName(NameValue As String, CollectionValues As Collection, CollectionNames As Collection)
    Dim i As Integer
    For i = 1 To CollectionNames.Count
        If CollectionNames(i) = NameValue Then CollectionValues.Remove i: CollectionNames.Remove i: Exit Sub
    Next i
End Sub

Private Sub RemoveAllValueInCollection(MyCollection As Collection)
    Dim i As Integer
    For i = 1 To MyCollection.Count
        MyCollection.Remove i
    Next i
End Sub

Private Sub collection_ApdateValue(MyCollection As Collection, Index As Integer, NewValue As Variant)
    If MyCollection.Count = Index Then
        MyCollection.Add NewValue
        MyCollection.Remove Index
    Else
        MyCollection.Add NewValue, before:=Index + 1
        MyCollection.Remove Index
    End If
End Sub

dovid

@Y-Excel-Access
אני מבין הרבה פחות ממך כנראה באקסל, וקשה לי ללמוד את הצורך.
אני סתם זורק הערות שמקוה שיש להם בנותן טעם:
גלובלי במובן של אקסל זה לכל אורך חיי התוכנה, בעצם כל משתנה שמוגדר מחוץ לפונקציה הוא כזה, לא?
בנוסף אני חושב המחלקה CacheService היא לקאש, כלומר לנסות להחזיק באויר דברים לפרק זמן מסויים כדי לא לצרוך אותם שוב ושוב מאה פעמים תוך שלוש שניות. אבל אחרי פרק זמן כל שהוא (אין לי מושג כמה) הם נעלמים.
זה מה שאתה צריך? אולי אתה צריך את זה PropertiesService.

OdedDvir

@Y-Excel-Access הצלחת לסקרן אותי, אבל חוץ מזה, לא הבנתי את המטרה שאליה אתה חותר.

dovid

@Y-Excel-Access בשולי הדברים, לפי ההתרשמות שלי אתה מוכשר מידי בשביל להישאר בגבולות האקסל בלבד, אני מניח שאתה פותר כמעט הכל בעזרתו (ונראה לי שהצורך של פה הוא פועל יוצא של מקרים כאלו), אבל אתה תגלה עולמות ותחסוך מאמצים אם תשלב בארגז כלים שלך טכנולוגיות אחרות.

Y.Excel.Access

פוסט זה נמחק!

Y.Excel.Access

הקוד הסופי באקסל:
שדרוג אקסל - הוספת משתנים.xlsm
זה קובץ אקסל עם הקוד. (סופי עד השיפור הבא...)

כמו שרואים כאן, הצבתי דוגמה שאני מחפש את המקסימום רק אם המינימום גדול מ 1.
אז לולא המשתנים הייתי אמור לחשב את המינימום פעמיים - אחד ל IF והשני למקרה שהוא באמת לא גדול מאחד, וכשהכלתי אותו בתוך משתנה חסכתי את זה.
כמובן זו רק דוגמה, כי החישובים יכולים להיות הרבה יותר מסובכים.
\אני מעתיק כאן את הקוד ישירות:
מודול קלאס בשם ArrayAsList

Option Explicit
Option Base 1
' אמנם קבעתי כאן את אופציית ברירת המחדל של המערכים להתחיל ב 1, אך בפועל כשאתחלתי מחדש
' את המערכים המקומיים אתחלתי מ 0 כדי למנוע שגיאה בעת מחיקת הערך האחרון או מחיקת כל ערכי המערך
' אך באמת ההתייחסות המעשית למערך היא עדיין כמותחל ב 1 = לגבי הלולאות הרצות עליו
' כך יוצא שהערך 0 הוא ריק וחסר משמעות לקלאס זה

Private MyArray() As Variant
Private KeyMyArray() As String
Private LengthMyArray As Integer
Private Const LimitMaxCountValueInMyArray = 72

Public Sub Add(Value As Variant, Optional Key As String = "")

    Dim i As Integer
    For i = 1 To LengthMyArray
        If KeyMyArray(i) = Key Then MyArray(i) = Value: Exit Sub
    Next i
    
    If LengthMyArray < LimitMaxCountValueInMyArray Then
        ChangeLengthToMyArray 1
        MyArray(LengthMyArray) = Value
        KeyMyArray(LengthMyArray) = Key
    End If
    
End Sub

Public Function Item(Optional Key As String = "")
    Dim i As Integer
    For i = 1 To LengthMyArray
        If KeyMyArray(i) = Key Then Item = MyArray(i): Exit Function
    Next i
End Function

Public Sub Remove(Optional Key As String = "")
    Dim i As Integer
    For i = 1 To LengthMyArray
        If KeyMyArray(i) = Key Then
            Dim j As Integer
            For j = LengthMyArray To i + 1 Step -1
                MyArray(j - 1) = MyArray(j)
                KeyMyArray(j - 1) = KeyMyArray(j)
            Next j
            ChangeLengthToMyArray -1
            Exit Sub
        End If
    Next i
End Sub

Public Sub RemoveAll()
    LengthMyArray = 0
    ReDim MyArray(0 To LengthMyArray)
    ReDim KeyMyArray(0 To LengthMyArray)
End Sub

Public Property Get Length() As Variant
    Length = LengthMyArray
End Property

Private Sub ChangeLengthToMyArray(Optional StepForChange As Integer = 1)
    LengthMyArray = LengthMyArray + StepForChange
    ReDim Preserve MyArray(0 To LengthMyArray)
    ReDim Preserve KeyMyArray(0 To LengthMyArray)
End Sub

והפונקציות עצמם במודול רגיל:

Option Explicit
Option Base 1

Public Function SubInFunction(ParamArray ReturnOnlyLest() As Variant)
    Dim temp As Variant
    For Each temp In ReturnOnlyLest
        SubInFunction = temp
    Next
    temp = StaticVars("Remove all temp")
End Function

Public Function SetVar(ByRef Value As Variant, Optional ByRef VarName As String = "") As Variant
' לכתוב למשתנה סטטי = ליצור משתנה או לערוך אותו
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    SetVar = StaticVars("Set", VarName, Value)
End Function

Public Function GetVar(Optional ByRef VarName As String = "") As Variant
' לקרוא ממשתנה סטטי = להחזיר את הערך שלו
    GetVar = StaticVars("Get", VarName)
End Function

Public Function RemoveVar(Optional ByRef VarName As String = "") As Variant
' למחוק משתנה סטטי
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    If VarName <> "All" Then
        RemoveVar = StaticVars("Remove", VarName)
    Else
        RemoveVar = StaticVars("Remove all")
    End If
End Function

Public Function SetTempVar(ByRef Value As Variant, Optional ByRef VarName As String = "") As Variant
' לכתוב למשתנה סטטי = ליצור משתנה או לערוך אותו
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    SetTempVar = StaticVars("Set temp", VarName, Value)
End Function

Public Function GetTempVar(Optional ByRef VarName As String = "") As Variant
' לקרוא ממשתנה סטטי = להחזיר את הערך שלו
    GetTempVar = StaticVars("Get temp", VarName)
End Function

Public Function RemoveTempVar(Optional ByRef VarName As String = "") As Variant
' למחוק משתנה סטטי
' פעולה בלבד - נכתב כפונקציה רק כדי שיהיה אפשר להפעיל את זה דרך פונקציות אקסל
    If VarName <> "All" Then
        RemoveTempVar = StaticVars("Remove temp", VarName)
    Else
        RemoveTempVar = StaticVars("Remove all temp")
    End If
End Function


Private Function StaticVars(Optional ByRef ActType As String = "Get", Optional ByRef VarName As String = "", Optional ByRef Value_ForSetOrForRemove As Variant = "") As Variant
' האחסון וכל הפעולות על משתנים סטטיים
' פונקציה זו היא לפעמים משמשת כשגרה ללא החזרת ערך (רק כך אפשר לעבוד בשפה זו עם משתנים סטטיים)
Static Vars As New ArrayAsList
Static TempVars As New ArrayAsList
Select Case (ActType)
    Case "Get"
        StaticVars = Vars.Item(VarName)
    Case "Set"
        Vars.Add Value_ForSetOrForRemove, VarName
        StaticVars = 0
    Case "Remove"
        Vars.Remove VarName
        StaticVars = 0
    Case "Remove all"
        Vars.RemoveAll
        StaticVars = 0
    Case "Get temp"
        StaticVars = TempVars.Item(VarName)
    Case "Set temp"
        TempVars.Add Value_ForSetOrForRemove, VarName
        StaticVars = 0
    Case "Remove temp"
        TempVars.Remove VarName
        StaticVars = 0
    Case "Remove all temp"
        TempVars.RemoveAll
        StaticVars = 0
End Select

End Function

(בכוונה אני עובר על המערך של PARAM ARRAY ולא מנסה להשיג את האורך שלו עם UBOUND ו LBOUND כי יש גרסאות באקסל שאין להם את הפונקציות האלו
גם - משום מה לא עובד על המערך בקלאס = NULL או = NOTHING אז ניסיתי להסתדר...
הפרמטרים של הפונקציות כולם BYREF בשביל המהירות בלבד. - אין טעם ליצור עוד משתנה (ואני יודע בשNET הקמפול מחשב את זה לבד... אבל בVBA כמדומני שלא.))

Y.Excel.Access

@dovid אמר באיך ליצור משתנה גלובלי ב Google sheets:

אולי אתה צריך את זה PropertiesService.

כן, זה נשמע בכיוון, תודה רבה.
אשמח לעוד קישור / חומר על זה למי שיש, עדיין לא ממש הבנתי איך לעבוד עם זה בפועל.

תחומים - פורום חרדי מקצועי

איך ליצור משתנה גלובלי ב Google sheets