MS Excel / VBA help thread

Yes, "" works.

I have a strange feeling that occasionally they may return slightly different things but I can't for the life of me remember when.

Yeah, there’s something about “”*%!?”” Or similar

Does anyone have any good sources for sample excersises?

I'm trying to brush up on my excel - specifically vlookups and IF. But mainly If as I seem to be really struggling even when trying to effectively copy existing answers.

I'm finding it hard to work through and build up to "getting it" without having any excersises to practice.

Excel jet is great. Really clear and helpful.

Do you want some data to muck around with?
https://www.contextures.com/xlsampledata01.html

Thanks that's much appreciated.

Cheers for the data as well. I have some, but it's already been worked on elsewhere so its hard to do it do it independently - if that makes sense?

Hadn't seen that site before, it's always useful having some non-confidential sample data. I ended up writing a macro to generate loads of random sample data as I didn't have anything to work with.

it was my first hit on "excel random data set". I did think about linking to fingertips/phe/ohid/coronavirus datasets but thought it might not be relevant.

Someone also recommended https://data.london.gov.uk/

Here are some good data sets: https://www.makeovermonday.co.uk/data/

After a million years using excel, I have just discovered what the flashfill thing is doing and IT HAS CHANGED MY LIFE.

flashfill

Looks interesting, is the only thing it can do combine or separate text? Or does it recognise other patterns?

Don't know I'm afraid. I use it to separate text (though it does numbers too) - eg pulling out the colour part from a long column of SKU variants:
S/M / White
XXL / Black/White
L / Saffron
One Size / Green

Gives me:
White
Black/White
Saffron
Green
etc

Was using text to columns but always needed a fair amount of fiddling because of variants using / or space.

Edit: ignore me it was networkdays function.

This is interesting. An attempt to return formulas from a description of what you want it to do. It's OK at the moment, works with some stuff but gets a lot wrong.
https://excelformulabot.com/

That’s still helpful!

Doesn’t excel have this built in at the moment?

It's quite a good use of the OpenAI open beta (infinitely better than the usual slash fiction writing prompt crap that people use it for).

I'm completely considering ripping it off for my masters dissertation, except using shonkier models.

Microsoft has a $1billion investment in OpenAI - i reckon you can expect to see it across the whole MS suite in varying cappacities.

Just think - Clippy on steroids.

Yessssssss!

Some of the natural language is available in BI. I think it’s ok, what I don’t understand is people still saying “I don’t understand excel etc”

Power BI has something similar. I don't think Excel does.

Anyway, back to shit Excel questions.

Anyone any idea why, if I save a file as a csv using a macro it saves with American format dates? If I save the exact same file manually as a csv it comes out with UK date format.

I'm assuming there is a locale setting I've missed somewhere but I'm fucked if I know where.

Code to save is

 ActiveWorkbook.SaveAs Filename:= _
        RootFolder & Chr(j) & h & ".csv", FileFormat _
        :=xlCSVUTF8, CreateBackup:=False

How are you looking at the csv file to see what the date is saved as?

A text editor.

Macro saved one has stuff like 3/30/2018 whereas manual saved one is 28/05/2016

You need to add

Local:=True

to the

ActiveWorkbook.SaveAs

bit.

Edit: assuming your PC's regional settings are correct.

I have to say exporting date values in your computer's locale format gives me a sense of unease.

Maybe I have worked with too many Americans or databases, but I would prefer YYYY-MM-DD, which is ISO 8601 compliant.