PDA

View Full Version : Investigating a more object orientated approach to doing formulas, need help



j.smith1981
08-09-2010, 02:27 AM
Hi all,

Just wondering how the best way to make a formula using object orientated programming concerning VBA.

Just wanted to get this theory working, been looking at the PHP equivalent if you like and wondered if theres any way of doing a like minded approach to my macro's in Excel to make them speed up as it where.

Here's the test I have been working with:


Dim testRange

Set testRange = Columns(2).Select

With testRange
.Formula = "=RC[-1]"
End With


testRange Nothing

Is there anyway of making it so if there's say 10 rows of data, in Column A, then only fill up the required rows in the 2nd column (Column B), using an object, then it sets that as nothing when completed.

Just wanted to try and speed up this macro a bit, as its crashing each time at the moment using this command here:


Dim LastRowFirstRunOEM As String

LastRowFirstRunOEM = Cells(Rows.Count, "A").End(xlUp).Row

Range("B1").Resize(LastRowFirstRunOEM).Formula = "=vlookup($A1,'" & ThisWorkbook.Path & "\" & "dataSheet.xls'!xREF1,4,FALSE)"

Just wanted to create a more robust solution to this formula that I could possibly make into a more object orientated approach.

Thanks,
Jeremy.

Bob Phillips
08-09-2010, 02:55 AM
More robust in what way, where is the problem?

j.smith1981
08-09-2010, 03:03 AM
It just crashes when entering that formula, as there's allot of data in the VLOOKUP table and the sheet its entering the formula in.

I believe this to be the reason why its not working, just there's way too much data but really it has to be done this way, is there anyway of getting around this to make this more robust than just using procedural code?

Its quite a lengthly macro I am writing, been writing this for a while and got no where with it thats all.

Aahhh ha lol, woops apologies you know why it was crashing? In the formula its got this workbook.path or whatever yea?

It had a ! in it, obviously causing problems in the code, why it ran the first time then i just made a comment in it.

But it would help immensly if someone could go through this with me and review my code, convert this from procedural to OOP using steps this macro must go through to make this more of an object orientated approach, could someone help me with this?

I dont mind going by examples though, I am more than capable of converting examples into usable code, what I do anyways.

Thanks for your help so far,
Jeremy.

Bob Phillips
08-09-2010, 03:51 AM
Do you actually understand what OOP is and where and how it is applied? You can't just wave a magic OOP wand over procedural code and make it OOP. Sometimes procedural code is that way because it needs to be.

j.smith1981
08-09-2010, 04:17 AM
Yes I do understand what OOP is.

Its basically a way of classifying a blue print, creating objects with attributes based on the blue print then applying methods that allow you to create functionality on the object(s).

Bob Phillips
08-09-2010, 04:43 AM
So what does that have to do with assigning formulae to a set of cells *which is procedural by its nature). What objects do you think need to be created, what properties and methods could be defined to make this simpler?

j.smith1981
08-11-2010, 07:24 AM
Not sure whats going on in my head at the moment, grr sorry xld.

The applying of a formula to cells in a range, has actually been set, not sure why I did not see this before, sincere apologies there.

But I am interested in working out how to make it, more of an object orientated approach, if you can excuse the pun.

But would it be possible to add say a sheet called 'mysheet' then refer to it as an object? I am quite convinced it can be done, I dont see why not and then refer to it as a worksheet later in the application?

I have actually attempted this, but just wanted clarification, as to why this is not working (would be a great hint with later excel macro's I create aswell), here's what I have at least attempted:


Dim test As Worksheets

test.Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "mysheet"


It keeps bringing up the error:



Run-time error: '91':
Object variable or with block variable not set


I'm presuming I have declared the wrong data type for (in this case at least) 'test' data type is wrong, if so what should it be?

Thanks for your help again in advance,
Jeremy.

j.smith1981
08-11-2010, 07:33 AM
Ahh I have it, sorry lol.


Dim test As Worksheet
Set test = Sheets.Add


Ahh it wont allow me to name it with that command so I can then reffer to it in the rest of the application, just thought it would be better to make it more (albeit not completely object orientated, I have heard, well know from what you have said, in previous posts), its just not possible.

But just to make it much easier to follow as an application so to speak.

Any advice is greatly appreciated,
Jeremy.

austenr
08-11-2010, 07:34 AM
Dim test As Worksheets

test.Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "mysheet"


It keeps bringing up the error:



I'm presuming I have declared the wrong data type for (in this case at least) 'test' data type is wrong, if so what should it be?

Thanks for your help again in advance,
Jeremy.

take off the test. in your code. For that matter you dont need to declare the variable either.

j.smith1981
08-11-2010, 07:51 AM
Ah ok then.

Bob Phillips
08-11-2010, 07:55 AM
I am sure I once found a way to create a worksheet variable and name the worksheet at the same time, but I may just be imagining that because I have tried many variations since and I cannot get it.

But again, that is not object oriented, no way, no how. Even the one step approach, if we could do it, would not be OO as such. That is just good programming disciplines, setting a variable, and using the variable throughout. You could apply exactly the same technique to simple constants, such as


Dim x As Long

x = 1
Range("A1").Value = Range("A1").Value + x


I am sure that you wouldn't claim that to be OO.

OO is when your code is acting upon and responding to actions of an object or objects. Using VBA is somewhat OO by the very nature of interacting with the hosting product's (say Excel) object model, but just writing code that interacts with that OM in a different way does not make it more or less OO.

j.smith1981
08-11-2010, 08:02 AM
I am sure I once found a way to create a worksheet variable and name the worksheet at the same time, but I may just be imagining that because I have tried many variations since and I cannot get it.

But again, that is not object oriented, no way, no how. Even the one step approach, if we could do it, would not be OO as such. That is just good programming disciplines, setting a variable, and using the variable throughout. You could apply exactly the same technique to simple constants, such as


Dim x As Long

x = 1
Range("A1").Value = Range("A1").Value + x

I am sure that you wouldn't claim that to be OO.

OO is when your code is acting upon and responding to actions of an object or objects. Using VBA is somewhat OO by the very nature of interacting with the hosting product's (say Excel) object model, but just writing code that interacts with that OM in a different way does not make it more or less OO.

Oh no I can fully appreciate that, just in my head it works out, but obviously not in excel.

Going to give this one more bash myself, if not I will remain corrected

Thanks ever so much for your help though, saved me allot of frustration (in a way of course lol),
Jeremy

j.smith1981
08-12-2010, 04:47 AM
I am sure I once found a way to create a worksheet variable and name the worksheet at the same time, but I may just be imagining that because I have tried many variations since and I cannot get it.

But again, that is not object oriented, no way, no how. Even the one step approach, if we could do it, would not be OO as such. That is just good programming disciplines, setting a variable, and using the variable throughout. You could apply exactly the same technique to simple constants, such as


Dim x As Long

x = 1
Range("A1").Value = Range("A1").Value + x

I am sure that you wouldn't claim that to be OO.

OO is when your code is acting upon and responding to actions of an object or objects. Using VBA is somewhat OO by the very nature of interacting with the hosting product's (say Excel) object model, but just writing code that interacts with that OM in a different way does not make it more or less OO.
Ahh thanks I will try and get that to work, its fun doing this work really.

I have actually created a function that creates a sheet, then calling that at 3 specific points in the sub routine, each with a different variable name, here we go (it works aswell):

Function createSheets(sheetName) 'Procedural version of making a sheet

'Dim sheetName As String
'sheetName = "mySheet"
Dim costpricesOWsh As Worksheet

On Error Resume Next
Set costpricesOWsh = ActiveWorkbook.Worksheets(sheetName)
'On Error GoTo 0
If costpricesOWsh Is Nothing Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetName
Else
'If costprices does exist then delete it and add it again
costpricesOWsh.Delete
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetName
End If

End Function


Sub createSheet()
Dim sheetName As String
sheetName = "costprices"
Call createSheets(sheetName)
End Sub

Thats more based on OO though, probably not brilliant but it is more OOP based, though generally VB anyway (minus the .net language), is mostly procedural anyways.

Just thought I would share, all I would have to do is change sheetName to a new value in each sub routine.

But if I was to do this each time, how would I stop from having to use:

Dim sheetName As String
sheetName = "thesheets real name"


Each time?

It surely would be possible to allow this to be run just delcaring the value to the variable sheetName wouldnt it?

Any feedback about that would be great.

Thanks and I look forward to any reply,
Jeremy

Bob Phillips
08-12-2010, 05:15 AM
Not sure what you are asking. You define the sheet name in a single place and pass it as a parameter to the create sub. That seems a good practice to me, although I would use a function that returns the worksheet object.

What would you prefer, leaving that sheet name in memory, clogging it up?

j.smith1981
08-12-2010, 05:19 AM
How would I go about doing that then?

I did though state that what I did was probably not the best way with going about OOP with VBA.

Bob Phillips
08-12-2010, 05:50 AM
Doing what then?

j.smith1981
08-12-2010, 07:04 AM
Ah here I go lol:


Function createSheets(sheetName)

Dim costpricesOWsh As Worksheet

On Error Resume Next
Set costpricesOWsh = ActiveWorkbook.Worksheets(sheetName)
'On Error GoTo 0
If costpricesOWsh Is Nothing Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetName
Else
'If costprices does exist then delete it and add it again
costpricesOWsh.Delete
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetName
End If

End Function


Sub test()



Call createSheets("costprices")
Call createSheets("costprices1")
Call createSheets("costprices2")
End Sub

Bob Phillips
08-12-2010, 07:31 AM
Function createSheets(sheetName) As Worksheet

Dim costpricesOWsh As Worksheet

On Error Resume Next
Set costpricesOWsh = ActiveWorkbook.Worksheets(sheetName)
'On Error GoTo 0
If costpricesOWsh Is Nothing Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetName
Else
'If costprices does exist then delete it and add it again
costpricesOWsh.Delete
Set costpricesOWsh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
costpricesOWsh.Name = sheetName
End If

Set createSheets = costpricesOWsh
End Function


Sub test()
Dim shPrices As Worksheet
Dim shPrices1 As Worksheet
Dim shPrices2 As Worksheet

Set shPrices = createSheets("costprices")
Set shPrices1 = createSheets("costprices1")
Set shPrices2 = createSheets("costprices2")
End Sub

j.smith1981
08-12-2010, 07:43 AM
Function createSheets(sheetName) As Worksheet

Dim costpricesOWsh As Worksheet

On Error Resume Next
Set costpricesOWsh = ActiveWorkbook.Worksheets(sheetName)
'On Error GoTo 0
If costpricesOWsh Is Nothing Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetName
Else
'If costprices does exist then delete it and add it again
costpricesOWsh.Delete
Set costpricesOWsh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
costpricesOWsh.Name = sheetName
End If

Set createSheets = costpricesOWsh
End Function


Sub test()
Dim shPrices As Worksheet
Dim shPrices1 As Worksheet
Dim shPrices2 As Worksheet

Set shPrices = createSheets("costprices")
Set shPrices1 = createSheets("costprices1")
Set shPrices2 = createSheets("costprices2")
End Sub


Nice one, thats ace!

Thank you ever so much,
Jeremy.

j.smith1981
08-13-2010, 12:58 AM
Got it working really well now.

But is there any way of making a macro transfer data into say 'costprices' sheet, from a pricelist.csv file (literally is the name of the file), without using the clip board?

I believe this to be one of the main reasons why the previous one I did was dead slow.

Thanks in advance for any replies,
Jeremy.

Bob Phillips
08-13-2010, 01:44 AM
Just open the CSV file as if it were another workbook, and select the data and copy it in bulk (and don't you dare mention OO :))

j.smith1981
08-13-2010, 02:43 AM
I have managed to get this working though.

Sheets("sheetTest").Range("A1") = Range("A1")
Obviously it only applies one cell though to another sheet, not a workbook.

Any ideas?

Going to keep going with this though, its got quite a few rows has the pricelist.csv file, well over 1,000, so want to preferably do this without using the clipboard at all.

OO is fun, why not? LOL

Nah I wont mention OO again, just you can implement some standards of OO in VBA but its like the old v4 of PHP, not the best way of doing things, but it allows you to keep reusing code, edited the creation of worksheets, so it applies that name, as a set, so in the function (not as oppose to what you suggested doing it in the sub routine), I created a function that sets the variable of that to the worksheet, so whatever the worksheets name is, thats what the objects called.

Its fun developing OOP applications, since what I am doing will require allot of reusable code, the whole point of OOP is to stop retyping code, boggs down the application.

Thanks in advance again,
Jeremy.

PS The reason being is the macro I used before, gets way clogged up with memory it crashes, thats why I am going for functions as much as possible and trying to limit the use of copy paste actions, so it stays as clean as a whistle (why I have asked about this essentially).

Its fine using copy and paste for a few rows, but for 7,000 rows of data in the CSV pricelist file, its far too much to use copy and paste.

Suppose logically I could just clear the clipboard and see how it behaves with that, might make things allot easier.

Bob Phillips
08-13-2010, 03:03 AM
I have managed to get this working though.

Sheets("sheetTest").Range("A1") = Range("A1")
Obviously it only applies one cell though to another sheet, not a workbook.

Any ideas?

Going to keep going with this though, its got quite a few rows has the pricelist.csv file, well over 1,000, so want to preferably do this without using the clipboard at all.

As I said, copy it in bulk.



wsCSV.Rows(1).Resize(somenumber) Worksheets("sheetTest").Range("A1")



OO is fun, why not? LOL

Nah I wont mention OO again, just you can implement some standards of OO in VBA but its like the old v4 of PHP, not the best way of doing things, but it allows you to keep reusing code, edited the creation of worksheets, so it applies that name, as a set, so in the function (not as oppose to what you suggested doing it in the sub routine), I created a function that sets the variable of that to the worksheet, so whatever the worksheets name is, thats what the objects called.

I agree, OO is good, and where appropriate, is a great approach. But nothing you have raised so far is OO. You are using the object model, you have no choice, but you haven't defined your own objects, raised or reacted to object events at all.


Its fun developing OOP applications, since what I am doing will require allot of reusable code, the whole point of OOP is to stop retyping code, boggs down the application.

Thanks in advance again,
Jeremy.

No, that is not the point of OOP at all. That is just good programming practices, off-loading common functionality to their own procedures. Modular, reuasable code was a desirable paradigm a long time before OO came onto the scene.


PS The reason being is the macro I used before, gets way clogged up with memory it crashes, thats why I am going for functions as much as possible and trying to limit the use of copy paste actions, so it stays as clean as a whistle (why I have asked about this essentially).

Its fine using copy and paste for a few rows, but for 7,000 rows of data in the CSV pricelist file, its far too much to use copy and paste.

I cannot see why 7,000 clogs memory, if you were doing it well it would process one, then move onto the next. Memory would be reused, not grabbed anew. It might take time, but it should not clog up memory.

Paul_Hossler
08-14-2010, 04:51 PM
But is there any way of making a macro transfer data into say 'costprices' sheet, from a pricelist.csv file (literally is the name of the file), without using the clip board?


You could always read the file one line at a time, parse the CSV line with Split() and process each piece as needed. Example from the help for EOF:


Dim InputData
Open "MYFILE" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
Line Input #1, InputData ' Read line of data.
Debug.Print InputData ' Print to the Immediate window.
Loop
Close #1 ' Close file.


Paul