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.

Hi,

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?.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Sub triggered by the Before Close event that removes the command button and
    'removes the code if saving as output file
    Call RemoveCommandBtn
    If RemoveMyCode = True Then
        Call RemoveCode(ThisWorkbook.VBProject.Name)
        Call DeleteRefLibraryVBIDE
    End If
End Sub


Private Sub RemoveCode(ProjectName As String)
    'Sub that parses through all the loaded code modules and removes them if they
    'are in the Report V.xls workbook
    Dim Project As VBProject
    Dim Component As VBComponent
    Dim CodeWindow As Variant
    Dim line As Integer
    Dim linecount As Integer
    For Each Project In Application.VBE.VBProjects
        If Project.Name = ProjectName Then
            For Each Component In Project.VBComponents
                If Not Component.CodeModule.CountOfLines = 0 Then
                    Let linecount = Component.CodeModule.CountOfLines
                    For line = 1 To linecount
                        Component.CodeModule.DeleteLines (1)
                    Next
                    If Not Component.Type = vbext_ct_Document Then 'this is superfluious now as all the code is in the 'ThisWorkbook' Module
                        Project.VBComponents.Remove Project.VBComponents(Component.Name)
                    End If
                End If
            Next
        End If
    Next
End Sub


Sub DeleteRefLibraryVBIDE()
    'Deletes the reference to the VBIDE
    On Error Resume Next
    With ThisWorkbook.VBProject.References
        .Remove .Item("VBIDE")
    End With
End Sub

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

Thanks Dave, it works a treat!

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!
Chris.

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
        Application.Calculate
        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)?

Cheers,
Chris.

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:

Sub CallingCode()
    'Example Sub that creats some dynamic code, calls it and then deletes it
    
    Const HOLDING_MODULE_NAME As String = "DynamicModule"
    
    'some code...
    
    Call CreateVariableCode(10, HOLDING_MODULE_NAME)
    Call DynamicSub
    RemoveVariableCode (HOLDING_MODULE_NAME)
    
End Sub


Sub CreateVariableCode(NumberOfCodeLinesNeeded As Integer, ModuleName As String)
    'Sub that adds code lines to a blank sub routine
    
    Dim ThisModule As VBComponent
    Dim CodeLines As Integer
    Dim LineOfCode As String
    
    Set ThisModule = ThisWorkbook.VBProject.VBComponents(ModuleName)
    
    'Write the dynamic code to the new module
    For CodeLines = 2 To NumberOfCodeLinesNeeded + 1
        LineOfCode = ConstructCodeNeeded(CodeLines - 1) 'call to a function that builds your code
        ThisModule.CodeModule.InsertLines CodeLines, LineOfCode
    Next
    
End Sub


Sub RemoveVariableCode(Module As String)
    'Sub that deletes the dynamic code added to the previously blank sub routine
    
    Dim ThisModule As VBComponent
    
    Set ThisModule = ThisWorkbook.VBProject.VBComponents(Module)
    
    'check that the sub is not already blank
    If Not ThisModule.CodeModule.CountOfLines = 2 Then
    
        linecount = ThisModule.CodeModule.CountOfLines
        'ignore the first and last lines as they are the Sub...End Sub statements
        For Line = 2 To linecount - 1
            'always delete line 2 as the line numbers are dynamic
            ThisModule.CodeModule.DeleteLines (2)
        Next
        
    End If
    
End Sub


Function ConstructCodeNeeded(Line As Integer) As String
    'example function that builds required code
    
    ConstructCodeNeeded = "'Example Code Line " & Line
    
End Function

Hi!

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!

Regards,
Chris.

http://www.thereillys.plus.com/BezierSurfaces.xls

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.

Thanks! That was roughly the direction I was going, I was just nervous that something could 'pinch' the ActiveWorkbook before it was assigned to the ObjectVar.

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 )

m,

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'

m

I am sorry it did not work for you.

PM me and I can email you an example that works for me if would help?

Try:

=IF(ISBLANK(A1)=TRUE,"yes","no")

Have a go, you will see it works :yes:

mhabib,

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

=SUMIF('C:[Book1.xls]Sheet1'!$A$1:$A$16,1,'C:[Book1.xls]Sheet1'!$B$1:$B$16)

Try:

{=SUM(IF('C:[Book1.xls]Sheet1'!$A$1:$A$16=1,'C:[Book1.xls]Sheet1'!$B$1:$B$16))}

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.

Andy,

This is exactly what I was after, thank you very much! (took me a wile to realise I hadn't referenced MS Forms 2.0 Object Library :banghead: )

I can feel a whole new world of irritatingly interactive forms coming on :biggrin:

Cheers!

Ray,

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
.HOSTNAME = HOSTNAME
.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
Rows(rownum).Delete
rownum = rownum - 1
End If
rownum = rownum + 1
Loop
End Sub