Converting Excel Formula Into VBA

[Excel_VBA_User]

Hi Everyone,

I'm working with an ever-growing spreadsheet that I'm using to parse weather data strings from the internet. At the moment the spreadsheet contains 100,000 lines of data and 50+ columns, each with Excel formulas like the following to extract key bits of data:



=IFERROR(IF(LEFT(J3,3)="---",TRIM(MID(J3,FIND(CHAR(1),SUBSTITUTE(J3," ",CHAR(1),2)),LEN(J3))),TRIM(MID(J3,FIND(CHAR(1),SUBSTITUTE(J3," ",CHAR(1),3)),LEN(J3)))),"")



This Excel formula looks at a data string cell J3 (down to J100,000) and removes a leading portion. The resulting substring is printed in cell K3. Here are two sample strings that I'm working with:



10Oct15 NASA1 2 Miami 23 23.5 25 26 27 28 28 27.9 27.8 29 .....

--- NASA15 12 Tampa 24 24 24 25 25.1 26.2 27 27.1 27.1 27.2 .....



The Excel formula generates the following desired results:



Miami 23 23.5 25 26 27 28 28 27.9 27.8 29 .....

Tampa 24 24 24 25 25.1 26.2 27 27.1 27.1 27.2 .....



Because each cell in my spreadsheet contains a similar Excel formula, the file is 100mb+ and very unstable. I have tried various techniques to reduce the file size but it keeps ballooning back up to 100mb+. I looked into VBA options and started by recording the following macro:

Sub TestRecording()

'

Dim rowNum As Integer

Set UsedRng = ActiveSheet.UsedRange

LastRow = UsedRng(UsedRng.Cells.Count).Row

Range("K3").Select

    Do Until ActiveCell.Row = LastRow + 1

            ActiveCell.FormulaR1C1 = "=IFERROR(IF(LEFT(RC[-1],3)=""---"",TRIM(MID(RC[-1],FIND(CHAR(1),SUBSTITUTE(RC[-1],"" "",CHAR(1),2)),LEN(RC[-1]))),TRIM(MID(RC[-1],FIND(CHAR(1),SUBSTITUTE(RC[-1],"" "",CHAR(1),3)),LEN(RC[-1])))),"""")"

        ActiveCell.Offset(1, 0).Select

    Loop





End Sub

This code produces the correct result, but it writes the Excel formula into the cell. Hence, the ballooning file size issue remains. I'm looking for help/guidance to convert the above Excel formula into a VBA functions with nested IFs etc. that just prints the result in corresponding cell in column J. Once I have a better understanding of how to rewrite the Excel formula as a VBA, I'll be able to tackle the remaining formulas in my spreadsheet. Many thanks in advance for your help!!

 

[sheetspread]

evaluate() around the formula will give just the result, and you can turn off the screen updating to speed up the code. If you want it even faster, write the formula in VBA (Instr, Regex, etc.) to an array like the scripting dictionary then transfer it all to your spreadsheet in a single step.

 

[My Aswer Is This]

Using Vba does away with entering all the formulas in the cells in a lot of cases.

For example it we want to want the result of adding the values of Range("A2")+Range("A3") to be put in Range("A1") we do not need to put a formula in Range ("A1")

We just write some Vba code like this:

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

Can we not do the same in your situation?
Do you really need the formula in the cell?
Why not just have Vba put the results of the formula in the cell.

If that would work for you show us a better example of what the data looks like in the beginning and what you want it to look like after modifying it.

I got confused see all the ".........." stuff. Did you really want "......."
It looks like we are just trimming off some data.

 

[Excel_VBA_User]

Thanks for your quick response, sheetspread. Is the evaluate option best used in the Excel formula or the VBA code above? I tried adding to the VBA but may have been writing the code in the wrong place.

 

[sheetspread]

You put evaluate() around the formula in quotes (in VBA) which returns the result. See if it works, but the R1C1 notation may have to be changed back to A1.

 

[Excel_VBA_User]

Thanks My Aswer Is This. Writing VBA code to trim and extract bits of data is exactly what I have in mind. I don't need the formulas in the cell, just the result is the goal. I added the "...." in the above examples because the data strings that I'm working with are often 100-120 characters long with a lot of variability. The first half of each string is fairly consistent - i.e. with the similar dividers and characters. For example:

31Oct15 12NEV1 11 Reno (Nevada, Washoe) 1014 SW 14 2 25/5 25/4 256 25 5/6 25 5/6 1:00 FEW075TCU BKN100 BKN100 OVC200
1Nov15 1NEV12 1 Las Vegas (Nevada, Clark) 999 W 9 11 25/5 25/4 256 25 5/6 25 5/6 12:30 FEW060 BKN080 BKN130 OVC200
--- 10NEV11 1 Black Rock Desert (Nevada, Humboldt) 1012 W 12 1 25/5 26/3 27/6 27 5/6 25 5/6 0:00 FEW080 BKN130 RAN122 OVC200

Using the first string as an example, the parts that I'd like to extract using VBA are:

10OCT15 - the data on which the weather reading was taken. Ideally, I'd like to rewrite the date as "October 15, 2015". Note the reset reading for each day starts with "---" instead of the actual date.
NEV - the state code
Reno - the city in which the reading was taken
Washoe - the county
Nevada - the state
1014 - the barometric pressure reading
SW - the wind direction
14 - the wind speed
2 - reading # for that day
11:30 - the time of the reading

I'd also like to save the middle portion "25/5 25/4 256 25 5/6 25 5/6" into a column as well. I have lengthy IF AND Excel formulas that extract and organize the data from these substrings. That'll be round two of this project because the middle portion varies greatly from one reading stations to the next. Once I have a better handle on how to convert my Excel functions to VBA code, I'll work on extracting the key bits of information that I need from the "25/5 25/4 256 25 5/6 25 5/6" portion. Lastly, I don't presently have a need for the portion after the time (i.e. FEW075TCU BKN100 BKN100 OVC200). So, this portion can be deleted.

Hope this helps. I'm happy to whittle my way through the extraction process one by one. But if you have a way to extract the above portions in one step, that would be a huge help. Many thanks again for your help.

 

[Excel_VBA_User]

Hi Sheetspread, As you suspected, the addition of evaluate () did not work with the R1C1 code. So, I'm working to convert the R1C1 to A1 and will keep you posted. Cheers David.

 

[Perpa]

Hi Sheetspread, As you suspected, the addition of evaluate () did not work with the R1C1 code. So, I'm working to convert the R1C1 to A1 and will keep you posted. Cheers David.

Excel_VBA_User,

The code below copies your formula into cells in column W, then converts those formulae into 'values' using pastespecial.
You can change where you want the values to end up if not in column W, but your will also need to change the 'offset' accordingly.
Hope this is helpful.
Perpa

Sub Test()

    Dim LastRow, n As Long

    Set UsedRng = ActiveSheet.UsedRange
    LastRow = UsedRng(UsedRng.Cells.Count).Row
    Range("J3").Select
'The next line places the formula in column W row 3
    ActiveCell.Offset(0, 13).Formula = "=IFERROR(IF(LEFT(J3,3)=""---"",TRIM(MID(J3,FIND(CHAR(1),SUBSTITUTE(J3,"" "",CHAR(1),2)),LEN(J3))),TRIM(MID(J3,FIND(CHAR(1),SUBSTITUTE(J3,"" "",CHAR(1),3)),LEN(J3)))),"""")"
    
    Do Until ActiveCell.Row = LastRow
        ActiveCell.Offset(0, 13).Copy ActiveCell.Offset(1, 13)
        ActiveCell.Offset(1, 0).Select
    Loop
 'These next lines convert the formulas in Column W to values  
    LastRow = UsedRng(UsedRng.Cells.Count).Row
    Range("W3:W" & LastRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

'This last For/Next removes any rows where there are blanks in column W - remove if not needed
    
    For n = LastRow To 3 Step -1
        If Cells(n, "W") = "" Then
            Cells(n, "W").EntireRow.Delete
        End If
    Next n
        
    Cells(LastRow + 1, "W").Select
    
End Sub

 

[sheetspread]

Here's a slightly different way to loop and build the formula:

Excel 2010
	A	B
1	Number	Formula
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
Sheet3

Sub formulaplace()
Dim i%, lr%, QQQ As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
QQQ = Cells(i, 1).Address(0, 0)
Cells(i, 2).Value = "=IFERROR(IF(LEFT(" & QQQ & ",3)=""---"",TRIM(MID(" & QQQ & ",FIND(CHAR(1),SUBSTITUTE(" & QQQ & ","" "",CHAR(1),2)),LEN(" & QQQ & "))),TRIM(MID(" & QQQ & ",FIND(CHAR(1),SUBSTITUTE(" & QQQ & ","" "",CHAR(1),3)),LEN(" & QQQ & ")))),"""")"
Next
End Sub

Excel 2010
	A	B
1	Number	Formula
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
Sheet3

Cell Formulas
Range	Formula
B2	=IFERROR(IF(LEFT(A2,3)="---",TRIM(MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),2)),LEN(A2))),TRIM(MID(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),3)),LEN(A2)))),"")

If you change the third to last line to:

Cells(i, 2).Value = evaluate("=IFERROR(IF(LEFT(" & QQQ & ",3)=""---"",TRIM(MID(" & QQQ & ",FIND(CHAR(1),SUBSTITUTE(" & QQQ & ","" "",CHAR(1),2)),LEN(" & QQQ & "))),TRIM(MID(" & QQQ & ",FIND(CHAR(1),SUBSTITUTE(" & QQQ & ","" "",CHAR(1),3)),LEN(" & QQQ & ")))),"""")")

you'll get the result instead of the formula.

 

[Excel_VBA_User]

Fantastic! Sheetspread, your solution worked perfectly. I have been trying to figure out the correct use of the evaluate() function today but couldn't quite get it to work. And I have about 30 more columns with similar Excel formulas that I can now wrap in macros to finalize my string parsing in a handful of steps. Many thanks for your help and insight!!! Cheers David.