Greetings Excelers!
I'm running into an issue where all of my in-cell functions run whenever one is changed.
I've got a workbook that I use to pre-populate cells with the values of their corresponding activity/role. The values lives on another sheet.
I'm trying to make it so that the default values are held in excel memory, to avoid re-running them if they haven't changed, and to keep the workbook nimble, calculate only the corresponding functions to be triggered, not all of them.
I feel like I'm missing something simple...
Using Office 365 ProPlus, Windows 7 Professional
Example:
If the activity is "Plan", and the role is "Technical Consultant", it should populate the corresponding value of Plan/Technical Consultant from the "Roles" sheet into Cell B2 (10, in the example below).
<tbody>
</tbody>
To do this, I've got a user defined function (UDF) called "GetWbsActivityTime" in the worksheet cell that passes the name of sTopLevel ("Plan") and the sActivity ("Technical Consultant").
<code>B2=GetWbsActivityTime(sTopLevel As String, sActivity As String)</code>
B2=GetWbsActivityTime(B1,A2)
B2=GetWbsActivityTime("Plan","Technical Consultant")
On the sheet "Role Defaults", I've defined a table of my defaults.
<tbody>
</tbody>
<code>
Public Function GetWbsActivityTime(sTopLevel As String, sActivity As String) As Variant
End Function
</code>
GetWBS() checks to see if the Public oDictWbs object is populated
<code>
Public Function GetWBS() As Object
</code>
The Function RefreshWBS builds the two-dimensional array of the "Role Defaults" table
<code>Public Function RefreshWBS()
Dim sDefault As Worksheet
Dim rTopLevels As Range
Dim rActivities As Range
Dim rIterator As Range
Dim rInnerIter As Range
Set oDictWbs = Nothing
'Both variables below establish the range that stores the fixed info (the default worksheet)
'Instead of hard coding in the range, create your own logic based on your needs and rules
Set sDefault = Sheets("Role Defaults")
Set rTopLevels = sDefault.Range("RoleDefaultsTable_Head")
Set rActivities = sDefault.Range("RoleDefaultsTable_FirstCol")
Set oDictWbs = CreateObject("Scripting.Dictionary")
End Function
</code>
I'm running into an issue where all of my in-cell functions run whenever one is changed.
I've got a workbook that I use to pre-populate cells with the values of their corresponding activity/role. The values lives on another sheet.
I'm trying to make it so that the default values are held in excel memory, to avoid re-running them if they haven't changed, and to keep the workbook nimble, calculate only the corresponding functions to be triggered, not all of them.
I feel like I'm missing something simple...
Using Office 365 ProPlus, Windows 7 Professional
Example:
If the activity is "Plan", and the role is "Technical Consultant", it should populate the corresponding value of Plan/Technical Consultant from the "Roles" sheet into Cell B2 (10, in the example below).
| A | B | |
| 1 | Plan | |
| 2 | Technical Consultant | 10 |
| 3 | Business Analyst | 8 |
<tbody>
</tbody>
To do this, I've got a user defined function (UDF) called "GetWbsActivityTime" in the worksheet cell that passes the name of sTopLevel ("Plan") and the sActivity ("Technical Consultant").
<code>B2=GetWbsActivityTime(sTopLevel As String, sActivity As String)</code>
B2=GetWbsActivityTime(B1,A2)
B2=GetWbsActivityTime("Plan","Technical Consultant")
On the sheet "Role Defaults", I've defined a table of my defaults.
| A | B | C | |
| 1 | Plan | Test | |
| 2 | Technical Consultant | 10 | 20 |
| 3 | Business Analyst | 8 | 12 |
<tbody>
</tbody>
<code>
Public Function GetWbsActivityTime(sTopLevel As String, sActivity As String) As Variant
Dim oDict As Object
Set oDict = GetWBS()
GetWbsActivityTime = oDict(sTopLevel)(sActivity)
End Function
</code>
GetWBS() checks to see if the Public oDictWbs object is populated
<code>
Public Function GetWBS() As Object
If Not oDictWbs Is Nothing Then
Set GetWBS = oDictWbs
Exit Function
End If
Set GetWBS = RefreshWBS()
End Function</code>
The Function RefreshWBS builds the two-dimensional array of the "Role Defaults" table
<code>Public Function RefreshWBS()
Dim sDefault As Worksheet
Dim rTopLevels As Range
Dim rActivities As Range
Dim rIterator As Range
Dim rInnerIter As Range
Set oDictWbs = Nothing
'Both variables below establish the range that stores the fixed info (the default worksheet)
'Instead of hard coding in the range, create your own logic based on your needs and rules
Set sDefault = Sheets("Role Defaults")
Set rTopLevels = sDefault.Range("RoleDefaultsTable_Head")
Set rActivities = sDefault.Range("RoleDefaultsTable_FirstCol")
Set oDictWbs = CreateObject("Scripting.Dictionary")
For Each rIterator In rTopLevels
If Not oDictWbs.exists(rIterator.Value) Then
Set oDictWbs(rIterator.Value) = CreateObject("Scripting.Dictionary")
End If
For Each rInnerIter In rActivities
If Not oDictWbs(rIterator.Value).exists(rInnerIter.Value) Then
oDictWbs(rIterator.Value)(rInnerIter.Value) = sDefault.Cells(rInnerIter.Row, rIterator.Column)
End If
Next rInnerIter
Next rIterator
Set RefreshWBS = oDictWbs
End Function
</code>
