Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SpreadsheetGear & VB.Net -> Workbook.Names

Posted on 2010-11-12
5
Medium Priority
?
2,122 Views
Last Modified: 2012-05-10
Hello,

I am new to SpreadsheeetGear which is a tool that allows excel interactions with VB.Net.
On looking at the sample V.Net source  code at http://www.spreadsheetgear.com/support/samples/srcview.aspx?file=amortizationVB.aspx and the associated Excel spreadsheet it interacts with I note in the source code it has the following:

        ' Open the workbook.
        Dim filename As String = Server.MapPath("files/amortization.xls")
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook(filename)
   
        ' Get IRange for cells from defined names.
        Dim pv As SpreadsheetGear.IRange = workbook.Names("PV").RefersToRange
        Dim rate As SpreadsheetGear.IRange = workbook.Names("Rate").RefersToRange
        Dim nper As SpreadsheetGear.IRange = workbook.Names("NPer").RefersToRange

Within the Excel sheet the following is displayed (where "Loan Amount (PV)" and "Annual Interest Rate (Rate)" etc are in column A and "$15,000.00" is in column B etc........

Loan Amount (PV)      $15,000.00
Annual Interest Rate (Rate)      7.25%
Total # of Months (NPer)      24

While "PV", "Rate", and "NPier" are written in column A as "Loan Amount (PV)" etc... in the excel worksheet there is no other reference to such abbreviations.

My question in relation to this is: When it states "Dim pv As SpreadsheetGear.IRange = workbook.Names("PV").RefersToRange" in the VB.Net code, is it actually looking up the excel sheet for the equivalent column/value PV? I presume" PV" is hardly just defined in the worksheet by using "Loan Amount (PV)"?

If anyone can explain how this works & what the IRange values are doing then I'd appreciate all help.

Thanking you in advance
0
Comment
Question by:speedygonzalez
  • 2
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 34128553
speedygonzalez

I'm not familiar with SpreadSheetGear - I usually use Excel Interop, depending on what I'm coding of course.

Anyway, in Excel there is a Names collection which consists of all the named ranges in the workbook.

You would create them in Excel either using Insert>Names or the Name Manager.

I would assume that Names in the code you posted refers to that collection and that there are named ranges called PV, Rate etc.

So workbook.Names("PV") refers to  the range and RefersTo is a reference to the range that has the name PV.

So, the code is creating object variables for the ranges the names refer to.

I hope that makes sense - when I read it back it does sound like I might have overcomplicated a bit.:)
0
 

Author Comment

by:speedygonzalez
ID: 34134464
Thanks imnorie, that sounds as if it could be what I'm referring to. I'll leave the question open for now to see if anyone else has any further suggestions but that sounds as if it could be what "PV" etc are referring to!
0
 
LVL 35

Assisted Solution

by:Norie
Norie earned 2000 total points
ID: 34135801
It's definitely named ranges being referred to in that code, but without seeing the rest of the code it's hard to tell why they are being used.

In Excel VBA you would usually just refer to a range as Range(<rangename>), which would return a range object.

So if you wanted to get the value from the named range PV you would just use something like this.

decPV =Range("PV").Value

I don't know why the code seems to be creating ranges from the named ranges - perhaps it's something to do with how SpreadSheetGear implements Names?
0
 

Author Comment

by:speedygonzalez
ID: 34153214
Thanks Imnorie!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question