Jump to content
Macro Express Forums

Excel Functions Library


joe
 Share

Recommended Posts

Over the past couple of years, we've accumulated Excel / Macro Express functions that were developed for different projects.

 

Floyd and I want to begin the process of formalizing them into a library for other developers and users. To that end we would like your input as to what YOU want.

 

What is it that you want to see Macro Express do for Excel? What kind of operations, tools, handlers, functions, features, and so forth, would make your automating Excel easier?

Link to comment
Share on other sites

One that I have on the to do list is to write a routine ot generate an integer date. I've done one for day of year that works for leap years and such but maybe there's a built in function in VBScript or at least a existing script that could be tailored for ME users.

Link to comment
Share on other sites

One that I have on the to do list is to write a routine ot generate an integer date. I've done one for day of year that works for leap years and such but maybe there's a built in function in VBScript or at least a existing script that could be tailored for ME users.

This is easily done. The command itself is a one-liner.

Link to comment
Share on other sites

VBScript lessons are beyond the scope of a Macro Express forum, but ...

 

Within VBScript, you need to look at these three functions:

 

DatePart - Returns the specified part of a given date.

DateAdd - Returns a date to which a specified time interval has been added.

DateDiff - Returns the number of intervals between two dates.

 

What the heck does that mean; "intervals"? Well, an interval can be a year, quarter, month, day of year, day, weekday, week of year, hour, minute, or second. It simply depends on what you tell the command to return in relation to the function call.

 

Hopefully Joe won't see this ;)

Link to comment
Share on other sites

Yes well, let's get back to the subject; Excel and Macro Express.

 

What is it that you want to see Macro Express do for Excel? What kind of operations, tools, handlers, functions, features, and so forth, would make your automating Excel easier?

Link to comment
Share on other sites

Joe,

 

Maybe this has been done before... and I am just too lazy to search, but I have a project on my table right now that requires me to view different tabs within an excel file. I need excel to launch a program, and I need to be able to navigate to a specific tab. Have you done this before? I am not an excel expert, but I don't see a way to navigate directly to a specific worksheet.

Link to comment
Share on other sites

Joe,

 

Maybe this has been done before... and I am just too lazy to search, but I have a project on my table right now that requires me to view different tabs within an excel file. I need excel to launch a program, and I need to be able to navigate to a specific tab. Have you done this before? I am not an excel expert, but I don't see a way to navigate directly to a specific worksheet.

Cyberchief it's good to hear from you again.

 

As a matter of fact take a look at this topic. It is exactly what you need IF you are referring to worksheets when you say TAB. This itty-bitty applet will allow you to activate not only an underlying workbook (if you have multiple workbooks open) but also whichever worksheet you need within the workbook.

 

I strongly suggest that you download the Help_Active_Window.zip file and read it before doing anything else. It explains all in detail.

Link to comment
Share on other sites

I use F5 to navigate within Excel. Many folks don't realize it exists but it's a seriously handy way to specify ranges and cells in Excel. I can F5 and enter C18 to get acell, A1:D46 for a range or C:C for acolumn. And of course Sheet1!A12:C56.

 

Also I've been known to create a worksheet in the workbook that pulls cell contents from various places and may even do calculations for my macro. This way I can control format and such in a way that the user won't muck up.

 

Sorry, this is a bit off topic but I didn't know how else to respond to the guy.

Link to comment
Share on other sites

Joe,

 

Maybe this has been done before... and I am just too lazy to search, but I have a project on my table right now that requires me to view different tabs within an excel file. I need excel to launch a program, and I need to be able to navigate to a specific tab. Have you done this before? I am not an excel expert, but I don't see a way to navigate directly to a specific worksheet.

 

Thanks Joe! Been pretty busy so haven't had much time to post on here. I do read through the threads regularly... but don't always have time to reply. And thanks for making those corrections to the re-direct. Couldn't figure out what was going on.

 

I saw your post before regarding the Excel functions. I didn't go into it deep enough because I thought it had to do with mulitple workbooks rather than worksheets. I am going to give it a look. Thanks!!!

Link to comment
Share on other sites

Thanks Joe! Been pretty busy so haven't had much time to post on here. I do read through the threads regularly... but don't always have time to reply. And thanks for making those corrections to the re-direct. Couldn't figure out what was going on.

 

I saw your post before regarding the Excel functions. I didn't go into it deep enough because I thought it had to do with mulitple workbooks rather than worksheets. I am going to give it a look. Thanks!!!

 

Joe,

 

Working with that applet now. All I can say is... WOW!!! This is exactly what I needed. I can modify this to do quite a few things that I handn't been able to do in the past. Great work!!!

Link to comment
Share on other sites

Hey Joe... Another question for you.

 

In the Example "Activate Workbook - Interactive"... Had a question on your coding. You set T11 to the path of the applet... and you set T14 to the applet name. Seems to me like this is a waste of variables. Couldn't you set T14 to be the entire path and application? In my world, variables are at a premium. I need to cut waste wherever I can because some of our programs are stretched to the brink. Let me know your thoughts on why it was programmed this way.

Link to comment
Share on other sites

Hey Joe... Another question for you.

In the Example "Activate Workbook - Interactive"... Had a question on your coding. You set T11 to the path of the applet... and you set T14 to the applet name. Seems to me like this is a waste of variables. Couldn't you set T14 to be the entire path and application? In my world, variables are at a premium. I need to cut waste wherever I can because some of our programs are stretched to the brink. Let me know your thoughts on why it was programmed this way.

Good question! In this case, T14 is also used as the window title (caption) for the error MsgBox and for terminating the process. Look at lines 110 and line 118.

Link to comment
Share on other sites

  • 1 month later...

Well since it applies to my last post, I thought I'd throw in a request for the Excel Functions Library to handle direct write to Excel files without opening them. I know how to create CSV or Text files that Excel can open, but if the file is never opened in Excel, Excel cannot link to the cells in these file types.

 

For example:

 

If I create a CSV file that has a variable value written to cell A5, another Excel file cannot reference the stored value in cell A5 unless it is open in Excel. If there is a workaround on this - PLEASE SHARE. If not, let's get this added. It sure would be nice for some of the crap I'm working on right now.

 

Thanks!

 

Brad

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...