Posts by A9192Shark

Re: Range.offset Execution Time

shg,
Thanks.
I will give that a go in the morning. I did wonder about the with statement, but why should it run fast sometimes and not otehrs????
I will optimise using Range variables and the with statement. I will also look at teh rest of the code to see if that can be improved.
Cheers,
Alan.

Hello,

I have a work book that uses four inputs to change an analysis of a time series. The workbook also has a macro that will use 144 different cases (sets of the input variables) and save the results for graphing. The individual case works fine with a calculation time of 8-10 seconds, no problems there as it is processing 48 000 rows.

The macro simply loops through teh 144 cases and sets the input cells using four lines of code highlighted in teh code at the bottom. One line of the code looks like:

Range("nmResults").Offset(lngRowNumber, 0) = Range("nmMonths")(lngMonth)

This line takes approximately 7 to 9 seconds to execute. Four lines like this plus a big calc times 144 starts to add up in execution time.

The problem is that the workbook has at times run very quickly but we can not get it to do so again.

Any ideas would be appreciated; I have looked at http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm for ideas and tested the Application.EnableEvents to no avail.

Thanks,

Alan.

Option Explicit


Private lngMonth                As Long
Private lngHsNumber             As Long
Private lngDurationNumber       As Long
Private lngRowNumber            As Long
Private oldStatusBar
Private strHsStatus             As String
Private rngToClear              As Range
Private dtStart                 As Date
Private dtEFT                   As Date


Public Sub CalculateProbabilities()
    dtStart = Now()
    oldStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    lngRowNumber = 0
    'Clear Results Area
    Set rngToClear = Worksheets("Probabilities").Range("A65536").End(xlUp)
    If Intersect(rngToClear, Range("nmResults")) Is Nothing Then
        Set rngToClear = Range(Worksheets("Probabilities").Range("A65536").End(xlUp), Range("nmResults").Offset(1, 3))
        rngToClear.ClearContents
    End If
    For lngHsNumber = 1 To Range("nmHsCritValues").Rows.Count
        Range("nmHsCrit") = Range("nmHsCritValues")(lngHsNumber)
        strHsStatus = "Hs=" & Format(Range("nmHsCrit"), "0.00") & _
                                "(" & lngHsNumber & "/" & Range("nmHsCritValues").Rows.Count & ")"
        If lngHsNumber > 1 Then strHsStatus = strHsStatus & " EFT: " & dtEFT
        Application.StatusBar = strHsStatus
        Debug.Print strHsStatus
        Application.Calculate
        For lngMonth = 1 To 13
            For lngDurationNumber = 1 To Range("nmDurations").Columns.Count
                lngRowNumber = lngRowNumber + 1
[B]                Range("nmResults").Offset(lngRowNumber, 0) = Range("nmMonths")(lngMonth)
                Range("nmResults").Offset(lngRowNumber, 1) = Range("nmHsCritValues")(lngHsNumber)
                Range("nmResults").Offset(lngRowNumber, 2) = Range("nmDurations")(lngDurationNumber)
                Range("nmResults").Offset(lngRowNumber, 3) = Range("nmP")(lngMonth, lngDurationNumber)[/B]            Next lngDurationNumber
        Next lngMonth
        'ThisWorkbook.Save
        dtEFT = dtStart + (Now() - dtStart) * Range("nmHsCritValues").Rows.Count / lngHsNumber
    Next lngHsNumber
    Application.Calculate
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    Application.ScreenUpdating = True
End Sub

Re: Status Bar Font Appears Solid Black

Derk,
I have had this for quite a while now and it just 'got to me' today so I decided to post a message. I have therefore rebooted my machine lots of times.

Today is the first time I noticed the difference between maximised and normal window mode. I have implemented a quick fix that sets the window state to normal whilst the macro is running and then restores it to what it was before teh macro ran.

I will live- it is just a silly thing!

Thanks,

Alan.

Hello,

I am trying to use teh staus bar for simple reporting of progress.

When I launch Excel 2003 on XP the status bar works as normal with Excel reporting progress etc.

When I take control in VBA using

Application.StatusBar = "some text here"

then the status bar text appears as solid black. As if each character is black on a black background. If the text is longer then the length of black increases. See attached image.

OK now it gets stranger...

I reduced the Excel window from maximised to allow me to print a small image of the window showing teh status bar. The status bar works as I would expect, running the macro I can see the progress.

When I maximise it it reverts to being black on black!

Any ideas?

Thanks,

Alan.

Hello All,

This is driving me nuts! The code below should reformat a workbook within the application window such that there are three windows. Each window will show a different worksheet. The Instructions window will appear over the other two (Diagram and Data) which are tiled. The user will then be able to see the data input sheets in one window and the diagram in another window. If he wishes to see the instructions then the window is open and can be moved or resized as he wishes.

Option Explicit


Public wdwDiagram       As Window
Public wdwData          As Window
Public wdwInstructions  As Window


Public Sub FormatWorkBookInWindow()
    Const lngMargin As Long = 20
    'Close all but one window
    Do While ThisWorkbook.Windows.Count > 1
        ThisWorkbook.Windows(1).Close
    Loop
    
    'Rename that window to be instructions and format it
    Set wdwInstructions = ThisWorkbook.Windows(1)
    With wdwInstructions
        .WindowState = xlNormal
        .DisplayWorkbookTabs = True
        .Caption = ThisWorkbook.Name & ": Instructions"
        .Top = 0 + lngMargin
        .Left = 0 + lngMargin
        .Height = Application.UsableHeight - 2 * lngMargin
        .Width = Application.UsableWidth - 2 * lngMargin
        Worksheets("Instructions").Select
        .DisplayGridlines = False
    End With
    
    Set wdwDiagram = ThisWorkbook.NewWindow
    With wdwDiagram
        .WindowState = xlNormal
        .DisplayWorkbookTabs = True
        .Top = 0
        .Left = 0
        .Width = Application.UsableWidth
        .Height = 0.6 * Application.UsableHeight
        .Caption = ThisWorkbook.Name & ": Diagram"
        Worksheets("Diagram").Select
        .DisplayGridlines = False
    End With
    
[B][U]    Set wdwData = ThisWorkbook.NewWindow[/U][/B]
    With wdwData
        .WindowState = xlNormal
        .DisplayWorkbookTabs = True
        .Top = wdwDiagram.Height + 1
        .Width = Application.UsableWidth
        .Height = Application.UsableHeight - wdwDiagram.Height
        .Left = 0
        .Caption = ThisWorkbook.Name & ": Data"
        Worksheets("Block Wall").Activate
        .DisplayGridlines = True
    End With
    
    wdwData.Activate
    wdwDiagram.Activate
    wdwInstructions.Activate
    
    Dim wdw As Window
    For Each wdw In Windows
        Debug.Print wdw.WindowNumber, wdw.Caption
    Next wdw


End Sub

The problem is that when the bold underlined line is executed the window containing the instructions also changes to display the diagram worksheet.

Is this in some way linked to Windows(1) always being the active window? Can I (should I) use Window objects in this way?

I have tried to close all but one window, rename that window, create the others and rename them and then allocate the window objects to variables using the names of the windows rather than the window number and this also fails. I have also tried to rename each window as I create it and then not use the wdw variables at all but change the with statement to work on Windows([Window name]).

Still the same/similar problems.

Any ideas please?

Thanks,

Alan.

Re: Filled Patterns For Xy Chart Lines

Thanks Andy,

I would need to learn alot for that and I need a quick solution (as ever) and so I will use the XY chart in Reporting Services and change the line thickness. It is not ideal but it will work.

I will then look to develop my own class/component at some point for drawing these special graphs that I need.

Thanks for your guidance,

A.

Re: Filled Patterns For Xy Chart Lines

I have just explored Option 2 and will now use the line item in Reporting Services!

Thanks for the info.
A.

Re: Filled Patterns For Xy Chart Lines

Andy,

Thanks for confirming what I thought was the case.

I am trying to create a wind/wave rose and I can generate the data from various types of sources but needed to be able to change the line thickness and symbology to conform to some standards.

I am actually developing in VB and do not have a good charting tool and was going to try and use Excel's capability but I guess I am back to the drawing board (or have to buy a charting tool that wil do the job).

Do you know any cheap/freebie charting tools for VS2005? (sorry question is off the forum subject!:) )

Thanks again,

Alan.

Hello,

I have an xy chart for which I would like to format the lines to be different patterns beyond those available in the format series dialog. I recognise that this will mean using VBA and I am happy to do that. I would like to be able to use patterns in the same way as they are used in a column chart.

I recorded a macro to see which properties were changed when the patterns are selected in the dialog for the xy chart and it is clear that it is only the LineStyle of the border and that there is no interior.

With Selection.Border
        .ColorIndex = 57
        .Weight = xlMedium
        .LineStyle = xlGray25
    End With

This would explain why when I use debug.print ser.Interior.Pattern I get -4105 or if I change ser.Fill.Forecolor etc it has no effect.

Is there any way to use user defined patterns to colour the lines in the same way as in a histogram/coloumn chart?

Thanks,

Alan.

Re: Activechart.export Not Renaming Temp File

Thanks for looking. Interesting it is an environment setting, not sure where I would look to change this, I will just tuck this away and try and remember it and always use 8.3. It is only to quickly generate some graphs for a report.

Thanks,

A.

Re: Activechart.export Not Renaming Temp File

Andy,

I have stripped down the workbook and attached it to this post. Note that the data actually consists of another 50 or so columns taht I have deleted to reduce the file size. I have also deleted all data other than Profile 1 from the data table.

Stop press.

In stripping it down I decided to check it was still causing the problem. I noticed that the temporary filenames all had a DOS 8.3 filename using the ~ and a number. So I changed the code to create a filename with an 8.3 filename and it works!

Workbook attached for demo purposes. I have added a button and a cell to use the long filenames so that you can easily reproduce the problem if you wish,

Thanks!

A.

Hello,

I am trying to export a chart using the code below, the loops change the data that is used to produce the chart:

Dim strFilename As String
    Dim strExt  As String
    Dim lngProfile As Long
    Dim lngOption As Long
    For lngOption = 1 To 3
        Range("nmOption") = Choose(lngOption, "Existing", "Option 4", "Option 5")
        For lngProfile = 1 To 4
            Range("nmProfile") = "Profile " & lngProfile
            ActiveSheet.ChartObjects("Chart 13").Activate
            ActiveChart.ChartArea.Select
            strExt = "gif"
            strFilename = ThisWorkbook.Path & "\Images\" & _
Range("nmOption") & "_" & Range("nmProfile") & "." & strExt
            ActiveChart.Export strFilename, strExt
        Next lngProfile
    Next lngOption

Each export command creates a file with the correct name but no data (0kb in size) and a temporary file that is the correct file type (I have tried both jpg and gif).

I could try and copy the temporary file, but why does Excel not finish the job? I have tried to put a loop in and also added DoEvents after the export but no joy.

Any ideas?

Thanks,

Alan.

Re: Button Or Hyperlink On Access Report

You can not place a button a report.

If what you are trying to do is to display a report and then allow the user to open another report then you will have to provide the options on the form taht opens the first report.

If you actually want to print two reports from one button then insert two buttons on a form and use the wizard to open the reports. Then copy the code for one of teh buttons into the code for the other button (you may be able to work out how to simplify the code).

If I recall you need to wait for one report wndow to close before the other can be opened. Youmay need to pause the code until the first report is closed.

Good luck,

A.

Re: Add Attachments To Access Database

My advice is DO NOT insert OLE objects in teh database. The file size grows far to quickly. Instead store a string that provides the filename and path of the 'attachment'.

For pictures you can display these by using teh ONCurrent Event that will update the source for an unlinked image on the form. If you want to open a word document on your form then you will have to insert a word document object and again update teh source. I have avoided doing this in teh past and instead used teh stored filename string to change a hyperlink. If the user wishes to read the 'attachment' then they click the hyperlink and Word is opened without you having to determine taht Word was required.

Good luck,

A.

Re: Stop Display Of Names Of Named Ranges When Zooming

The code solution works!

Thanks very much for the solution, I will store it a way and try and remember it....

The hiding of the formula bar did not work.

Thanks,

A.

Re: Stop The Display Of Names For Named Areas

Thanks,

I will give that a go later!

A.

Hello,

Can I stop the displaying of the name of the named area when I am zoomed out of a worksheet? I can not see an option on the Tools > Options > View menu.

[Edit]
A workaround, found by accident, is to make the named area have more than one area. However this is not always possible so I am still interested in an answer!

Thanks,

Alan.

Re: Finding Next 1 In A List Of 0 And 1

I agree with you.

If I was allowed the time I would develop a program to read the original text file and do alot more with teh data. As it is I did a calc using Excel that works very well. I was then asked the next logical question and tried to look at it using the excel sheet.

Cheers,

A.

Re: Finding Next 1 In A List Of 0 And 1

Rich,

Thanks for the suggestion. I have solved the problem by telling my boss that it really is not required. This is a reasonable statement at this stage but we will want teh info later.

I will look into the idea of using a string to store the data. I think that I could implement something using this but it will be a peice of code rather than a function.

Your code runs very quickly because you fill the data from a loop, I have to fill it from a worksheet where the numbers are calculated.

Thanks for your help- I will look at using teh string when I solve this one later in teh year!

Cheers,

Alan.

Re: Finding Next 1 In A List Of 0 And 1

Kris,

This still takes forever to run.

I have implemented the udf it takes too long. I will now look at developing a more generic analysis tool.

Thanks for help everyone,

A.