VBA help (again)

[grantx]

The following represent values inserted into their respective cells:

ExcelDoc.Sheets(1).Cells(1, 3).Value = "BSz 1 "
ExcelDoc.Sheets(1).Cells(1, 5).Value = "BSz 2"
ExcelDoc.Sheets(1).Cells(1, 7).Value = "BSz 3"
ExcelDoc.Sheets(1).Cells(1, 9).Value = "BSz 4"
ExcelDoc.Sheets(1).Cells(1, 11).Value = "BSz 5"
ExcelDoc.Sheets(1).Cells(1, 13).Value = "BSz 6"
ExcelDoc.Sheets(1).Cells(1, 15).Value = "BSz 7"
ExcelDoc.Sheets(1).Cells(1, 17).Value = "BSz 8"
ExcelDoc.Sheets(1).Cells(1, 19).Value = "BSz 9"
ExcelDoc.Sheets(1).Cells(1, 21).Value = "BSz 10"

How would I sum these and insert the result into a cell (I will also exclude the above values from the sheet)?

Grant.

 

[Rhody Trader]

I presume you mean how would you sum them in VBA, not directly in a worksheet.

Have you tried the Application.WorksheetFunction.Sum() function? You could do something like:
Total = Application.WorksheetFunction.Count(Worksheets("Sheet1").Range("D44000"))

And of course putting that value into a cell would be done by:
Sheets("Sheet1").Cells(1,1).Value = Total

Does that help?

 

[Profitaker]

Rhody

The count function would only count the cells, you would need the SUM function.

Grant

Sub grantsmasterpiece()
Dim total
Cells(1, 3).Value = 1
Cells(1, 5).Value = 2
Cells(1, 7).Value = 4
Cells(1, 9).Value = 6
Cells(1, 11).Value = 4
Cells(1, 13).Value = 7
Cells(1, 15).Value = 4
Cells(1, 17).Value = 3
Cells(1, 19).Value = 9
Cells(1, 21).Value = 2
total = Application.WorksheetFunction.Sum(Range(Cells(1, 3), Cells(1, 21)))
Cells(5, 3) = total 'or whatever cell you fancy !
End Sub

 

[A Dashing Blade]

'
'===============================================
'
Sub GenericSumRoutine()

Dim TempSheet As Worksheet
Dim TempRange As Range
Dim SummedTotal As Variant

'assign worksheet to worksheet variable. use the "Set" statement
'to assign objects
Set TempSheet = ThisWorkbook.Sheets(1)
'assign range to range variable.
Set TempRange = Range(TempSheet.Cells(1, 3), TempSheet.Cells(1, 21))
'feed assigned range into function
SummedTotal = SumCells(TempRange)
'put value onto cell in tempsheet
TempSheet.Cells(1, 1).Value = SummedTotal

TidyUp:
'not really required, just good coding practice imo
'to explicitly clear object variable
Set TempSheet = Nothing
Set TempRange = Nothing
End Sub
'
'===============================================
'
Function SumCells(i_Range As Range) As Variant
'Function that can return a value to a spreaddy cell
'or called by a piece of vba code. Takes account
'of 2 dimensional ranges and non-numeric cells
Dim RowCounter As Long
Dim ColCounter As Long
Dim TempValue As Variant
Dim SubTotal As Double

For RowCounter = 1 To i_Range.Rows.Count

For ColCounter = 1 To i_Range.Columns.Count

'assign contents of cell to generic datatype
TempValue = i_Range.Cells(RowCounter, ColCounter).Value

If IsNumeric(TempValue) Then
'only sum numeric values
SubTotal = SubTotal + CDbl(TempValue)
End If

Next

Next

SumCells = SubTotal

End Function
'
'===============================================
'

 

[Rhody Trader]

Rhody

The count function would only count the cells, you would need the SUM function.

Absolutely correct. I forgot to change from Count to Sum after I did a cut and paste. My appologies.

 

[grantx]

Rhody,

Still appreciated, mate.

After discarding my highly reviewed vba manual, around 4:00 this morning I worked out a solution:

ExcelDoc.Sheets(1).Cells(1, 14).Value = BidSize
ExcelDoc.Sheets(1).Cells(2, 14).Value = .LimitVol(2, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(3, 14).Value = .LimitVol(3, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(4, 14).Value = .LimitVol(4, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(5, 14).Value = .LimitVol(5, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(6, 14).Value = .LimitVol(6, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(7, 14).Value = .LimitVol(7, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(8, 14).Value = .LimitVol(8, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(9, 14).Value = .LimitVol(9, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(10, 14).Value = .LimitVol(10, osBuy, 0, Data)

ExcelDoc.Sheets(1).Cells(1, 15).Value = AskSize
ExcelDoc.Sheets(1).Cells(2, 15).Value = .LimitVol(2, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(3, 15).Value = .LimitVol(3, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(4, 15).Value = .LimitVol(4, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(5, 15).Value = .LimitVol(5, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(6, 15).Value = .LimitVol(6, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(7, 15).Value = .LimitVol(7, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(8, 15).Value = .LimitVol(8, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(9, 15).Value = .LimitVol(9, osSell, 0, Data)
ExcelDoc.Sheets(1).Cells(10, 15).Value = .LimitVol(10, osSell, 0, Data

This puts each value on the sheet (not the original requirement but certainly useful). From here I could simply =SUM() the columns. However, the solutions suggested (below) add to flexibility and will be used .

PT,

That seems OK.

DB,

Typical thoughts when I'm driving: 'move it, you silly bitch...sorry, mate...that's alright...nice car, mate'

Your typical thoughts: 'acceleration...mass...velocity...energy...friction...gravity...expansion of gases'.

I've printed your solution (as with all your replies representing a "higher" level). Difficult as it appears, it illustrates the potential of vba and increases my interest (and thus motivation) to learn more.

Can I eliminate all ref's to "ExcelDoc.Sheets(1)."? There is only one sheet.

Thank you for your help.

Grant.

 

[A Dashing Blade]

Can I eliminate all ref's to "ExcelDoc.Sheets(1)."? There is only one sheet.

No, you need a handle on the sheet object (else how XL gonna know what cell to put the data, the sheet is the "perant" object of the cell)

you could . . .
Dim TempSheet as worksheet
Set TempSheet = ExcelDoc.Sheets(1)

then replace ExcelDoc.Sheets(1) with TempSheet thruoput all subsequent code tho'

 

[grantx]

DB,

Don't think it's worth doing.

Thanks,

Grant.

 

[grantx]

Test. Ignore

 

[grantx]

PT,

Re “Sub grantsmasterpiece()”, can we put any word (even meaningless) between Sub and ()?

Re “=1, = 2”, in eg

Cells(1, 3).Value = 1
Cells(1, 5).Value = 2

To what do these correspond?

Re “Dim total”, is “total” here an arbitrary label? I ask because I would like to use an alternative. See below.

To create two tables (bid total, ask total), could I simply Copy and Paste the original (including Sub...() and End Sub)and adjust cell ref’s accordingly?

Grant.

 

[A Dashing Blade]

PT,
Re “Sub grantsmasterpiece()”, can we put any word (even meaningless) between Sub and ()?

Yup, you can call the sub procedure anything you want

Re “=1, = 2”, in eg

Cells(1, 3).Value = 1
Cells(1, 5).Value = 2

To what do these correspond?

Cells(1, 3) = 1st row, 3rd column of the perant object,in this case a sheet so = cell("A3")

Re “Dim total”, is “total” here an arbitrary label? I ask because I would like to use an alternative. See below.

Yup, it's just a variable name, there are a few reserved Keywords but any text will do

To create two tables (bid total, ask total), could I simply Copy and Paste the original (including Sub...() and End Sub)and adjust cell ref’s accordingly?
Grant.

Yup.

 

[grantx]

DB,

Thank you for the clarification.

Re correspondence of

“=1, = 2”, in eg

Cells(1, 3).Value = 1
Cells(1, 5).Value = 2

I know that Cells(1,3) refers to 1st row, 3 column but what does “=1” refer to?

Grant.

 

[A Dashing Blade]

put the number 1 in the cell

actually, you're assigning the value 1 to the .value property of the cell
other properties of the cell would "cell.interior.color=vbRed" for example

guess what Cells(1, 3).Value = "wibble" would do!

 

[timm]

No, you need a handle on the sheet object (else how XL gonna know what cell to put the data, the sheet is the "perant" object of the cell)

you could . . .
Dim TempSheet as worksheet
Set TempSheet = ExcelDoc.Sheets(1)

then replace ExcelDoc.Sheets(1) with TempSheet thruoput all subsequent code tho'

Try this

With ExcelDoc.Sheets(1)
stuff you want to do the this sheet e.g.
.cells(1,1).value=0
End With

 

[grantx]

DB,

There may be some confusion (probably my fault).

From initial query:

ExcelDoc.Sheets(1).Cells(1, 3).Value = "BSz 1 "
ExcelDoc.Sheets(1).Cells(1, 5).Value = "BSz 2"
etc

"BSz 1 ", etc should be a value, eg 500. The quotation marks would indicate text (?). My mistake.

The relevant codes are, eg

ExcelDoc.Sheets(1).Cells(12, 14).Value = BidSize 'Bid
ExcelDoc.Sheets(1).Cells(13, 14).Value = .LimitVol(2, osBuy, 0, Data)
ExcelDoc.Sheets(1).Cells(14, 14).Value = .LimitVol(3, osBuy, 0, Data)
etc

These are the figures to be summed.

Would this change:

Sub grantsmasterpiece()
Dim total
Cells(1, 3).Value = 1
Cells(1, 5).Value = 2

total = Application.WorksheetFunction.Sum(Range(Cells(1, 3), Cells(1, 21)))
Cells(5, 3) = total 'or whatever cell you fancy !
End Sub


Sorry to mess you about. I think it’s called a fkcu up.

Grant.

 

[The General]

Grant,

Would make sense if you utilised a dynamic range for the values in the sheet. That way you can use the range name in code as opposed to the range 'address'. This also means that you could adxd more rows to the bottom of the range and these would be included. Do a search on 'Dynamic Excel range' (utilises offset worksheet function). More professional imho....

 

[grantx]

General,

I've spent most of the day looking for solutions on the net; I've also downloaded and printed two intro's to vba. I've thrown one away, I have yet to read the second.

This is want I want to do, but a simple explanation is elusive: calculate, determine, insert, extract values into an Excel sheet. Isn't this Beginner's stuff?

Please refer to the attachment. The figs in N2...P3 are taken straight from the sheet (eg =SUM( N13:N22)), not vba generated. I decided it was simpler (albeit not necessarily the best) solution. The DOM is vba generated.

Now I want to record N1 and P1 in Bcv (bid cumulative volume) and Acv (Ask cumulative volume), respectively on a tick-by-tick basis (as the values at left). I've tried many ways, none work.

Grant.

 

[A Dashing Blade]

OK

To do this, you have to know if the worksheet_Change event is firing.

Slap this code into the WORKSHEET vba page ie NOT a normal module, but the code page that appears when you double click on the worksheet object in the Project explorer . . .

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "N1" Or Target.Address = "P1" Then

Application.StatusBar = Target.Value

End If

End Sub

IF you're seeing the values appear in the status bar of your workbook when your spreaddy runs then you know you can trap & therefore process every change.

If this doesn't work, then try replacing with this code . . .

Private Sub Worksheet_Calculate()

Application.StatusBar = Me.Range("N1").Value & " - " & Me.Range("P1").Value

End Sub

and see what happens. This is the second best option.

 

[A Dashing Blade]

Here are a few links to good XL coding resources . . .

http://www.cpearson.com/excel/MainPage.aspx
http://www.fontstuff.com/vba/

& these guys are good for real-time data stuff . . .
http://www.willowsolutions.com/tips/tips.shtml

& these guys have a lot of info on optimising spreadsheets (I use their FastExcel professionally)
http://www.decisionmodels.com

 

[grantx]

DB, I think the following can now be ignored. Please see next post.

I’m still unsure as to where I should be looking. Could you please refer to the attachment and direct from there? Is the code placed in the sheet where the current code is (at left)?

Thanks for the ref’s; I’ll chase them up later.

Grant.