TM1 Forum

Posted: **Thu Sep 15, 2011 5:10 am**

I have recently downloaded the TM1 Add-In Tools from this thread:
http://www.tm1forum.com/viewtopic.php?f=21&t=2515

I would like to ask a question regarding how I add the "TM1 Tools: Force the data in the clipboard to be sent to string elements" process to a macro.
These are my steps of the task I wish to assign a macro to do:
1. Highlight cell with string value ("Yes")
2. Copy string value ("Yes")
3. Highlight cells I wish to mass copy and paste string value ("Yes") into.
4. Click on Add-Ins Tab
5. Click on "TM1 Tools: Force the data in the clipboard to be sent to string elements".

This process works fine (the tools are great), and I wish to record a macro to do this, so I do not have to keep reselecting the cells everytime. The problem is when I tried to record a macro, the macro only records upto and including step 3. It fails to record step 4 and 5.

Would anyone know of a way to get the macro to record these last two steps? (if you know of a bit of code I could add to the macro so that it does this, this would be great).

For your information I am using Excel 2007 and version 9.5.1 of TM1

Kind Regards,
Magnus

Posted: **Thu Sep 15, 2011 5:28 am**

mags88 wrote:I have recently downloaded the TM1 Add-In Tools from this thread:
http://www.tm1forum.com/viewtopic.php?f=21&t=2515

I am would like to ask a question regarding how I add the "TM1 Tools: Force the data in the clipboard to be sent to string elements" process to a macro.

You can't record this using the macro recorder; you need to roll up your sleeves and do a bit of coding.

First, set a reference to the TM1Tools project. Instructions for doing this will be found in the Help File under the topic TM1 API, heading Referencing TM1 Tools.

After you do that, you can simply call the tool's public methods as if they were local to your own project. For example:

Code: Select all

Sub AutoUpload()
' AutoUpload
' 15-Sep-2011, Alan Kirk
' Example only; no error handling added.
' A reference must be set to TM1 Tools in Tools -> References...
' in the Visual Basic Editor.

    Selection.Copy
    'This is whatever range contains the cells that
    'you want to send to.
    Range("B23:B30").Select
    'This is the method in TM1 Tools that pastes as string.
    CallBulkPasteString
    
End Sub

Posted: **Thu Sep 15, 2011 5:32 am**

Thanks, I'll have a look now and see if this works.

Posted: **Thu Sep 15, 2011 6:55 am**

Thanks works great.

Could anyone help me with my secondary problem?

I wish to apply my macro function (defined above) to multiple rows in excel that have been filtered. Is there a way of editing the macro so that it checks if there is a filter applied to the range of cells that are being affected by the macro?

In summary, after slicing to Excel I wish to have a macro that applies a string value to multiple cells in a column (currently that is no problem and is working fine). If I apply an Excel filter I would like the same macro to paste the string value only to those cells are shown after the filter being applied.

Does anyone know of a bit code that I could use to modify the macro so that it checks if a filter is being applied and only pastes the string value into the cells which are on display because of the filter.

Regards
Magnus

Posted: **Thu Sep 15, 2011 7:31 am**

Code: Select all

Public Function RowIsFilteredOut(ws As Worksheet, ByVal iRowNumber As Integer) As Boolean

    RowIsFilteredOut = ws.Rows(iRowNumber).Hidden

End Function

Posted: **Fri Sep 16, 2011 6:14 am**

I'm very new to VBA and I'm having a bit trouble regarding where to put the bit of code you kindly posted. Do I write it above or below the Sub?

Also if you could offer a bit more detailed advice about your code that would be great.

Do I need to modify your code or can I just put it straight into the VBA module?

Do I need to put the function in excel, I've been searching google earlier today and found a similar code but for sum_total

Code: Select all

Function Sum_Visible_Cells(Cells_To_Sum As Object)
       Application.Volatile
       For Each cell In Cells_To_Sum
           If cell.Rows.Hidden = False Then
               If cell.Columns.Hidden = False Then
                   Total = Total + cell.Value
               End If
           End If
       Next
       Sum_Visible_Cells = Total
   End Function]

This requires me to put =Sum_Visible_Cells(range of cells) in an excel cell before I put this code into the VBA module. Do I need to do the same regarding RowFilteredOut? So I would have to use the function =RowFilteredOut(range of cells).

Thank you for your time

Regards,
Magnus

Posted: **Fri Sep 16, 2011 6:46 am**

mags88 wrote:I'm very new to VBA and I'm having a bit trouble regarding where to put the bit of code you kindly posted. Do I write it above or below the Sub?

Also if you could offer a bit more detailed advice about your code that would be great.

Do I need to modify your code or can I just put it straight into the VBA module?

Do I need to put the function in excel, I've been searching google earlier today and found a similar code but for sum_total
Code: Select all
Function Sum_Visible_Cells(Cells_To_Sum As Object)
       Application.Volatile
       For Each cell In Cells_To_Sum
           If cell.Rows.Hidden = False Then
               If cell.Columns.Hidden = False Then
                   Total = Total + cell.Value
               End If
           End If
       Next
       Sum_Visible_Cells = Total
   End Function]
This requires me to put =Sum_Visible_Cells(range of cells) in an excel cell before I put this code into the VBA module. Do I need to do the same regarding RowFilteredOut? So I would have to use the function =RowFilteredOut(range of cells).

I think you'd be far better off leveraging Excel's calculation muscle via inbuilt worksheet functions rather than trying to iterate through a range. At the risk of stealing Paul Nielsen's thunder (though he says it in the context of SQL Server)... you should always try to "kill the cursor".

You can put the following code straight into a VBA module. Although you can use it as a worksheet function by storing it in a cell (no reason why you would though; you'd just use the Subtotal function directly), you can also just call it from another block of code (as shown in the second example below).

Finally, Try to avoid using Object as an argument type. It's slower, and also more prone to error if the wrong data type is passed.

Code: Select all

Function ReturnVisibleSum(RangeToSum As Excel.Range) As Variant

On Error GoTo ErrorHandler

ReturnVisibleSum = Application.WorksheetFunction.Subtotal( _
 9, RangeToSum)

ExitPoint:
Exit Function

ErrorHandler:

ReturnVisibleSum = CVErr(xlErrValue)

Resume ExitPoint

End Function

Test calling code:

Code: Select all

Sub TestRVS()

MsgBox ReturnVisibleSum(ActiveSheet.Range("A3:B13"))

End Sub

Posted: **Fri Sep 16, 2011 7:43 am**

I think you'd be far better off leveraging Excel's calculation muscle via inbuilt worksheet functions rather than trying to iterate through a range. At the risk of stealing Paul Nielsen's thunder (though he says it in the context of SQL Server)... you should always try to "kill the cursor".

You can put the following code straight into a VBA module. Although you can use it as a worksheet function by storing it in a cell (no reason why you would though; you'd just use the Subtotal function directly), you can also just call it from another block of code (as shown in the second example below).

Finally, Try to avoid using Object as an argument type. It's slower, and also more prone to error if the wrong data type is passed.

Code: Select all

Function ReturnVisibleSum(RangeToSum As Excel.Range) As Variant

On Error GoTo ErrorHandler

ReturnVisibleSum = Application.WorksheetFunction.Subtotal( _
9, RangeToSum)

ExitPoint:
Exit Function

ErrorHandler:

ReturnVisibleSum = CVErr(xlErrValue)

Resume ExitPoint

End Function

Alan - Thank you for your assistance, but I think you misunderstood me. I do not wish to calculate the sum of only the visible cells in my defined column range. I only posted the sum_total code above as an example of a piece of code that affects only the visible cells.

I would like to paste a string value (CallBulkPasteString) into only the visible cells in my defined column range that has some rows hidden. Currently (CallBulkPasteString) pastes to all cells in my defined column range.

Rmackenzie kindly posted this code

Code: Select all

Public Function RowIsFilteredOut(ws As Worksheet, ByVal iRowNumber As Integer) As Boolean

    RowIsFilteredOut = ws.Rows(iRowNumber).Hidden

End Function

It is this code I'm having a bit of trouble with adding to my VBA module.

Regards,
Magnus

Posted: **Fri Sep 16, 2011 8:17 am**

Hi,

After a day of googling and receiving the help from here, I've finally solved my problem. Here is the code I am using.

Code: Select all

Sub CopyYtoAll()
' AutoUpload - CopyYtoAll
' 15-Sep-2011, Alan Kirk
' Example only; no error handling added.
' A reference must be set to TM1 Tools in Tools -> References...
' in the Visual Basic Editor.
    
    Range("K2").Select
    Selection.Copy
    'This is whatever range contains the cells that
    'you want to send to.
    
    Range("K7:K18").SpecialCells(xlCellTypeVisible, xlTextValues).Select
    'added expression.SpecialCells(Type,Value).
    'This is the method in TM1 Tools that pastes as string.
    CallBulkPasteString
    
End Sub

Thanks Alan for your initial code and your continual help.

Regards,
Magnus

TM1 Forum

TM1 Add In Tools

TM1 Add In Tools

Re: TM1 Add In Tools

Re: TM1 Add In Tools

Re: TM1 Add In Tools

Re: TM1 Add In Tools

Re: TM1 Add In Tools

Re: TM1 Add In Tools

Re: TM1 Add In Tools

Re: TM1 Add In Tools