Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
I'm trying to generate a sequential number. I have a workbook ( .xls) used
as a "template" to create new order forms. Each form must have a sequential number. The number is generated and inserted into the proper cell, then the workbook is SavedAs with the sequential number as part of the new file name. (I was trying to use an incremented CustomDocumentProperty when I finally realized the incremented number got saved with the new file, while the original number stayed with the original "template" file!) I copied the text file macro (from http://www.mcgimpsey.com/excel*/udfs...ntialnums.html) into the same module I have all the other macros for this workbook, and inserted the calling line ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber in the macro that inserts the new sequential number. When stepping through this, the code runs right over that line, and GetNextSeqNumber stays empty. The code never steps into the function to generate the next number, and the text file that is supposed to hold the number is never generated (it does not exist now). What am I doing wrong? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
Ed,
When I look at that page I see a funtion called "NextSeqNumber," which is different than the name you use below. Is it possible that you are calling the function by the wrong name? hth, Doug "Ed" wrote in message ... I'm trying to generate a sequential number. I have a workbook ( .xls) used as a "template" to create new order forms. Each form must have a sequential number. The number is generated and inserted into the proper cell, then the workbook is SavedAs with the sequential number as part of the new file name. (I was trying to use an incremented CustomDocumentProperty when I finally realized the incremented number got saved with the new file, while the original number stayed with the original "template" file!) I copied the text file macro (from http://www.mcgimpsey.com/excel*/udfs...ntialnums.html) into the same module I have all the other macros for this workbook, and inserted the calling line ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber in the macro that inserts the new sequential number. When stepping through this, the code runs right over that line, and GetNextSeqNumber stays empty. The code never steps into the function to generate the next number, and the text file that is supposed to hold the number is never generated (it does not exist now). What am I doing wrong? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
You should change GetNextSeqNumber to NextSeqNumber
On his page the function name is "Public Function NextSeqNumber" so you should be calling NextSeqNumber instead of GetNextSeqNumber. When you call GetNextSeqNumber, you are calling an undefined variant variable that is populating with the cell either with a null string or more likely the value zero. I think JE forgot the name of his function when he wrote the lower part of the page. (or intended to go back and rename it to match) -- Regards, Tom Ogilvy "Ed" wrote in message ... I'm trying to generate a sequential number. I have a workbook ( .xls) used as a "template" to create new order forms. Each form must have a sequential number. The number is generated and inserted into the proper cell, then the workbook is SavedAs with the sequential number as part of the new file name. (I was trying to use an incremented CustomDocumentProperty when I finally realized the incremented number got saved with the new file, while the original number stayed with the original "template" file!) I copied the text file macro (from http://www.mcgimpsey.com/excel*/udfs...ntialnums.html) into the same module I have all the other macros for this workbook, and inserted the calling line ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber in the macro that inserts the new sequential number. When stepping through this, the code runs right over that line, and GetNextSeqNumber stays empty. The code never steps into the function to generate the next number, and the text file that is supposed to hold the number is never generated (it does not exist now). What am I doing wrong? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
Did you get a stray hyphen in the URL when you clicked it?
(the OP is following JE's advice - believe JE forgot to rename the function). -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... Ed, When I look at that page I see a funtion called "NextSeqNumber," which is different than the name you use below. Is it possible that you are calling the function by the wrong name? hth, Doug "Ed" wrote in message ... I'm trying to generate a sequential number. I have a workbook ( .xls) used as a "template" to create new order forms. Each form must have a sequential number. The number is generated and inserted into the proper cell, then the workbook is SavedAs with the sequential number as part of the new file name. (I was trying to use an incremented CustomDocumentProperty when I finally realized the incremented number got saved with the new file, while the original number stayed with the original "template" file!) I copied the text file macro (from http://www.mcgimpsey.com/excel*/udfs...ntialnums.html) into the same module I have all the other macros for this workbook, and inserted the calling line ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber in the macro that inserts the new sequential number. When stepping through this, the code runs right over that line, and GetNextSeqNumber stays empty. The code never steps into the function to generate the next number, and the text file that is supposed to hold the number is never generated (it does not exist now). What am I doing wrong? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
That was it, Tom. Thank you.
Ed "Tom Ogilvy" wrote in message ... You should change GetNextSeqNumber to NextSeqNumber On his page the function name is "Public Function NextSeqNumber" so you should be calling NextSeqNumber instead of GetNextSeqNumber. When you call GetNextSeqNumber, you are calling an undefined variant variable that is populating with the cell either with a null string or more likely the value zero. I think JE forgot the name of his function when he wrote the lower part of the page. (or intended to go back and rename it to match) -- Regards, Tom Ogilvy "Ed" wrote in message ... I'm trying to generate a sequential number. I have a workbook ( .xls) used as a "template" to create new order forms. Each form must have a sequential number. The number is generated and inserted into the proper cell, then the workbook is SavedAs with the sequential number as part of the new file name. (I was trying to use an incremented CustomDocumentProperty when I finally realized the incremented number got saved with the new file, while the original number stayed with the original "template" file!) I copied the text file macro (from http://www.mcgimpsey.com/excel*/udfs...ntialnums.html) into the same module I have all the other macros for this workbook, and inserted the calling line ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber in the macro that inserts the new sequential number. When stepping through this, the code runs right over that line, and GetNextSeqNumber stays empty. The code never steps into the function to generate the next number, and the text file that is supposed to hold the number is never generated (it does not exist now). What am I doing wrong? Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
Tom,
I got an error 404 when I clicked on the link (didn't note exactly what the difference was) and then surfed from there. Thanks for the clarification. Ed, I believe that setting your VBE to "declare variable declaration" would have helped you track this down. It would have generated a compile error something like "unknown variable name." Ditto using the the VBE's Autocomplete feature, i.e., typing the first few letters of the function and then hitting Ctl-Space to show completion options, which would have indicated that there was no function with that name. Just my 2 cents from hours of experience doing it the hard way. Doug "Tom Ogilvy" wrote in message ... Did you get a stray hyphen in the URL when you clicked it? (the OP is following JE's advice - believe JE forgot to rename the function). -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... Ed, When I look at that page I see a funtion called "NextSeqNumber," which is different than the name you use below. Is it possible that you are calling the function by the wrong name? hth, Doug "Ed" wrote in message ... I'm trying to generate a sequential number. I have a workbook ( .xls) used as a "template" to create new order forms. Each form must have a sequential number. The number is generated and inserted into the proper cell, then the workbook is SavedAs with the sequential number as part of the new file name. (I was trying to use an incremented CustomDocumentProperty when I finally realized the incremented number got saved with the new file, while the original number stayed with the original "template" file!) I copied the text file macro (from http://www.mcgimpsey.com/excel*/udfs...ntialnums.html) into the same module I have all the other macros for this workbook, and inserted the calling line ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber in the macro that inserts the new sequential number. When stepping through this, the code runs right over that line, and GetNextSeqNumber stays empty. The code never steps into the function to generate the next number, and the text file that is supposed to hold the number is never generated (it does not exist now). What am I doing wrong? Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
In article ,
"Tom Ogilvy" wrote: I think JE forgot the name of his function when he wrote the lower part of the page. (or intended to go back and rename it to match) Yup. Thanks for pointing it out! - it's fixed now. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with McGimpsey's Seq No Function, please?
Thanks, Doug. I forget why I chose not use the "declare variable
declaration" - it was probably some newbie frustration of mine that I couldn't stand being reminded of everything I was forgetting! I never knew about the Ctrl+space - that will come in handy! Ed "Doug Glancy" wrote in message ... Tom, I got an error 404 when I clicked on the link (didn't note exactly what the difference was) and then surfed from there. Thanks for the clarification. Ed, I believe that setting your VBE to "declare variable declaration" would have helped you track this down. It would have generated a compile error something like "unknown variable name." Ditto using the the VBE's Autocomplete feature, i.e., typing the first few letters of the function and then hitting Ctl-Space to show completion options, which would have indicated that there was no function with that name. Just my 2 cents from hours of experience doing it the hard way. Doug "Tom Ogilvy" wrote in message ... Did you get a stray hyphen in the URL when you clicked it? (the OP is following JE's advice - believe JE forgot to rename the function). -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... Ed, When I look at that page I see a funtion called "NextSeqNumber," which is different than the name you use below. Is it possible that you are calling the function by the wrong name? hth, Doug "Ed" wrote in message ... I'm trying to generate a sequential number. I have a workbook ( ..xls) used as a "template" to create new order forms. Each form must have a sequential number. The number is generated and inserted into the proper cell, then the workbook is SavedAs with the sequential number as part of the new file name. (I was trying to use an incremented CustomDocumentProperty when I finally realized the incremented number got saved with the new file, while the original number stayed with the original "template" file!) I copied the text file macro (from http://www.mcgimpsey.com/excel*/udfs...ntialnums.html) into the same module I have all the other macros for this workbook, and inserted the calling line ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber in the macro that inserts the new sequential number. When stepping through this, the code runs right over that line, and GetNextSeqNumber stays empty. The code never steps into the function to generate the next number, and the text file that is supposed to hold the number is never generated (it does not exist now). What am I doing wrong? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
McGimpsey's Disappearing Pictures | Excel Discussion (Misc queries) | |||
Adding a custom function to the default excel function list | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |