I’ve been updating several books for the Office 2016 release.
These forecasting functions are no doubt included as a necessary part of the new Forecast Sheet functionality.
You’ll find these functions in the Statistical formula category.
These poor functions have been demoted to the dreaded Compatibility category; meaning because there are better functions for accomplishing their respective tasks, the functions listed below will be found under Compatibility on the Formulas tab. MATH offers a more controlled method of rounding up to the nearest integer or multiple of significance FLOOR: FLOOR.
Optimally, this would work like the FREQUENCY function, that returns an array (that can then be accessed via the INDEX function).
I know both Pivot Tables and the Advanced Filter can do this, but both don't update dynamically.
I'm just trying to get VBA to update an OLEDB connection string. Connection = String To Array(Connection String) End With Active Workbook. Refresh End Sub Connection String = = "Provider=SLXOLEDB.1; Data Source=SERVER; Initial Catalog=DATABASE" _ & "; User ID=" & Username & "; Password=" & Password & _ "; Persist Security Info=True; Extended Properties=" _ & Chr(34) & "PORT=1706; LOG=ON; CASEINSENSITIVEFIND=ON; INCLUDECALCFIELDS=ON;" & Chr(34) I suggest that you carefully check each of the properties in the locals window to make sure that they actually exist - I don't know the syntax by heart.
When I step through the code, I don't get any errors but the connection refresh fails and when I examine the connection string in the UI, it's obvious that my code has not changed it at all (hence the refresh failure). Here is the code: Sub Update Query Connection String(Connection String As String) With Active Workbook. Array(Connection String) seem like weird syntax, considering the fact that Connection String is a string. The array is used to stop strings over 255 characters being truncated but I don't think I did this right.
I tried it without the Array() but get a runtime error. I'll update the code in my question to what I've done now.
The UI still shows no changes to the Connection String (right or wrong). Connection = Connection String End Sub Sub Update Connection() Dim Server Name As String Dim Server Name Raw As String Dim Cube Name As String Dim Cube Name Raw As String Dim Connection String As String Server Name Raw = Active Workbook. Visible Slicer Items List(1) Server Name = Replace(Split(Server Name Raw, "[")(3), "]", "") Cube Name Raw = Active Workbook. Visible Slicer Items List(1) Cube Name = Replace(Split(Cube Name Raw, "[")(3), "]", "") If Cube Name = "All" Or Server Name = "All" Then Msg Box "Please Select One Cube and Server Name", vb OKOnly, "Slicer Info" Else Connection String = Get Connection String(Server Name, Cube Name) Update All Query Table Connections Connection String, Cube Name End If End Sub Function Get Connection String(Server Name As String, Cube Name As String) Dim result As String result = "OLEDB; Provider=MSOLAP.5; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error; Update Isolation Level=2" '"OLEDB; Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; Use Procedure for Prepare=1; Auto Translate=True; Packet Size=4096; Use Encryption for Data=False; Tag with column collation when possible=False" Get Connection String = result End Function Sub Update All Query Table Connections(Connection String As String, Cube Name As String) Dim cn As Workbook Connection Dim oledb Cn As OLEDBConnection Dim Count As Integer, i As Integer Dim DBName As String DBName = "Initial Catalog=" + Cube Name Count = 0 For Each cn In This Workbook. Name = "This Workbook Data Model" Then Exit For End If o Tmp = Split(cn. Connection, ";") For i = 0 To UBound(o Tmp) - 1 If In Str(1, o Tmp(i), DBName, vb Text Compare) = 1 Then Set oledb Cn = cn.