Posts by =RSI

    Re: Removing reference to the VBIDE

    Thanks Will, I think you're right. I was trying to avoid that as the workbook has charts and controls that like to reposition/reformat themselves when they are copied over.

    It's a shame as the code that removes itself works fine - it's just that :censored: reference to the VBIDE.


    Ive got a spreadsheet that is essentially calculation based, reporting on a live view of a database. I have a small bit of code in it that allows an offline snapshot copy of itself to be saved so that it can be distributed.

    The problem I have is that I want to remove the code from this snapshot to prevent it triggering Excels virus protection. In itself that's not a problem, the difficulty I have is that unless the reference to the VBIDE is removed it still triggers the virus protection.

    In itself, the code to remove the reference works fine, but when it is combined witht the code that removes itself I get a compile error.

    Any ideas?.

    Re: Range().Value = Range().Value causing issues with currency formats

    Tried forcing the number formats with no joy. It seems to only occur when the source data is in currency or accounting format with just a plain '£' prefix. Any of the '$' prefixes or even the prefix labeled '£ English (United Kingdom)' do not produce this error.

    I've attached an example of the result for reference in case any one runs into this again.

    Re: Range().Value = Range().Value causing issues with currency formats

    Thanks, I hadn't thought of forcing the number format. I'll try it on Monday and see what happens.

    I'm certain that there was no formatting, I even restarted Excel and replicated just the effect in a new, blank workbook. I'm not conserned about what the format is, so long as it's a number not text.

    Re: Range().Value = Range().Value causing issues with currency formats

    Hi batman,

    Yes they were all 'general' format before they received the data. So they shouldn't have received any formatting from that code. I tried replacing the sheet (hence Output2) but still the same...

    I've just tried to replicate the problem at home on XL2000 (have 97 still at work!) and do not get the same results. I guess it must be to do with the NT environment.

    Thanks for your help!

    Now this is weird...

    I have 2 sheets. The first sheet contains calculations with results in the range AL3:BO3. Each time it calculates, the results change as its formulae contain randon numbers. The second sheet is blank.

    I have a small peice of code that recalculates the workbook a few times and puts the result of the calculation in the first sheet in successive rows in the second:

    Sub MontyCarlo(Itterations As Integer)
        Dim x As Integer
        For x = 1 To Itterations
            Output2.Range(Cells(x, 1), Cells(x, 30)).Value = MC.Range("AL3:BO3").Value
        Next x
    End Sub

    Now here's the weird bit: The results in the first sheet are in currency format (GBP). When the code puts them in the second sheet they become text, prefixed as USD currency with a trailing space!

    After trying lots of alternatives I found that if I changed the format of the results to 'General' the code worked fine :confused:

    Although I've now got it working, can anyone explain why .Value = .Value gave such strange results? Could it be to do with the over engineered build of NT I am on (I cannot change the styles for example)?


    Re: Code creates its own If/For statements

    If you believe you really do need to dynamically generate your code you could try something like this:

    Create a new module in the workbook that contains the code you are running and call it 'DynamicModule'. Enter the following code in this module:

    Sub DynamicSub()
    End Sub

    Then in your original code use something like this:


    I had need to develop methods for manipulating large arrays of data in a particular way and settled on using Bezier Surfaces. After looking for a way to view these easily, I found Andy Pope's 3D x,y scatter graph through this forum. Having adapted it a little to display these surfaces, I though I'd post up an example for you to have a play about with.

    I've put a few example 'control point' sets in the spreadsheet but the possibilities are infinite; just put in the x,y,z co-ordinates of 25 control points and see what surfaces appear!

    This is by no means a robust application of any sort, just a bit of fun I thought I'd share.

    Many thanks to Andy Pope for the 3D Scatter chart!


    The total number of ways of arranging n objects is given by n! (factorial). Use the =FACT(n) formula or the equivalent to using the =COMBIN would be to use =PERMUT(n,n). Each would give you 5040 for 7 objects.

    Does anyone know how to reference the new workbook that is created when you use the sheets(sheet1).copy without a before or after qualifyer?

    It looks like it is just Book(n).xls where n is an incriment on the last new workbook created. I know I could compare the list of open workbooks before and after this event but that seems a bit messy. Is there a way I can assign the new book to an object variable? (have tried Set ObjectVar = sheets(sheet1).copy - no luck :( )


    For 'PM' read Private Message (called U2U messages here I think). There is a button labeled 'pm' at the bottom of each post, next to 'profile' and 'find'


    Have you tried using array formulae? for example instead of:




    If you are not familiar with array formulae, just enter them without the { and } and hold SHIFT + CTRL when you press ENTER. The curly brackets will then be added automatically.


    I have found the best way to do this is to set up the application you want excel to contol as an object in excel and run the macro you have recorded from within VBA. Make sure in the VBA editor, under tools/references, 'OLE Automation' and if available 'BusinessObjects' or similar is selected. This will alow you to run business objects code from VBA. You can then set up code something like:

    Sub run_BO
    Dim Session1 as Object
    Set BO as CreateObject([here you need the registered application name for business objects - get it from the task manager when it is running])
    Set session1 as BO.[now you're into the code business objects uses, see the help file or macro you've recored but it will be something like 'NewSessionFile' or 'Openfile']
    [set up any other objects the macro you recorded needs to run]

    [set up any attributes you need eg With Session1
    .Port = Port
    End with]

    [continue translating the macro you recorded into VBA]

    [VBA funmctionality eg copy to spreadsheet and process]

    End sub

    More often than not you can copy the data you've extracted onto the clipboard and then past it into a spreadsheet, but watch out Excel can trip over itself here and sometimes you'll have to parse it item by item.

    Good luck!

    Assuming the first row is 2 and last column is ‘D’ try:

    Sub delete_rows()
    Const column = 4
    Dim rownum As Integer
    rownum = 2
    Do While Cells(rownum, column).Text <> ""
    If Cells(rownum, column).Text = "#N/A" Then
    rownum = rownum - 1
    End If
    rownum = rownum + 1
    End Sub