vb code for vlookup (and other func.) question!

[RamziSaab]

I dunno know if this is a stupid qustion but:

I would like to know if in built functions in excel such as vlookup, sum, are all built in visual basic (like UDFs) and if so is it possible to see the code..

 

[xlbo]

AFAIK they are not. I don't know for sure but being as they are native to excel, I would've thought they will have been built either in a language native to the internal workings of excel or C++ or similar.

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[RamziSaab]

ok, but technically it should be possible to build similar functions (as vlookup in VB) or would these be extremely complicated to build...

 

[xlbo]

Easy enough to build a comparable function to vlookup - just using FIND and OFFSET BUT it would be a LOT slower. Why can you not just use the built in functionality ??



Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Loomah]

Ramzi
Ultimately this is a bit of a pointless exercise. That said (I'm no UDF guru) I started by trying to recreate a sum function - something like this

Function MySum(rng As Range) As Double
Dim c As Range
For Each c In rng
    MySum = c + MySum
Next
End Function

But this would get very slow if the range is big.
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[RamziSaab]

No its really just out of curiousity, e.g. vlookup only find the first value (if fields are not unique) it would be nice (if it was not to hard to make) to be able to add a small functin that would make it go to the 2nd value (or 3rd etc,) and i am sure a lot of people would find that useful...But then again if it is really slow then its probably easier to format the data so the categories are unique¬!

 

[xlbo]

If it's a one off for a specific project then it's probably a good idea but bear in mind that whilst one or two instances of a custom vlookup UDF wouldn't take too long to recalc, if you have a dataset of several hundred (or thousand) records that you'll need to use this for, it's gonna be recalcing for a loooooong time. I have actually done similar (within a sub) for one of my "databases" in excel where there can be duplicate values and I need to check which one is the active record:

With dbWBdbSht
Set fCell = .Columns("D&quot.Find(mOutlet, LookIn:=xlValues, Lookat:=xlWhole)
firstaddress = fCell.Address
'test to see if flag is "ACTIVE"
If fCell.Offset(0, 10).Text <> &quot;ACTIVE&quot; Then
Do
Set fCell = .Columns(&quot;D&quot.FindNext(fCell)
Loop While Not fCell Is Nothing And fCell.Address <> firstaddress And fCell.Offset(0, 10).Text <> &quot;ACTIVE&quot;
'do stuff on record found with ACTIVE flag
Else
'do stuff on first record found
End If
End With

This could be easily modded to find the 2nd or 3rd instance and return a value in a UDF

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[RamziSaab]

See my problem is i ahve 80 odd sheets with 300 categories each and i need to extract around 100 from each sheet...now not only are the categories not unique (which i can solve with a custom built find and replace macro) but the people who send me the sheet decide to change names (therefore i have to go and change my names for vlookup) and this happens monthly.

The data gets put into a single sheet and imported to access...but will have to stick with what i have...

Thanks for ur help