Variables or Formulae - A question of speed

[Absolution]

Dear all

I am about to venture forward into a new sheet, but I am wondering if you could help me resolve a query first.

The main event is big. (Almost like space). I am going to be using a lot of formulae, most of which will be taking text from a large text string.

Would the better programmer stay away from naming a hundred and one variables which will just be used a few times each, or would it be more sensible to name these and then the code looks much more organised ?

Look forward to your opinions.

PS I wasn't kidding when I said hundred, I currently have an excel spreadsheet doing this and it is immense, hence the need to write it in VBA.

 

[Norie]

Could you actually give us a clue about what you want to do?

Have you considered arrays?

 

[Absolution]

What the event is doing is searching for information in a long mish mash of a text string and then taking information out of it and putting this into nice neat columns in a table.

I have to use the Instr function to find where the information starts and ends, and this is the question.

Is it better to set a variable, eg:

dim startofbit as long
dim endofbit as long
dim result1 ad string

startofbit = instr(Blah blah blah)
endofbit = instr(blah2 blah2 blah2)

result1 = mid(mishmashtext,startofbit,endofbit-startofbit)

range("sheet1!b"&a).value = result1

since there are going to be tonnes of those, is it not then better to just put the formula straight into the whole thing and make it look a little messy, or is the above not going to affect speed at all either way I do it ?

Sorry I didn't post too much in depth before, I just thought I was asking a fairly basic programming question.

I haven't considered arrays as I am in the learner seat with regards to the VBA side, I will look into that to see if it is an option.

 

[Norie]

Have you actually considered Data>Text to Columns...?

I suppose this is a simple programming question but without seeing an example of the data and the required result it's kind of hard to advise.

Can you post an example of your data and required result, perhaps using Colo's HTML Maker?

 

[Absolution]

I would post it but we are talking of text from Html pages which end up around 50-80 pages long, so it wouldn't really do much than clog up the page. there are around 20 different phrases I am looking for, so Text to columns doesn't work that well for me (Along with the fact there are not enough columns.) I have got a page which works at the moment using formula. I am just looking to speed up this task as I have to search through and copy it right now.

I appreciate what you are saying about the fact it is hard to advise, I was just hoping there was an answer similar to "Try to avoid volatile funtions in formulae as it clogs up the system" type stuff.

 

[Norie]

Could you at least post a small example and more information on what you actually want to do?