Irritating scaling / DBRW issue when using thousands format.

[Steve Rowe]

Hello!

I’ve a worksheet where the number format is used to scale the numbers into thousands.
# ##0,_);(# ##0,);-

I’ve also a DBRW where I want the user to input some values and I need to have these numbers scaled on the way into TM1. So if I type 1 in a cell I must have a thousand in the cell. At first thought I was thinking a bit of VBA or even simpler changing the options would get this done pretty easy.

Think again….

I’ve tried changing the options to fixed decimal places to -3, this works in Excel with the TM1 add in but doesn’t when you try and do it on a DBRW.

Next attempt was a very simple piece of VBA that just multiplied the value that had just been entered by 1,000.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
'check to see if we are on an input line where we need to apply multiplier to the input values
Application.EnableEvents = False
If Target.Offset(0, -Target.Column + 1) = "Mvt - Override" Then
   Target.value = Target.value * 1000
End If
Application.EnableEvents = True
End Sub

I wasn’t expecting much from this and didn’t get much. The cell value was the correct amount but the DBRW gets overwritten.

My next attempt was to unpick the DBRW in the cell and use this to run a DBS in the VBA using the change event.

Code: Select all

Public cellFormula As String
Type formRef
    address As String
    value As String
End Type

‘sheet event macros

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'required as while the DBRW is "executing" Target.formula=Target.value
'which means I have to capture the DBRW formula before the edit is made.
    cellFormula = Target.Formula
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'check to see if we are on an input line where we need to apply multiplier to the input values
Application.EnableEvents = False
If Target.Offset(0, -Target.Column + 1) = "Mvt - Override" Then
   Call InSheetMacros.AmendSend(Target)
End If
Application.EnableEvents = True
End Sub

‘main macro

Code: Select all

Sub AmendSend(c As Range)
'hard coded to work with a DBRW with 7 arguements inclduing the cube ref
Dim argue(7) As formRef, lastChar As Integer, ixArray As Integer, refCount As Integer
Dim ans As Variant, valueToSend As Double

Const checkChar = ","
Const firstBit = "=DBRW("
'scale the input amount
valueToSend = c.value * 1000

'establish the cell references in the formula
argue(1).address = Mid(cellFormula, Len(firstBit) + 1, InStr(Len(firstBit), cellFormula, checkChar) - Len(firstBit) - 1)
lastChar = InStr(Len(firstBit), cellFormula, checkChar)

For ixArray = 2 To 7
    If ixArray = 7 Then
    'special case for the last bit of the formula that doesn't have a ","
        refCount = Len(cellFormula)
    Else
        refCount = InStr(lastChar + 1, cellFormula, checkChar)
    End If
    argue(ixArray).address = Mid(cellFormula, lastChar + 1, refCount - lastChar - 1)
    lastChar = refCount
Next ixArray

'establish the range values
For ixArray = 1 To 7
    argue(ixArray).value = ActiveSheet.Range(argue(ixArray).address)
Next ixArray

'send the value in
ans = Application.Run("DBS", valueToSend, argue(1).value, argue(2).value, argue(3).value, argue(4).value, argue(5).value, argue(6).value, argue(7).value)


End Sub

Now it’s all getting a bit more complicated than I would want and still doesn’t work. The DBS formula in the Application.Run at the end of the macro does not appear to work. I still see the unscaled amount ending up in the cube, and I can’t see the scaled value appearing in the transaction log.

Now I know from recent experience that working with Excel/VBA events seems to be getting much harder than it used to be when TM1 is thrown into the mix. I seem to remember that in good old 845 and 714 you could do pretty much as you pleased, that could be rose coloured specs though.

Anyway to get to the point, I was just wondering is anyone has tried to do this and succeeded? I’m going to rethink my approach to this so it’s no drama, just curious if the forum thinks that the DBRW should cope with the formats being set to thousands and fixed decimal being set -3 and correctly load 1,000 even if only 1 is input a cell.
Cheers

[John Hobson]

Fascinating - I am having an identical issue with "another product" and yes, I think you are right that previous versions used to allow this kind of interception as I used exactly this methodology to allow a value to be copied over a range of DBR cells in 7 and I think in 8. Not sure if it worked in 9 as I never had to use it with that version.

I think that the calculation and change events along with the undo seem particularly nervy when something like TM1 is loaded, and the behaviour is not consistent between versions. I seem to recall a MAJOR change in the calculation cycle some time in the V8 years - anyone who has tried a search and replace in Excel and watched the screen flash a zillion times like a demented disco strobe will remember the change

This is one obvious problem with the old Applix attitude that "we can ignore that requirement as you can probably use VBA to get round it" - spreading, copying, kicking off TI processes etc.

I recall Jason ("Flash") Mullins demoing TI processes being kicked off by a VBA button at a user day and when I asked for the function it turned out it was done with VBA / API code by someone in Germany and was totally unsupported. They really didn't get why I was unhappy in providing this to a client to use in real life, or why I thought that they should provide a proper supported function to allow this. AFAIK there still isn't one. (Apologies for the thread creep)

[Steve Rowe]

I found part of the problem with this code.

Code: Select all

ans = Application.Run("DBS", valueToSend, argue(1).value, argue(2).value, argue(3).value, argue(4).value, argue(5).value, argue(6).value, argue(7).value)

doesn't work and just returns the value in the cube but

Code: Select all

ans = Application.Run("DBSW", valueToSend, argue(1).value, argue(2).value, argue(3).value, argue(4).value, argue(5).value, argue(6).value, argue(7).value)

does send the required value into the system. So for some buggy reason when you use VBA you need to use DBSW not DBS which is odd, annoyingly I seem to remember finding this before and had forgotten about it.

Now all I need to do is stop the DBRW itself executing and overwriting the value I have sent in with the DBSW......
Cheers

[Wim Gielis]

Hi there

Add one step in between to set the value multiplied with 1000 in the cell, after which you again put the DBRW in the cell:

Code: Select all

    ans = Application.Run("DBSW", valueToSend, argue(1).value, argue(2).value, argue(3).value, argue(4).value, argue(5).value, argue(6).value, argue(7).value)
    c.value = ans
    c.Formula = cellFormula

Wim

[Wim Gielis]

Hello again

Here's a piece of generic code.

If you select a cell with a DBRW, the code will transform it into a DBSW and send it to TM1.

In the code, you can transform the cell value to any other value that you send to the DB. Here, I multiply with 1000.

Code: Select all

Sub FromDBRWtoDBSW()
    
    'take out the part with the cell references
    arr = Split(Split(Split(ActiveCell.Formula, "(")(1), ")")(0), ",")
    
    'convert to string with actual cell value
    For i = 0 To UBound(arr)
        arr(i) = ",""" & Range(arr(i)).Text & """"
    Next
    
    'bringing it all together
    sFormula = "=DBSW(" & ActiveCell.value * 1000 & Join(arr, "") & ")"
    
    'send to TM1
    MsgBox Evaluate(sFormula) & " is sent to the cube"
    
End Sub

Wim

[Steve Rowe]

Great Wim, thanks for the help that seems to have stopped the DBRW from executing. Cheers