Investigating a more object orientated approach to doing formulas, need help

[j.smith1981]

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:

[vba]
Dim testRange

Set testRange = Columns(2).Select

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


testRange Nothing
[/vba]
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:

[vba]
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)"
[/vba]
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]

More robust in what way, where is the problem?

[j.smith1981]

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]

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]

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]

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]

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:

[VBA]
Dim test As Worksheets

test.Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "mysheet"
[/VBA]

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]

Ahh I have it, sorry lol.

[vba]
Dim test As Worksheet
Set test = Sheets.Add
[/vba]

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]

[vba]
Dim test As Worksheets

test.Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "mysheet"
[/vba]

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]

Ah ok then.

[Bob Phillips]

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

[vba]
Dim x As Long

x = 1
Range("A1").Value = Range("A1").Value + x
[/vba]

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]

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

[vba]
Dim x As Long

x = 1
Range("A1").Value = Range("A1").Value + x
[/vba]
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]

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

[vba]
Dim x As Long

x = 1
Range("A1").Value = Range("A1").Value + x
[/vba]
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):

[vba]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
[/vba]
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:
[VBA]
Dim sheetName As String
sheetName = "thesheets real name"
[/VBA]

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]

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]

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]

Doing what then?

[j.smith1981]

Ah here I go lol:

[VBA]
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
[/VBA]

[Bob Phillips]

[vba]

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
[/vba]

[j.smith1981]

[vba]

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
[/vba]

Nice one, thats ace!

Thank you ever so much,
Jeremy.

[j.smith1981]

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.