I am trying to do the following --

[nuandaEB]

I am trying to do the following -- I have a macro that asks for a date range, and then creates a text entry in word for each of the dates in the range.

After displaying an English (US) date, I would like to convert that date to the equivalent date in a different calendar (e.g. Hebrew).

So I need two things:
1- Ensure that the English date be formatted correctly, irrespective of the OS's regional settings. I understand that the locale cannot be set in VBA since this requires a change to the registry (but I this can be done by using the shell command)

2- I need to pass the english date to a function that will do the hebrew date conversion. I found VB code for doing this, but it's not compiled and I don't have visual studio.

I'm pretty new to VBA (but not to programming) and am not sure if the above makes sense. Can anybody confirm that I'm on the right track?

Also, if anybody has advice on calling VB code from VBA, that would be helpful.

Thanks,

EB

 

[RobBroekhuis]

Why don't you copy the VB code right into VBA?
As for the date settings - VBA will handle things properly according to the local date settings. Is that not what you want?
Rob

 

[nuandaEB]

Thanks Rob,

I don't specifically want the locale settings, because that
will mean that the english date will not be displayed correctly when the macro is run on a computer with a different locale (e.g. Hebrew).

I want to switch the locale programmatically to a US locale, and then switch it back to whatever it was.

Also I tried pasting the 3rd party VB code for calendar conversion-- the VBA interpreter is complaining about the following line
Attribute VB_Name = "HebrewToGregorian"

Any thoughts?

Thanks,
EB

 

[RobBroekhuis]

I'm not a VB programmer, but I doubt that line is doing anything useful. Delete any VB code that is specific to making an executable, and keep the code that actually "does stuff". I'm convinced it will work just fine in VBA.
As for the local settings - maybe an easier way around it is to use a calendar control for the date input (or three fields - day, month, and year). Once you have a non-ambiguous date into VBA, you can display it in any format you like, e.g.
[a1].numberformat="mm/dd/yyyy"
so that you are no longer constrained by the local settings.
Rob

 

[nuandaEB]

Thanks Rob,

You are correct - the VB code works inside the VBA script.
I removed the line as you suggested.

In this case, I would be constrained
by the locale settings since not only does the formatting
change, but the language used for the month name (the months
appear in Hebrew) also changes when the locale is set to Hebrew.

Thanks for your help,

EB

 

[RobBroekhuis]

If it's acceptable within your application, you could get the date as three input fields, one of which is a combobox dropdown for the month, which you could populate with the English month names. You'd have to do some error checking to make sure the date is valid, but I think it will be preferable to trying to change the local settings on the fly - I've heard too many reports of inconsistent behavior to have much trust in that approach.
Rob

 

[rmikesmith]

Rob & EB,

Just an FYI:
The

Attribute VB_Name = "SomeString"

line is generated when a module is exported, whether VB or VBA. To see this, right-click on a module in the VBE and select Export File... After saving the file, open it using Notepad or other editor and it will be at the beginning of the file.
Conversely, when you import such a file the VBE uses this to determine the module name. It is not intended to be acted on by the language compiler itself, however. Hence, if this text is pasted in rather than imported it causes an error.

Regards,
Mike

 

[RobBroekhuis]

Gotcha. Thanks for the clarification, Mike.
Rob