PDA

View Full Version : How to shorten this down?



WillyTheFish
10-12-2008, 11:47 AM
Hail all mighty VBA-geeks ;),

I'm working on my first big VBA-project and since I'm almost done, i want to 'clean' up my coding a little.

There's one function that annoys more than the others:
I have stored the projects settings in the first sheet. The routine loops
through the sheet and tries to find the stored values for each variable.
Since i have around 50 variables stored in that sheet, it takes a whole lot of code. Is there a way to make this more efficient?


Dim bytRow As Byte
bytRow = 0
Do
bytRow = bytRow + 1
Loop Until Sheet1.Cells(bytRow, 3) = "gsngExchangeRate"
gsngExchangeRate = Sheet1.Cells(bytRow, 4)


As you can see the procedure loops through the sheet1 in column 3 until
it can find the name of the variable (in this case 'gsngExchangeRate').
In Column 4 the variables value is stored. This usually works pretty okay, but since I have about 50 of these blocks it devours my scroll bar ^^

Thanks in advance!

mdmackillop
10-12-2008, 12:38 PM
Use Find instead of looping


gsngExchangeRate = Columns(3).Find("gsngExchangeRate").Offset(, 1)
MsgBox gsngExchangeRate

WillyTheFish
10-12-2008, 12:45 PM
But of course!!!
Thanks a lot, i owe you :friends: