Сохранение значений в скрытых именах книги Excel

Данные функции могут быть полезны, если вы хотите спрятать некоторые значения в книге Excel

Функция SaveValue предназначена для создания (изменения существующих) имён в книге, а функция GetValue - для получения ранее сохранённых значений.

Sub SaveValue(ByRef WB As Workbook, ByVal Parameter As String, ByVal NewValue As String)
    ' создаёт в книге WB скрытое имя Parameter со значением NewValue
    Dim n As Name: On Error Resume Next: Err.Clear
    NewValue = "%%%" & NewValue & "%%%"
    WB.Names(Parameter).RefersTo = NewValue
    If Err Then WB.Names.Add Parameter, NewValue
    WB.Names(Parameter).Visible = False
End Sub
 
Function GetValue(ByRef WB As Workbook, ByVal Parameter As String) As String
    ' возвращает ранее сохранённое значение в скрытом свойстве книги
    On Error Resume Next
    GetValue = WB.Names(Parameter).RefersTo
    GetValue = Split(GetValue, "%%%")(1)
End Function

Ознакомьтесь также с макросом для просмотра всех имён в книге Excel
(чтобы потом посмотреть результат работы функции SaveValue)

Пример записи и чтения скрытых значений:

Sub ПримерИспользования()
    SaveValue ThisWorkbook, "test", "123qwe"
    Debug.Print GetValue(ThisWorkbook, "test")
End Sub

Использовать данный код можно, например, для программного снятия пароля с листа Excel при открытии книги:

Private Sub Workbook_Open()
    ActiveSheet.Unprotect GetValue(ThisWorkbook, "пароль")
End Sub


Ещё один вариант (для сохранения длинных текстовых строк в скрытых именах листов Excel):

Sub SaveTextWithSheet(ByRef sh As Worksheet, ByVal Parameter As String, ByVal txt As String)
    On Error Resume Next: Dim cnt&, i&, NewValue$: Const MaxLen& = 240, DATA_SEP$ = "~Џ%ћ"
    cnt& = Application.WorksheetFunction.RoundUp(Len(txt) / MaxLen&, 0)
    Err.Clear: sh.Names(Parameter).RefersTo = cnt&
    If Err Then sh.Names.Add Parameter, cnt&, false
    If cnt& = 0 Then Exit Sub
    For i = 1 To cnt&
        NewValue$ = DATA_SEP$ & Mid(txt, (i - 1) * MaxLen& + 1, MaxLen&) & DATA_SEP$
        Err.Clear: sh.Names(Parameter & "_" & Format(i, "0000")).RefersTo = NewValue$
        If Err Then sh.Names.Add Parameter & "_" & Format(i, "0000"), NewValue$, false
    Next
End Sub
 
Function GetTextFromSheet(ByRef sh As Worksheet, ByVal Parameter As String) As String
    On Error Resume Next: Dim cnt&, i&, NewValue$: Const MaxLen& = 240, DATA_SEP$ = "~Џ%ћ"
    cnt& = Val(Mid(sh.Names(Parameter).RefersTo, 2))
    If cnt& = 0 Then Exit Function
    For i = 1 To cnt&
        NewValue$ = Split(sh.Names(Parameter & "_" & Format(i, "0000")).RefersTo, DATA_SEP$)(1)
        GetTextFromSheet = GetTextFromSheet & NewValue$
    Next
End Function

Sub ПримерИспользования()
    txt$ = "Это очень длинный текст: " & String(100500, "x")
    MsgBox "Длина исходной строки = " & Len(txt$)
 
    ' сохраняем текст в листе Excel
    SaveTextWithSheet ActiveSheet, "MyText", txt$
 
    ' извлекаем текст с листа
    res$ = GetTextFromSheet(ActiveSheet, "MyText")
 
    MsgBox "Длина извлеченной строки = " & Len(res$) & vbNewLine & vbNewLine & res$
End Sub

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".

Спасибо большое за ответ. Если можно хранить, пока файл открыт - подходит...

Здравствуйте, Андрей.
У ячейки ничего подобного, увы, нет.
Есть свойство ID (которое не используется - туда можно записывать и считывать значения),
но, при закрытии и открытии файла эта информация теряется.
Т.е. не уровне ячеек можно хранить информацию только пока файл открыт.

Подскажите, пожалуйста, а есть ли нечто-подобное, но для ячейки, а не для книги целиком? (какое-нибудь дополнительное скрытое значение для конкретной ячейки, которое можно использовать под свои нужды)

Если код выполняется из самой надстройки - ничего менять не надо
Если надо сохранить значения в другой книге Excel - замените в вызове функции ThisWorkbook на Workbooks("Имя.Расширение")
Это касательно первых функций: SaveValue и GetValue

а если использовать Workbooks("Имя.Расширение")но ссылаясь на лист надстроки?

подскажите как обнаружить что в книге есть скрытые имена и определить их и их значения

Спасибо.

Ну а в чем проблема?
Сначала преобразовываем массив в текстовую строку, сохраняем в свойствах,
а когда данные понадобятся - извлекаем их, и преобразовываем обратно в массив

PS: Я бы сделал проще - сохранил массив на скрытом листе.
 Ну или записал бы в отдельный файл (текстовый или Excel), сохранив в скрытом имени лишь путь к этому файлу.

Я имел ввиду программно сформированный двумерный массив значений

А какой массив вы туда пытаетесь записать?
Диапазон ячеек? или программно сформированный массив?

Можно попробовать сцепить массив в одну строку функцией Join, а потом эту строку записать в скрытое имя.

А как записать массив значений в скрытое имя?

Макрос принимает в качестве параметра книгу, в которую нужно записать свойство.

ThisWorkbook - книга, из которой запускается макрос

ActiveWorkbook - активная в момент запуска макроса книга

Workbooks("Имя.Расширение") - заданная пользователем книга (должна быть отрыта до запуска макроса)

Пример (книга test.xls должна быть открыта):

Sub ПримерИспользованияДляДругогоФайла()
    SaveValue Workbooks("test.xls"), "Свойство", "Значение"
End Sub

Подскажите, пожалуйста, если мне нужно сохранить какое-либо значение
не в этой книге, а в другой, - как это сделать?
Пока мои попытки ни к чему не привели.

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
  _  _     _      __        __  _   _   ____     ___  
| || | | | \ \ / / | | | | |___ \ / _ \
| || |_ | | \ \ /\ / / | |_| | __) | | (_) |
|__ _| | |___ \ V V / | _ | / __/ \__, |
|_| |_____| \_/\_/ |_| |_| |_____| /_/
Введите код, изображенный в стиле ASCII-арт.

Не получается применить макрос? Не удаётся изменить код под свои нужды?

Оформите заказ у нас на сайте, не забыв прикрепить примеры файлов, и описать, что и как должно работать.