VBA instead of formula

Topic

New Reply

I’m doing some “code culling” with my current project.
In some cases where I required code for a temporary calculation, I would use a formula, record macro of that formula, then use the macro to do the same calculation, then use another line of code to keep the Sum, but remove the formula because it’s no longer required within that particular Loop.

For example,

Sub sumthis ()
‘ lines of code
Sheets(“test1”).Range(“D1”).FormulaR1C1 = “=SUM(R4C9:R28C9)” ‘ adds formula
Sheets(“test1”).Range(“D1”).Value = Sheets(“test1”).Range(“D1”).Value ‘ leaves Sum but no formula

‘more lines of code

End sub
actual formula: =SUM($I$4:$I$28)

Is there a way of writing the code: FormulaR1C1 = “=SUM(R4C9:R28C9)”
so it sums that range/column and have the result only in cell D1 so I can remove 1 line of code?

Thanks

Reply | Quote

Replies

Hi

Perhaps you can use something like:
[D1] = Application.Sum(“i4:i28”)

zeddy

Reply | Quote

zeddy.
On test it showed in cell D1; #VALUE!

test macro was

Code:

Sub testaddintion()

[D1] = Application.Sum(“i4:i27”)

End Sub

Reply | Quote

Hi

OOps!

Try:
[D1] = Application.Sum(Range(“i4:i27”))

zeddy

Reply | Quote

Thanks zeddy, that worked,
but, it now needs to get “specific”

I tried improvising to make it work like;

test code

Code:

Sub testaddintion()

Sheets(“Sheet1”).Range(“D1”).ClearContents ‘ used for testing
[D1] = Application.Sum(Range(“i4:i27”)) ‘ works OK

‘———————————————————

Sheets(“Sheet1”).Range(“D1”).ClearContents ‘ used for testing

‘ to be specific
[B]Sheets(“Sheet1”).Range(“D1”) = Application.Sum(Range(Sheets(“Sheet1”).Range(“I4:I27”)))[/B]

End Sub

There is no syntax error, but it does score a,
Run time error 9. subscript out of range.

Can the code be so it’s specific for Sheet1 and Cell Range ?

Thanks

Reply | Quote

Hi

The code can be specific to another opened workbook if you really want!
e.g.
‘Set zSource = Workbooks(zDataFilename).Sheets(1) ‘use first sheet in datafile
‘Set zDest = Workbooks(zMergeFile).Sheets(“Stock”) ‘<<adjust sheetname if required!

In your case, I think you could use this:

Set zDest = sheets("Sheet1")
Set zSource = sheets("Sheet1")

zDest.[D1] = Application.Sum(zSource.Range("i4:i27"))

..assuming you have a sheet named "Sheet1" that is. If this named sheet doesn't exist, that will give you a Run time error 9. subscript out of range.

zeddy

Reply | Quote

And expect a reply from RG shortly.

zeddy

Reply | Quote

Alan,

Minor syntax adjustment:

Code:

Sub TestAddition()

Sheets("Sheet1").Range("D1") = Application.Sum(Range("Sheet1!I4:I27"))

End Sub

This also works:

Code:

Sub TestAddition()

Sheets("Sheet1").Range("D1") = WorksheetFunction.Sum(Range("Sheet1!I4:I27"))

End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks RG and zeddy

from the smorgasbord of options,

Code:

Sub medium_rare()

Sheets(“Sheet1”).Range(“D1”) = Application.Sum(Range(“Sheet1!I4:I27”))

End Sub

will do for now.

zeddy, your option will be applied when I get to that phase probably sooner than later cos this project is nearing the “split the workbooks” phase.

Reply | Quote

Hi

Just to further add to the mix, you can use Sheets(“Sheet1”) to refer to a specifically named sheet, or, in my opinion, a nice trick is to directly use the vba code name for the sheet e.g.
Sheet1.Range(“D1”)
..this means the User can change that particular tab sheet from “Sheet1” to “Fred”, but the vba code name doesn’t change, i.e. it is still Sheet1, so you don’t need to update the vba code.
You can also easily change the vba code sheet name for any Sheet, using the vba properties window (F4) and selecting that particular sheet in the Project Explorer window (Ctrl+R)

The reason you might want to do this is if you have say, Spanish versions of your file. You can have different (Spanish) tab names, but the code doesn’t need changing.

Just thought you might like to know that.

zeddy

Reply | Quote

Just for the sake of statistical trivia, this solution has eliminated 205 characters, ( including spaces within the syntax) of code.

Multiply 205 on average by a conservative 10 = 2050 parts of VBA space, or “excess code weight” being culled.
When the workbook is zipped and saved as a backup, currently it’s 356KB

Reply | Quote

Just thought you might like to know that.

zeddy

yes, good point….

Reply | Quote

Hi

..have you tried saving it in .xlsb format????
What size is it as .xlsb???
I reckon it would be smaller, or at least comparable, to your zipped size.
The .xlsb format is my preferred Excel filetype.
You can include macros in .xlsb files.
Smaller file size = faster loading on networks

zeddy

Reply | Quote

zeddy,
Never heard of xlsb format. (when googled)
results
╭──────────────╥────
│ ║ .xlsx │ .xlsb │
╞══════════════╬═════
│ loading time ║ 165s │ 43s │
├──────────────╫───────
│ saving time ║ 115s │ 61s │
├──────────────╫───────
│ file size ║ 91 MB │ 65 MB │
╰──────────────╨───────
hmm, something to consider
Thanks
XPDH

Reply | Quote

Another abbreviated solution would be:

Code:

[d1] = WorksheetFunction.Sum([c2:c6])

Reply | Quote

..or, perhaps

[d1] = Application.Sum([c2:c6])

zeddy

Reply | Quote

Hi XPDH

The .xlsb file format is a binary file format available since Excel 2007.
It is designed for speed and compactness.

Your google results show significant time savings with saving and loading.
The savings get even more better with very complex, large files e.g. > 1MB
I have been using .xlsb format for years without any problems.
It helps reduce network traffic.

All other .xlsx formats etc are designed for XML compatibility with other programs etc.
But who uses any other program but Excel?????

zeddy

Reply | Quote

Thanks zeddy,

Maudibe, no need for apologies, it’s the way I try to illustrate a question that probably clutters the question itself ! my apologies.

so the revised question is,
can this formula, (F4-D4)/ABS(D4) be coded so it calculates from row I4 to I27 in a similar way
the previous method of the Sum Formula

Sheets(“Sheet1”).Range(“D1”) = Application.Sum(Range(“Sheet1!I4:I27”))’ previous Sum Formula

if I try
Sheets(“Sheet1”) = Application.(F4-D4)/ABS(D4)(Range(“Sheet1!I4:I27”))
it errors.

Thanks

Reply | Quote

Hi XPDH

You could use this:

Code:

Sheets("Sheet1").select
[i4] = "=(f4-d4)/abs(d4)"
[i4].copy [i4:i27]
[i4:i27] = [i4:i27].value

zeddy

Reply | Quote

Hi XPD

..and to cancel the highlighted copy-to range after the last bit of code above, just follow with this vba line:
Application.CutCopyMode = False

zeddy

Reply | Quote

Maudibe, zeddy and RG thanks,

I think this wraps up my query on other ways to do VBA formula options.

All replies will serve a purpose, it’s cleared up what I was hoping.
Maud, your reply

Code:

Sub FORMULA_a()
Dim cell As Range
    With Worksheets(“Sheet1”)
        For Each cell In .[i4:i28]
            With cell
            cell = (.Offset(0, -3) – .Offset(0, -5)) / Abs(.Offset(0, -5))
            End With
        Next
    End With
End Sub

makes better sense now after a few test runs,

Thanks

Reply | Quote

Hi XPDH

..see attached file for demo of my simpler version.

[i4] = “=(f4-d4)/abs(d4)”
..is a lot easier to understand than
cell = (.Offset(0, -3) – .Offset(0, -5)) / Abs(.Offset(0, -5))
..when you are reading vba code, especially if you come back to it a month later.
My method does the job ‘in one go’ rather than looping through each cell in a range, so is MUCH quicker.

This method shows how easy it is in vba to enter any excel formula into a cell, copy the formula to a specified range, and then convert the formulas (if required) into values only.
You really need to avoid using loops in vba wherever possible, to get the fastest performance.

zeddy

Reply | Quote

Just noticed your reply maud, thanks.

Well, I have converted the main project workbook to xlsb, and it made a very noticeable difference.

Ok, the next formula is a bit different, but it needs “culling” to.
BTW, coding with square brackets is now new to me, I guess I’l pick up on it in good time.

I’ve posted this workbook, it better illustrates the process with a sample macro.

The actual formula is: =(F4-D4)/ABS(D4)
But to do a range I use:
Selection.Offset(1, 0).Resize(24, 1).FormulaR1C1 = “=(RC[-3]-RC[-5])/ABS(RC[-5])”
and then to just have value only I use:
Selection.Offset(1, 0).Resize(24, 1).Value = Selection.Offset(1, 0).Resize(24, 1).Value

Same question, how can I do this like the previous Sum formula ?

The entire macro is:

Code:

Sub FORMULA_a()
     Sheets(“Sheet1”).Select
      Sheets(“Sheet1”).Range(“I3”).Select
       Sheets(“Sheet1”).Range(“I4:I28”).ClearContents
       
‘FORMULA
‘=(F4-D4)/ABS(D4)
Selection.Offset(1, 0).Resize(24, 1).FormulaR1C1 = “=(RC[-3]-RC[-5])/ABS(RC[-5])”
Selection.Offset(1, 0).Resize(24, 1).Value = Selection.Offset(1, 0).Resize(24, 1).Value
End Sub

Thanks

Reply | Quote

XP,
This should achieve what you want

HTH,
Maud

Code:

Sub FORMULA_a()
Dim cell As Range
    With Worksheets(“Sheet1”)
        For Each cell In .[i4:i28]
            With cell
            cell = (.Offset(0, -3) – .Offset(0, -5)) / Abs(.Offset(0, -5))
            End With
        Next
    End With
End Sub

Reply | Quote

Thanks

maud,

This should achieve what you want

yes and no, the macro would be ok if it was on it’s own in the process of things.

But it’s part of many lines of code before and after that formula.
To use another Sub and End Sub just for that column adds more coding.

What I am looking for is hopefully to replace entirely what I am using to obtain the same calculation, and have that calculation for that range in one line of code/ syntax.

When I used the “Selection.Offset(1, 0).Resize(24, 1).” method, it was simply done to do away with previous recorded macro. Initially it was, Copy and Paste Special / Formula, then again, Copy and Paste Special / Value, to remove the formula.

The idea to use offsets came from when arrow keys can’t be recorded and cell range selection could not be identified by direct Cell A1 method.

For the record
Arrow Keys
”’Selection.Offset(0, 1).Select ‘*Right*
”’Selection.Offset(0, 2).Select ‘*Right 2 cells*

”’Selection.Offset(0, -1).Select ‘*Left*
”’Selection.Offset(-1, 0).Select ‘*Up*
”’Selection.Offset(1, 0).Select ‘*Down*

”’Selection.Offset(2, 0).Resize(5, 8).Select ‘*Down* selects range of cells,
hence: Selection.Copy

Reply | Quote

Hi XPDH

Using square brackets for coding can simplify things, takes less typing, is easy to follow once you start using it.
So, as you have seen previously above, you can use things like [i4:i28] to refer to a block of cells.
If you don’t specify a particular sheet, vba assumes you mean the current active sheet that is present (so make sure you know what sheet you’re on!).
You can also refer to named ranges like this: [blockCats], [blockDogs] etc.

I frequently put a copy of a set of formulas in the first row of a spreadsheet.
These formulas typically are used in the same columns, for records below, say in rows 11 to 200
I name the formula cells like this: [formulasRow1] e.g. referring to [i1:p1]
I can then use vba like this..
[formulas.row1].copy [i11:p200]
..I can then ‘convert’ the formulas to values only, via vba, like this..
[i11:p200] = [i11.p200].Values

Generally speaking, for the fastest vba operation, you should avoid selecting cells wherever possible.
And certainly try and avoid doing this in a loop on a cell-by-cell basis.
Each time you select a cell or have vba read from or write to a cell takes time.
So try doing it in one go.

zeddy

Reply | Quote

Hi

The blobby face in my previous post is the double-dot colon and (example) letter ‘p’ i.e. ‘:’ p
zeddy

Reply | Quote

But it’s part of many lines of code before and after that formula.
To use another Sub and End Sub just for that column adds more coding.

Sorry XP. Just following the same Sub FORMULA_a()…End Sub routine from your post and your uploaded workbook.

Reply | Quote

XP,

Will the sheet containing the data always be the active sheet when to code runs? Assumptions are being made.

Reply | Quote

Maud,

The data can change up to 3 times a minute during the cycle of a web query.
So it’s perpetually looping Until criteria is met.
My layman’s term is to think of it as a “market scanner”, checking prices as they change in real time.

zeddy,
I see now how your method works, I have “culled” another 3 lines of code to 1 line of code.
Sheets(“Sheet2”).Range(“F1”) = Application.Count(Range(“Sheet2!A3:A26”))

I now “get” this, predetermined excel functions?
Application.Sum
Application.Count

You really need to avoid using loops in vba wherever possible, to get the fastest performance.

It has to web query constantly in real time.

Thanks

Reply | Quote

Hi XPDH

You can certainly use a loop to web query in real time ( I would use the Application.OnTime method to refresh every, say, 2 seconds?).
And loops are certainly very important and powerful in vba.
What I meant to say was to try and avoid using loops in vba to update a block of cells on a cell-by-cell basis if you want fastest performance.

zeddy

Reply | Quote

zeddy,
yes, Application.OnTime is in fact in the works when the project is completed.
What that would do is update the daily event list at #~AM. I haven’t determined exactly when yet.

To do a Application.OnTime instead of Loop at this stage of development will mean a re-think of it’s current architecture. I guess I’ll look at that option once I get this thing to a point where it’s working right, storing, statistics for analysis.

XP

Reply | Quote