Replies

Peter,

Another thing you might consider to to Explicitly Type each of your variables. As it is you are using Variants (the default data type) which are highly inefficient and chew up memory space. Also avoid multiple variables in one DIM give each its own line, takes up space but makes things easier to read. HTH :cheers:

I have tried the watch suggestion and I may not be doing it correctly , two watches first just the array name ArrTemp() and second a new line I added to check if the assignment to a cell was the problem

CheckEmpty = WorksheetFunction.StDev(ArrTestTemp())

Both showed in the watch window our of context and variant empty.

Added a little test to my code to check if the array was empty

Code:

 
         For Z = 1 To NoRecalcs           
 If IsEmpty(ArrTemp(Z)) Then
            MsgBox “Array ” & Z & ” is empty ”
            End If
            Next Z
                ‘Do Calculations
                CheckEmpty = WorksheetFunction.StDev(ArrTestTemp())
                MsgBox CheckEmpt

and it did not fail but the moment I tried to calculate the StDev of the Array I had just checked to have all the elements in place it is empty.

Thanks for the tip about defining the variants one of the reasons I added a Progress bar was to try and find where the procedure slowed down, writing to the worksheet certainly slows it. I assume that all but one of my arrays should be integer the odd one the names of the results a string or is there any other class that would speed things up.

Did not mention before but all of the routines in this procedure are in an Addin that I call from my personal workbook just in case it makes any difference.

Thank you again, I have been working on this routine for some time.

Take care

peter

Thanks for taking the time.

I do not get an error when running the Module at that point or any other, the ArrTemp() simply goes blank. Also as I pointed out in my post running a test with a worksheet that has cells that recalculate it works fine. Also as I said if I copy the results from the workbook with which I have the problem to another and then run the procedure on that work book I have copied the results to it works OK.

For completeness ArrTemp() splits the multiple ArrFinal( , ) into the number of outputs that have been selected. It changes each time the loop for outputs is run. It has been re dimensioned at the start of the sub routine I posted.

Sorry I cannot be more explicit and thanks again.

Peter

Peter,

Kind of hard to do this w/o the workbook in question but here are a couple of things you can check:

[*]You day the array is declared publicly…exactly how? If you are declaring it outside of any procedure with a Dim statement it is only visible in the same Module so if the Standard Deviation procedure is in another module it will be a different variable and have no data.
[*]Try using a Public statement vs Dim.
[*]Have you tried putting a Watch on the Array so the code breaks if the array gets wiped?
[*]Use this query {vba debugging watch expressions} in your browser to learn how to do watch expressions. Knowing the location of execution when the array goes blank can be a big help in finding the problem.

HTH :cheers:

Thanks again, for taking the time. The array is Public and is in the same module as the code I posted. Just in case I have made an error this is the start of the module

Code:

Option Explicit

Option Base 1
Public NoRecalcs, NoBins, ArrXValues(), FrequencyArr, PctDone, PbarCheck, PBStart, PBEnd, ScaleMax, ScaleMin, ScaleMajor, _
ResultCells, ArrResultCells, NoOutPuts, ArrFinal, LabelCells, ArrLabelCells, NoLabels, BinFq, ColInc, Col1, Col2, i, y, x, Hi, n, _
ArrTemp(), ArrNDValues(), ArrBinHisto(), Response, PctDo, PctCheck, PrintData, WBtemp

I have not used watch expressions before so I will get to work on that.

The workbook in question is part of an estate plan so I would send it to you privately if acceptable but do not want to post it on line.

Thanks again

Peter

Hi Zeddy

The Array is already defined as Public, I think it is something to do with the syntax. I tried to set the array to the two arrays that are the feed for the frequency function but that did not work. The only thing that does (so far) is actual setting the array to worksheet cells the same size as the input.

by the way since seeking help it has occurred to me that I need the data on the worksheet to build the chart other wise once I exit the routine the Chart will no longer have any input.

Thanks for taking the time to help.

Peter

RG

I think I have messed it up again I tried to reply using the email address you provided and was asked to authentic and that failed as well do you have my files yet?

Sorry

peter

Hi Maud

Thanks for the input tried that and it does not cycle twice as it did on exit but it still goes to the command button “Next Stock” bypassing the next text box Opt 6 which is what I would have thought it would have gone to had the set focus command not worked. If the strike price is greater than the purchase price then it does just tab to the Opt6. It’s got me beat,I can live with it just not what is needed, it could be the strike is wrong as opposed to the purchase price however I wanted to start ta the first one and work back down the form.

Thanks again for your time

Peter

RG

Tried Application.enablevents= False/True same result

thanks

Peter

Hi RG

Do not have a problem passing the file onto you. However I would like to do it directly as there are lots of bits of code from other people and I am not sure I have made that clear through the routines. As I was only going to use it myself it was not an issue but if it gets into the public domain I would like to make sure it is done properly. Can you let me know if I can send to you?

Have not tried your late suggestion will do and let you know thanks again for all your help.

Take care

peter

RG Thank you

Still no luck I changed your code to conform with my UserForm hopefully I did that correctly see below.

Private Sub Opt5_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim iAns As Integer

If CDbl(Opt5.Value) 0 Then ‘Check to make sure Shares have been entered in multiples of 100
MsgBox “The number of shares must be in Units of 100”
Cancel = True
With Me.Opt4 ‘Selects the previous entry so it can be overwritten
.SelStart = 0
.SelLength = Len(.Text)
End With
End If
End Sub

Private Sub Opt5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim iAns As Integer
If CDbl(Opt5.Value) < CDbl(Opt2.Value) Then
iAns = MsgBox("The Strike Prices is lower than the Purchase Price is this correct", _
vbYesNo, "Check Strike Price")
If iAns = vbNo Then
Me.Opt2.SetFocus
End If
End If
End Sub

Private Sub UserForm_Activate()
With EnterOptionData
.Top = Application.Top + 125
.Left = Application.Left + 600
End With
Opt1.SetFocus
End Sub

Private Sub UserForm_Initialize()
Opt1.SetFocus
Opt2.Text = Format(Number, "000.00")
Opt3.Text = Format(Number, "000.00")
Opt4.Text = Format(Number, "000.00")
Opt5.Text = Format(Number, "000.00")
End Sub

Thank you and Take care

Thanks for that suggestion, there is not a yes option unless I should include an else option in the if then but if the test is passed I want to move on. Deleting the cancel = true for some reason moves the focus to a command button not the next text box.

Take care

Peter

The start is a form that allows me to select a number of reports of which this sequence is one.

The Sub “ExpReport1Year” is called first that builds the two tables then it opens the report.

In this case it is the report for Expenses only. the field “Type” can be a revenue classification or it can be blank. If it is blank the batch is only for expenses. You can see the report is designed to be for a selected year.

Thanks again

Private Sub ExpOnly1PYear_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “rptSelOpforExp”
Call ExpREport1Year
stLinkCriteria = “[OperatorID]=” & Me![OperatorID]
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
End Sub

Private Sub ExpREport1Year()
‘Creates a table of Batch numbers for the production year selected. 11192014
‘Remember the object is to get batch numbers that appear in the production year as it is possible that there may be items in prior years
Year1 = InputBox(“enter Production year”)

DoCmd.SetWarnings False

‘Note TYpe is Null to make sure no revenue entrys are included
DoCmd.RunSQL “SELECT tblIncome_Expenditure.BatchID ” _
& “INTO tblBatchNoExpSelYear ” _
& “FROM tblIncome_Expenditure ” _
& “GROUP BY tblIncome_Expenditure.BatchID, ” _
& “tblIncome_Expenditure.Year, ” _
& “tblIncome_Expenditure.Type ” _
& “HAVING (((tblIncome_Expenditure.Year)=” & Year1 & “) ” _
& “AND ((tblIncome_Expenditure.Type) Is Null));”

‘Creates a table of Data for the Selected year based on the Batch Numbersin the previous SQL statement
DoCmd.RunSQL “SELECT tblIncome_Expenditure.BatchID, ” _
& “tblIncome_Expenditure.OperatorID, ” _
& “tblIncome_Expenditure.Month, tblIncome_Expenditure.Year, ” _
& “Sum(tblIncome_Expenditure.Revenue) AS SumOfRevenue, ” _
& “Sum(tblIncome_Expenditure.Taxes) AS SumOfTaxes, ” _
& “Sum(tblIncome_Expenditure.GOthDedNothDeds) AS SumOfGOthDedNothDeds, ” _
& “Sum(tblIncome_Expenditure.Expenses) AS SumOfExpenses, ” _
& “Sum(tblIncome_Expenditure.NetThisEntry) AS SumOfNetThisEntry ” _
& “INTO tblExpOnlySelYear ” _
& “FROM tblBatchNoExpSelYear INNER JOIN tblIncome_Expenditure ” _
& “ON tblBatchNoExpSelYear.BatchID = tblIncome_Expenditure.BatchID ” _
& “GROUP BY tblIncome_Expenditure.BatchID, tblIncome_Expenditure.OperatorID, ” _
& “tblIncome_Expenditure.Month, tblIncome_Expenditure.Year ” _
& “HAVING (((tblIncome_Expenditure.Year)=” & Year1 – 1 & “)) ” _
& “OR (((tblIncome_Expenditure.Year)=” & Year1 & “)) ” _
& “OR (((tblIncome_Expenditure.Year)<[year]));"
DoCmd.SetWarnings True
End Sub

Thank you both for taking the time to reply.

Mark, I will try and explain. The Batch # referred to in my earlier post groups records that may include both revenue and expenses. What I wanted to do was separate Batch’s that included revenue from those that did not. Hence by grouping on type ( the field I referred to as being null or not). This returned just the batch #’s for 1) revenue, 2)revenue and expenses or just 3)expenses. Using the batch numbers to return all of the records for the selected batch #’s produced the final result used to produce a report that was either Revenue or Expense based.

In the clumsy way I am presently doing this (hence the request for assistance) the first table which is just a list of batch numbers is joined to the main db to produce the second table which is the basis for the report.

Hope this makes sense thank you again.

Peter

Thanks Mark , I am dense I know as I am still missing something but at least problem for now is solved and I will work on your suggestion.

Hi Mark

first my apologies for not replying to your post but my internet has been down for two days.

Secondly I discovered that I need Max rather than Min, I had started out asking for Min thinking if i solved that Max would work.

I have managed to solve my problem but I had to use an aggregate function in a query. Once I had established what batches had to be processed I concatenated Year, Month (padded if one digit) and BatchID, used grouping to get the Max and then in the report sorted by the concatenated field then year and Month which put all the entries in the correct order.

My DB is not that large about 40K entries and in any year about 4 to 5K so I guess the efficiency is not that critical. However I would prefer to follow your advice but cannot work out how to do it.
This is the sql statement I get when I try and follow your suggestion.

SELECT tblIncome_Expenditure.BatchID, tblIncome_Expenditure.Year, Max(tblRevOnlySelYear.Year)
AS MaxOfYear
FROM tblRevOnlySelYear INNER JOIN tblIncome_Expenditure
ON tblRevOnlySelYear.BatchID = tblIncome_Expenditure.BatchID
GROUP BY tblIncome_Expenditure.BatchID, tblIncome_Expenditure.Year;

This does not give me the correct result.

Again my thanks for your time.

Peter

I had posted a reply and went to edit it and seem to loose the lot. First thank for taking the time to respond to my post unfortunately I feel I did not outline properly what the problem is.

The query is part of the routine to produce a report and what I want to do is have the report listed so it shows each month sequentially. The difficulty is that within each batch there are sometimes adjustments to prior months and years and infrequently there are two batches with the same last month.I have attached a PDF which shows data and the desired result, there is of course a other information but I thought this way kept it simple.

Thanks