Converting Excel Function to VBA

[BozzA]

Hi Guys,

I am just wondering if there is an easy way to convert excel functions into VBA code. To be more specific this is the code I am trying to convert

Please Login or Register  to view this content.

Sheet1.cells(1,1).formula = THAT^^

I have seen the application.index used before but for some reason I cant get it to work easily, is there some automatic way of converting it?

Thanks

[Andrew-R]

Application.WorksheetFunction.Index might be the way to go.

[royUK]

Why would you want to? Excel functions are generally faster than VBA.

What are you trying to do?

[BozzA]

Well that code is used to compare 2 columns within a table and return its result from the 3rd column when found.
I am creating a program in a spreadsheet that can do this automatically, which i have already done, I just wanted this exact same function code in VBA code and was wondering if theres an easy way to do it thats all. My attempt at converting it to VBA code is below, is this correct?

Please Login or Register  to view this content.

[rylo]

Hi

Do you want VBA to generate the formula, or do you want to put the result of the formula in a cell???

rylo

[BozzA]

Hey,
i want to place this into my code, i have the VBA function version which works and i usually try first before writing up the VBA code for it, but I cant seem to place this specific code into my program correctly. Error occurs, I just want to know if i did it correctly

[rylo]

Hi

How about using the evaluate function.

Please Login or Register  to view this content.

rylo

[BozzA]

Hi,

My only problem with that is my reference cells in that code are D30 and E30, which is ok when pasted in the workbook because it will change as you drag it across cells, but not good when you place it in VBA as a loop as it would not service it correctly.

If you look at my attempt in changing it into VBA code in post #4 you will see I have replaced it (E30) with [sheet2.cells(x,A)] x & A are defined elsewhere in my code but I have all that sorted out,

Its just that line i need someone to review if possible

[rylo]

Hi

Why rebuild the formula every time. Put it into one cell that is fixed, then either fill the range you want, or copy and paste to the relevant cells as required.

How far do you want to copy it? Is there a pattern to the output?

rylo

[BozzA]

Hi,

Its not that im rebuilding the formula everytime, this checks a range of values in a tab, and compares it to another tab, each row is different, this code just checks to see if the row appears on the other tab.

The loop function goes through every row, value x is the row number

& and i do not want to paste it to relevant cells either as I need it automated in VB

[rylo]

Hi

Example file time.

I'm still not sure if you want to evaluate the formula and use the result as part of your processing in VBA, put the result to a place in the spreadsheet, or put the formula into the spreadsheet based on the revised positioning.

I gather you want to scroll through a range, and evaluate for each of the items in the range. But what do you do with them?

rylo

[BozzA]

Ok,

I have uploaded an example file, as you can see on it I have two sheets.

The first sheet is the sheet with all the info on it (this case all the animals there numbers and names.) Each one is different to the others.

Sheet 2 is where the user enters the data (user interface), so if the user only wants to see dog 2's name all they have to do is type it in and paste down the formula as ive attached earlier in this post. That code basically matches the col A & B and then returns the C value.

I would like this exact excel function in VBA that can be run with a push of a button.

If someone could help me work it out for just the first entry on sheet2 I would know how to finish off what i need to do,

Thanks again

[rylo]

Getting clearer.

If all you want to do is enter the formula, then putting it in sheet2!D3 and copying down would seem to me to be the logical way to go. You don't have to do anything fancy, as you know exactly what values to put in.

TO make things more consistent, make some dynamic defined ranges to cover the data in columns A, B and C of sheet1. Copying / filling the formula down in sheet2 is pretty straight forward.

Is there any reason that this wouldn't work???

rylo

[BozzA]

Yeah, theres one problem i seem to be facing, it appears that if you delete a row from sheet1 (this will actually be happening a lot!), the formula changes on sheet 2, no matter if you place the "$" signs in the formula (it seems to be an automatic thing, which is really annoying).

The other reason is that the rest of my actual spreadsheet is VBA coded and im trying to make it fancy :P

So yeah they are the basic reasons why I would like to do this

Boz

[rylo]

Hi

So why not use dynamic defined names for the columns in sheet1? Then if you add, or delete rows, the defined names will automatically update.

Something like
Sheet1:
Name: Animal, Refers to: =OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A),1)
Name: Numba, refers to: =OFFSET(Animal,0,1)
Name: Name, refers to: =OFFSET(Animal,0,2)

Sheet2:
D3: =INDEX('example(1).xls'!Name,MATCH(1,INDEX((A3='example(1).xls'!Animal)*(B3='example(1).xls'!Numba),0),0))

Copy that formula down.

If you like, you could have a sheet activation macro, so that every time you activate sheet2, the formula is put into D3 and automatically copied down.

rylo

[BozzA]

Hi,

Ok i have never used dynamic defined names before but I can now see its benefits, but for some reason im experiencing a problem.

Defining Animal, Numba & Name works ok. Its the code that i plug into sheet2 that is causing problems it seems.

I enter in D3: =INDEX('example.xls'!Name,MATCH(1,INDEX((A3='example.xls'!Animal)*(B3='example.xls'!Numba), 0),0))
& it automatically changes it to =INDEX(Sheet1!Name,MATCH(1,INDEX((A4=Sheet1!Animal)*(B4=example.xls!Numba), 0),0))

Im using excel 07 does this make any difference? Cell says "#Name", it says that "Sheet1!Name" is the first culprit, what am i doing wrong?

[rylo]

Hi

Fell into my own trap. I shouldn't use key names as the names of variables. Try changing the defined name of Name to something else - say Nme, or MyName - something other than a key word.

If that doesn't fix it, then there may be some quirk of 2007 that I don't know about. I'm still blissfully, happily, non problematically using 2003.

rylo

[MarvinP]

Hi Bozz,

Find the attached that has a user defined function that will return the name of the pet. The function takes in the Animal type and Number and does a search down colum A and B to return the pet name.

It looks nothing like the Excel functions the other volunteers are giving you.

This is just another way to do your problem. Code is just another tool.

See the attached witht the code. I'm sure it can be cleaner but I just popped it out.

Please Login or Register  to view this content.

[BozzA]

Hi Marvin,

Thnx so much for that, it works very well, I have no idea why I didnt think of having an IF statement within an IF statement. Was initially thinking that a it was easier to convert an excel function then doing what you have shown me.

But just a quick question is there a way of updating information as soon as it is changed on reference sheet (sheet1). I was thinking of "Application.ScreenUpdating = True" but I don't see that working, what do you think?

Boz

[MarvinP]

Hi BozzA,

Not a problem. Just need to insert an "Application.Volatile" in my code above. (I'll edit it in.)
The code above should now recalculate when you change data in sheet1.
Read about it at this link.

[BozzA]

Marvin, thanks again it works exactly how I need it too, application.volatile is exactly what that code needed

Cheers mate,

Boz