Heres a little example of some VBA functions I put together to get geocodes into excel using the google maps API.
It’s not the best geocoder in the world, but it does ok in the test I’ve done so far, although it does just come up with blanks for some locations. To use it you will need a Google maps API key which you need to store in a text file on the same path as the workbook, details are on the first sheet of the workbook.
As for the code itself I had to automate IE rather than use an API or the HTTP or XMLHTTP libraries, as my corporate firewall would not allow access using these. The function only asks for 1 string, so you have to add spaces yourself, this might be done better, but it suited my needs (that is to say, I didn’t bother writing in optional arguments etc.)
It’s worth while looking at the code as I have done a few things that might not be considered “best practice”, mainly not closing the IE application when the function has run – this is to speed up the function – there is a public function that you can use to close it from a worksheet if you wish (see code as it’s not explained in the “documentation”)
Theres probably some other stuff that I should mention, but I did this a week or so ago and have not been able to post it as my internet connection was down, so I have forgotten that!
Feel free to post any questions and I’ll feel free not to answer them!!!!
You know the score, you’ve done a pivot table and there are a load of empty cells. You want to fill them up:
[NB: you have to copy the PT as values or the code will fail!]
I use this almost every day:
[vba]
Sub fill_in()
Dim cell As Object
Application.ScreenUpdating = False
For Each cell In Selection
If cell.Value = “” Then
cell.Value = cell.Offset(-1, 0).Value
Else: End If
Next cell
Application.ScreenUpdating = True
End Sub
[/vba]
I think this may be a repost – I did look back but i could not find anything that i had done before, but i remember that Zack Barresse suggesting a none vba way that involvde a special paste of some sort.
I recently started a new job and that’s keeping me plenty busy, so the post count is down here, sorry about that, but here some stuff that might be useful.
If you have ever worked with planning or forecasting problems then you have probably used Excels Solver Tool (Solver.xla). Its a nice way to get started with linear programming and is actually quite a cable little tool for use with spreadsheets.
Powerful as Solver is, sometimes its just not quite up to the job. Now you can buy a bigger, more powerful solver addin form FrontLine Systems (who make the Solver.xla that ships with Excel) but it’s not cheap. Worry not! A Mister Samuel E. Buttrey has kindly complied a freeware solver for use with Excel. It’s based on the popular open source LPSolve, which has a very active development and support community. You can download all the software here along with instructions:
The other day a mate asked me to draw him some glass balls, I used Inkscape. Later, because I’m like that, I wondered if I could do it in Excel – this is the result – I don’t think Paint Shop has much to worry about!
I’ve just built a dashboard with a bit of SQL. I’ve not done much for a while but I soon remembered that I have to pass dates very carefully.
SQL now accepts quite a lot of date formats, but as far as I know the American MM/DD/YY is still the standard of DAO? I think SQL server may now have YYYY/MM/DD as the standard.
Personally I always pass dates like this DD/MMM/YYYY, this work fine into an Access DB and that’s what I’ve always used.
I also notice that I tend to pass string variables like this
Does anyone do differently? Are there any flaws in these (my) methods? I guess I picked these habits up because they where used in the books I read while I was learning them, but I don’t know if there really the best methods?
